Re: execute immediate

From: Daniel A. Morgan <damorgan_at_exesolutions.com>
Date: Wed, 09 Jan 2002 12:44:54 +0000
Message-ID: <3C3C3B46.6D07426D@exesolutions.com>

Actually I was thinking more of something like this which I did recently for a software company front-ending an Oracle database with XML.

DECLARE
   x VARCHAR2(500) := 'DECLARE i PLS_INTEGER := 2;

                                            BEGIN
                                                    UPDATE sources
                                                    SET sourceno = sourceno *
i;
                                             END;';

BEGIN
   EXECUTE IMMEDIATE x;
END;
/

Where as many statements as one wished could be written into the anonymous block (not just the single update statement as above).

But, of course, both work.

Daniel Morgan

Thomas Kyte wrote:

> In article <a1ffov$ejl11_at_kcweb01.netnews.att.com>, "S.A." says...
> >
> >Is it possible to execute two statements in one call to execute immediate?
> >
>
> using a SCHEMA statement, yes.
>
> tkyte_at_TKYTE816> begin
> 2 execute immediate 'create schema authorization ' || user || '
> 3 create view v as select * from t
> 4 grant select on v to connect';
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> >for example;
> >declare
> >string_to_execute long;
> >begin
> >string_to_execute := 'create view test_view on test_table
> >/
> >grant select on test_view to power_users'
> >
> >execute immediate string_to_execute;
> >end;
> >/
> >
> >thanks for any help.
> >My environment: 8.1.7 enterprise edition on sun solaris.
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Wed Jan 09 2002 - 06:44:54 CST