Re: Storing and running a sql script in the database.
- From: Guillermo Alan Bort <cicciuxdba@xxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx
- 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
declare
a number;
b number;
begin
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);
end;
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.
hth
Alan Bort
Oracle Certified Professional
On Sun, Apr 5, 2009 at 4:43 PM, Martin Berger <martin.a.berger@xxxxxxxxx> wrote:
>
> If you are running 11.1.0.7 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
> <Tom.Terrian.ctr@xxxxxxx> 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
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
