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
Who is Participating?
pratikroyConnect With a Mentor Commented:
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 !!

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

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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  ;
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 :)
@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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.