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

From: Terrian, Thomas J Mr CTR DLA J6DIB <Tom.Terrian.ctr_at_dla.mil>
Date: Fri, 3 Apr 2009 14:02:30 -0400
Message-ID: <B538F62DA0448741B308EA69417CC30201462A33_at_COL1SMX19.USE.AD.DLA.MIL>

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_at_yahoo.com] Sent: Friday, April 03, 2009 2:01 PM
To: Terrian, Thomas J Mr CTR DLA J6DIB
Cc: oracle-l_at_freelists.org
Subject: Re: Storing and running a sql script in the database.

Why not a shell/batch script?  

sqlplus / _at_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_at_dla.mil> To: Jared Still <jkstill_at_gmail.com>
Cc: oracle-l_at_freelists.org
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....._at_test.sql. 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_at_gmail.com] Sent: Friday, April 03, 2009 1:46 PM
To: Terrian, Thomas J Mr CTR DLA J6DIB
Cc: oracle-l_at_freelists.org
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_at_dla.mil> 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 _at_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

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 03 2009 - 13:02:30 CDT