[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3063
  • Last Modified:

Execute Immediate

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
 vSql1 :=   'RATE_TABLE_VALIDATION_PKG_SRI.'||aa.procedure_name||'('||pStateCd||','||''''||pRatingPlan||''''||','||''''||pVersion||''''||')';
       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 loop
1 Solution

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;';
       execute immediate (vSql1)  into pReturnCd  ;

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

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

Hope that helps ?

Best regards
Manfred Peter
TheHartfordAuthor Commented:
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;';

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  ;
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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 :)
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;


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
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

PL/SQL procedure successfully completed.

Hope this helps !!
@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.

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
@@sdortch  : Please post a new question for this.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now