By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,480 Members | 1,144 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,480 IT Pros & Developers. It's quick & easy.

Dynamically execute PL/SQL statement

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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_var0 := something; end;');

Kamal
Jul 19 '05 #2

P: n/a
Jan
DECLARE

v NUMBER;

BEGIN

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

DBMS_OUTPUT.PUT_LINE(v);

END;

Jan
Jul 19 '05 #3

P: n/a
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
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.

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;
/
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.