Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

execute immediate function with out parameter

LudockNov 26 2010 — edited Dec 2 2010
How to run a function with out parameter in the execute immediate statement?
declare
   vRunFunctie   varchar2(100) := 'startfunction';
   vParmIn1      varchar2(100) := 'AAA';
   vParmIn2      varchar2(100) := 'HHH';
   vParmOut1     number;
   vParmOut2     varchar2(100);
begin
   --
   execute immediate 'select '||vRunFunctie||'('''||vParmIn1||''','''||vParmIn2||''',:vParmOut2) from dual' into vParmOut1 using vParmOut2;
   --
   dbms_output.put_line('vParmOut1['||vParmOut1||']');
   dbms_output.put_line('vParmOut2['||vParmOut2||']');
end;
/
getting error: ORA-06572 Function startfunction has out arguments

the statement looks like this in pl/sql:
vParmOut1 := startfunction(vParmIn1, vParmIn2, vParmOut2);
--vParmOut1 := startfunction('AAA', 'HHH', vParmOut2);
Thanks.
L.

update:
You are not using bind variables with your dynamic code. That is a major mistake and the #1 reason for poor database performance.
It is a fundamental flaw in programming to design a function that includes output parameters. This is simply and plainly wrong.
Output parameters are also not supported by the SQL language - it does not support "procedure" like code units and call methods. 
I know the function should not have out parameters. But its programmed this way allready..
This post has been answered by Anton Scheffer on Nov 26 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 30 2010
Added on Nov 26 2010
6 comments
20,980 views