Re: Storing and running a sql script in the database.

From: Guillermo Alan Bort <>
Date: Sun, 5 Apr 2009 20:58:09 -0300
Message-ID: <>

from PL/SQL it should be doable. I don't have a test DB here but something like

a number;
b number;
select * into a from v$recovery_file_dest; select * into b from v$flash_recovery_area_usage; /* some way to print the report (utl_file or dbms_output??) */ file_a_rsrc := utl_file.fopen(....)
file_b_rsrc := utl_file.fopen(....)
ult_file.put_line (file_a_rsrc,a,true);
ult_file.put_line (file_b_rsrc,b,true);

you can manage column formatting with tochar perhaps...

Though, I'd personally do it with html (generate a simple html that might be easier to read/parse) or if you are really cool and want to use XML Database (XDB) is another option.

if you want it to show lines on screen instead of into a file, change utl_file for dbms_output.

I will read a little about column formatting in files and let you know what I discover.

disclaimer: I did not try this and it might produce garbage (probably will). This is off-my-head code and needs to be revised. I will try to do it tonight as it's a rather slow night.


Alan Bort
Oracle Certified Professional

On Sun, Apr 5, 2009 at 4:43 PM, Martin Berger <> wrote:
> If you are running you can check the preprocessor-feature of
> external tables.
> It might give you everything needed:
> first write down your stored script into a file using a DIRECTORY,
> execute sqlplus as preprocessor with the proper parameters (login!) and the
> script,
> parse the created file as external table.
> Never tested, just thought about it.
> might it help,
>  Martin
> Am 03.04.2009 um 20:41 schrieb Jared Still:
> On Fri, Apr 3, 2009 at 10:56 AM, Terrian, Thomas J Mr CTR DLA J6DIB
> <> wrote:
>> ILogically, it seems that I should be able to store the 5 line script "as
>> is" into the database and run it.
> Well, maybe someone can enlighten us with a simple
> way to do this - I can't seem to think of one myself.
> It might be possible to pipe this to sqlplus out of dbms_scheduler.
> You may want to check that out, I haven't looked closely
> at it myself.
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Sun Apr 05 2009 - 18:58:09 CDT

Original text of this message