Re: Running a SQL script from a procedure!
Date: Wed, 09 Jan 2008 07:08:56 +0100
On Tue, 8 Jan 2008 20:29:55 -0800 (PST), joe_smile2all <raghurajnair_at_gmail.com> wrote:
>i am right now using oracle 9i.
>i have a procedure named
>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)|| 'end;';
and this iwll work.
-- Sybrand Bakker Senior Oracle DBAReceived on Wed Jan 09 2008 - 00:08:56 CST