Dynamically execute PL/SQL statement

[Agoston Bejo]

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
ExecuteStatemen t('x := 10');
dbms_output.put _line(x); -- should put "10"
if EvaluateExpress ion('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.

[Kamal]

"Agoston Bejo" <gu***@freemail .hu> wrote in message news:<cl******* ***@news.caesar .elte.hu>...

[...] 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_var 0 := something; end;');

Kamal

[Jan]

DECLARE

v NUMBER;

BEGIN

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

DBMS_OUTPUT.PUT _LINE(v);

END;

Jan

[GQ]

"Agoston Bejo" <gu***@freemail .hu> wrote in message news:<cl******* ***@news.caesar .elte.hu>...

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
ExecuteStatemen t('x := 10');
dbms_output.put _line(x); -- should put "10"
if EvaluateExpress ion('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.

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