Dynamically execute PL/SQL statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Agoston Bejo - 29 Oct 2004 11:46 GMT

I am looking for the PL/SQL equivalent of the VBScript Exec and/or Eval
functions, i.e. I want to be able to dynamically create a statement, then
execute it in the current PL/SQL context, e.g.

declare
x integer := 5;
begin
ExecuteStatement('x := 10');
dbms_output.put_line(x); -- should put "10"
if EvaluateExpression('x*2 = 20') then
dbms_output.put_line('Yes');
else
dbms_output.put_line('No');
end if; -- should put 'Yes'
end;

EXECUTE IMMEDIATE doesn't seem to work, because it runs the statement in the
global context, not inside the context of the current function.

Reply to this Message

Kamal - 29 Oct 2004 16:40 GMT

> [...] because it runs the statement in the
> global context, not inside the context of the current function.

You could you use global variables in packages, but it is often a bad
practice, prone to errors.

You can write:

execute immediate ('begin pkname.glob_var0 := something; end;');

Kamal

Reply to this Message

Jan - 30 Oct 2004 17:39 GMT

DECLARE

v NUMBER;

BEGIN

EXECUTE IMMEDIATE
'BEGIN :1:=10;END;'
USING OUT v;

DBMS_OUTPUT.PUT_LINE(v);

END;

Jan

Reply to this Message

GQ - 31 Oct 2004 18:45 GMT

> I am looking for the PL/SQL equivalent of the VBScript Exec and/or Eval
> functions, i.e. I want to be able to dynamically create a statement, then
[quoted text clipped - 14 lines]
> EXECUTE IMMEDIATE doesn't seem to work, because it runs the statement in the
> global context, not inside the context of the current function.

What is it you would like to do that you can't do ?
The following is an example using an anonymous block
to create a table in the schema running the script,
followed by the same thing in a procedure (that could take
parameters).

Declare
v_obj number;
v_tblsp varchar2(30);
Begin
Select count(*) into v_obj
from user_tables
where table_name = 'T_EMP';
If v_obj = 0 then
Select tablespace_name into v_tblsp
from user_tables
where table_name = 'EMPLOYEE';
execute immediate 'Create table T_EMP (c1 number primary key, c2
varchar2(20))'||
' tablespace '|| v_tblsp;
end if;
End;
/
Create or replace procedure testx authid current_user as
v_obj number;
v_tblsp varchar2(30);
Begin
Select count(*) into v_obj
from user_tables
where table_name = 'T_EMP';
If v_obj = 0 then
Select tablespace_name into v_tblsp
from user_tables
where table_name = 'EMPLOYEE';
execute immediate 'Create table HR_OWN.T_EMP (c1 number, c2
varchar2(20))'||
' tablespace '|| v_tblsp;
end if;
End;
/

Reply to this Message