execute immediate - function

[mike778]

Is there a way to get a value out of an execue imediate statement?
like this:

execute immediate 'declare x varchar2;begin x:=function(2) end;'

i want to get x out of that statement.

any ideas?
thanks a lot,

mike

 

[sem]

Yes. Use into and/or using clauses. For your specific case:

declare
x varchar2(10);
begin
execute immediate 'begin :1:=function(2) end;' using out x;
end;

Regards, Dima

 

[jad]

according to:

http://www.dbasupport.com/oracle/ora9i/execute_immediate.shtml

3. To retrieve values from a dynamic statement (INTO clause).

declare
 l_cnt    varchar2(20);
begin
 execute immediate 'select count(1) from emp'
   into l_cnt;
 dbms_output.put_line(l_cnt);
end;

 

[mike778]

It works the way dima wrote, but the problem I got now is, that my function returns a table. tried it like this, but didnt work :-(.


declare
i_tbl pa_preval.t_Chartbl;
begin
execute immediate 'begin :1:=testei2; end;' using out i_tbl;
dbms_output.put_line(i_tbl(1));
end;

do you have any ideas?

greetings, michael

 

[sem]

No chanses,I suppose. You may use some "global" (package) variable as buffer, I did it, should work.

Regards, Dima

 

[jad]

can't you just use:

 declare
 i_tbl pa_preval.t_Chartbl;
 begin
   i_tbl :=testei2;
 dbms_output.put_line(i_tbl(1));
 end;

?

 

[sem]

Jad: it's pretty easy, if you know the name of procedure to call during design time

Regards, Dima

 

[jad]

the example you gave had it hard coded ;-)

 

[mike778]

jep, problem is, that i don't know the name before runtime.
i get it from a database table.
will try the global variable thing, but would have prefered to return the value directly...

greetings, mike