Re: Storing and running a sql script in the database.
Date: Sun, 5 Apr 2009 20:58:09 -0300
from PL/SQL it should be doable. I don't have a test DB here but something like
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(....)
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.
Oracle Certified Professional
On Sun, Apr 5, 2009 at 4:43 PM, Martin Berger <martin.a.berger_at_gmail.com> wrote:
> If you are running 22.214.171.124 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
> parse the created file as external table.
> Never tested, just thought about it.
> might it help,
> 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
> <Tom.Terrian.ctr_at_dla.mil> 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