Re: Running a SQL script from a procedure!

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

>Hi!
>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....
>thanks.

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)||
             'end;';

and this iwll work.

-- 


Sybrand Bakker
Senior Oracle DBA