RE: Storing and running a sql script in the database.
- From: "Terrian, Thomas J Mr CTR DLA J6DIB" <Tom.Terrian.ctr@xxxxxxx>
- To: "Joey D'Antoni" <jdanton1@xxxxxxxxx>
- Date: Fri, 3 Apr 2009 14:02:30 -0400
Yes, I know I can put it on the O/S. However, for other reasons, I
would like to store it in the database and run it from there.
Thanks,
Tom
-----Original Message-----
From: Joey D'Antoni [mailto:jdanton1@xxxxxxxxx]
Sent: Friday, April 03, 2009 2:01 PM
To: Terrian, Thomas J Mr CTR DLA J6DIB
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Storing and running a sql script in the database.
Why not a shell/batch script?
sqlplus / @test.sql
You can cron it if needed, and redirect the output as needed.
________________________________
From: "Terrian, Thomas J Mr CTR DLA J6DIB" <Tom.Terrian.ctr@xxxxxxx>
To: Jared Still <jkstill@xxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Friday, April 3, 2009 1:56:03 PM
Subject: RE: Storing and running a sql script in the database.
Isn't that a shame. Here is my script:
column space_limit format 999,999,999,999;
column space_used format 999,999,999,999;
column space_reclaimable format 999,999,999,999;
select * From v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
Running it as an O/S script is a breeze.....@xxxxxxxxx Running it as a
Database script is a mess...create a procedure, use dbms_sql, create
some cursors, etc.
Logically, it seems that I should be able to store the 5 line script "as
is" into the database and run it.
Tom
-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Friday, April 03, 2009 1:46 PM
To: Terrian, Thomas J Mr CTR DLA J6DIB
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Storing and running a sql script in the database.
On Fri, Apr 3, 2009 at 9:52 AM, Terrian, Thomas J Mr CTR DLA J6DIB
<Tom.Terrian.ctr@xxxxxxx> wrote:
I have a simple sql script that selects from a couple of tables.
What I
would like to do is store the script in the database and run it
from
there. For example, instead of @test.sql going to the O/S to
find
test.sql, I want it to find the script in the database
somewhere. Does
anyone know how to do this?
I know I can rewrite it into a stored procedure but I would
rather just
leave it as a sql script.....any ideas?
I think it is doable, though I haven't done it myself.
First off, you can't directly run a SQLPlus script from inside the
database.
There are sqlplus commands that SQL does not know what to do with.
set linesize NNN
set echo on|off
...
I think that to do this, you would need to create a directory object,
have adequate security and permissions on the OS directory used,
create a table to store the sqlplus commands, and use utl_file to
actually write out the commands to a temporary file.
Then the resulting script would still need to logon to the database.
Ironic, isn't it.
If you really want to do it from the database, writing it as a stored
procedure would be quite a bit easier.
Or just use external scripts and a scheduler.
Jared
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
