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