Solved

Execute Immediate

Posted on 2004-04-06
8
3,047 Views
Last Modified: 2008-01-16
Hi,
I have a package within which I am trying to execute plsql function that returns a return cd using execute immediate statement. The plsql function are being read from a table and execute in a loop. I am getting an invalid statement. Can you tell me what am i doing wrong. See the snippet of the code below;


Cursor c1 is
    select procedure_name,severity_level,message,module_name
    from auto_rate_tbl_validation_list
    where risk_state_Cd = pStateCd
    and rating_plan = pRatingPlan
    and promotion_level = pPromotionLevel;

  for aa in c1
  loop
     begin
       dbms_output.put_line(aa.procedure_name);
 vSql1 :=   'RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')';
       dbms_output.put_line(vSql1);
       execute immediate (vSql1)  into pReturnCd  ;
        if pReturnCd != 0 then
         vTable := aa.module_name;
         vDesc := aa.message;
         vSeverity := aa.severity_level;
         PIPE ROW(o_Error_Row(vTable,vDesc,vSeverity));
         vErrCd := 1;
       end If;
      end;
  end loop
0
Comment
Question by:TheHartford
8 Comments
 
LVL 2

Expert Comment

by:alligatorsql
ID: 10768768
Hello

Call the function as an anonymous block. That means, that you have to put the word "begin"  before and "end;" after the call.
Here is the new code:

vSql1 :=   'BEGIN; RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')'||'; END;';
       dbms_output.put_line(vSql1);
       execute immediate (vSql1)  into pReturnCd  ;
     

Note: At the end of the function, there must be a ;
example

vSQL1 := 'BEGIN func(); END;'

Hope that helps ?

Best regards
Manfred Peter
0
 

Author Comment

by:TheHartford
ID: 10768865
Peter,
I tried that. It did not work. I had removed the commented code from the snippet that I posted earlier. Is there any other way?


--       vSql1 :=   'Begin RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||');'
  --                || ' End;';

Thanks
Sri
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 10769037
what did you get in the following command?

        dbms_output.put_line(vSql1); -- check here, the command is your want.
       execute immediate (vSql1)  into pReturnCd  ;
     
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10769818
Well, your problem is that you are trying to execute a function, whereas the method that you are using, is used to dynamically call an SQL statement.

If you dont want to try anything else, you can do the following :

vSql1 :=   'SELECT RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||') FROM DUAL';

the variable vSql1 would have an SQL statement like "SELECT RATE_TABLE_VALIDATION_PKG_SRI.yourfunc(pStateCd,pRatingPlan,pVersion) from Dual"

This becomes a valid SQL statement, and you should be able to get the desired output without changing anything else :)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Accepted Solution

by:
pratikroy earned 500 total points
ID: 10769863
To test the above I did the following :

SQL> create or replace function f_test (stdt varchar2) return varchar2 as
  2  vdate varchar2(20);
  3  begin
  4  vdate := to_char(to_date(stdt,'DDMMYYYY'), 'DD-MON-YYYY');
  5  return vdate;
  6  end;
  7  /

Function created.

SQL> select f_test('01012004') from dual;

F_TEST('01012004')
----------------------------------------------------------------------------
01-JAN-2004

SQL> get tst
  1  declare
  2  sql_stmt  varchar2(200);
  3  stdate    varchar2(30);
  4  begin
  5  sql_stmt := 'f_test(''' ||  '01012004' || ''')';
  6  dbms_output.put_line(sql_stmt);
  7  execute immediate sql_stmt into stdate;
  8  dbms_output.put_line(stdate);
  9* end;

SQL> @tst
f_test('01012004')
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 7

SQL> get tst
  1  declare
  2  sql_stmt  varchar2(200);
  3  stdate    varchar2(30);
  4  begin
  5  sql_stmt := 'select f_test(''' ||  '01012004' || ''') from dual';
  6  dbms_output.put_line(sql_stmt);
  7  execute immediate sql_stmt into stdate;
  8  dbms_output.put_line(stdate);
  9* end;
SQL> /
select f_test('01012004') from dual
01-JAN-2004

PL/SQL procedure successfully completed.

Hope this helps !!
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 10769943
@AlligatorSQL :

Just wanted to point out that you were partially incorrect, w.r.t the function call within a PLSQL Block. You said that the variable can be set to :

vSql1 :=   'BEGIN; RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')'||'; END;';

1. You dont need a ';' after BEGIN
2. I dont think you can execute the function in the PLSQL block the way you have suggested. I believe that it is only applicable for procedure calls.

Like :    
plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block USING 7788, 500;

Please let me know, if you think there is a way to execute the function dynamically (the way you have suggested). A small example, the way I have given above might help in understanding.

Thanks.
0
 

Expert Comment

by:sdortch
ID: 12106780
I have tried ran into the same problem.  I need to run a function from EXECUTE IMMEDIATE however, my function has DML statements inside and I get ORA-14551.  

Is there any other solution for this problem?

Thanks in advance
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12110388
@@sdortch  : Please post a new question for this.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PL/SQL LOOP CURSOR 3 56
compre toata in where clue oracle 4 55
Oracle Public Synonyms and Privileges 2 50
Export table into csv file in oracle 10 41
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now