Solved

Execute Immediate

Posted on 2004-04-06
8
3,046 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.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

20 Experts available now in Live!

Get 1:1 Help Now