Re: Running a SQL script from a procedure!

From: <>
Date: Wed, 09 Jan 2008 07:08:56 +0100
Message-ID: <>

On Tue, 8 Jan 2008 20:29:55 -0800 (PST), joe_smile2all <> wrote:

>i am right now using oracle 9i.
>i have a procedure named
>any_table(p_tablename varchar2)
>which i am using to create a .sql file using the UTL_FILE package.that
>file is again containing the code of another procedure called retrieve
>.i want to execute the second script so that the procedure retreive
>gets created....and that too from within the procedure any_table.
>can anyone help me to get this done....

The idea is just horrible.
First of all to create procedures on the fly. The only place for this is in a generator. Secondly, even to want to call sqlplus from inside sqlplus. Isn't there execute immediate?
If you want to program an unscalable application by designing kludges: You can do *anything* using execute immediate, you can even create a procedure. Just put the entire procedure text in a string. To format it include chr(10) calls in the string.
sqlstr := 'create or replace procedure horrible as '||chr(10)||

              'begin '||chr(10)||
              'null; '||chr(10)||

and this iwll work.


Sybrand Bakker
Senior Oracle DBA
Received on Wed Jan 09 2008 - 00:08:56 CST

Original text of this message