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

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

Execute Immediate

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
TheHartford
Asked:
TheHartford
1 Solution
 
alligatorsqlCommented:
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
 
TheHartfordAuthor Commented:
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
 
DanielztCommented:
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
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.

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

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