Re: Dynamically execute PL/SQL statement

From: GQ <dbaguy_ott_at_yahoo.com>
Date: 31 Oct 2004 09:45:20 -0800
Message-ID: <aad8b5cb.0410310945.2d2bd34f@posting.google.com>

"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:<clt7a9$ruj$1_at_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;
/ Received on Sun Oct 31 2004 - 11:45:20 CST