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

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 3 Apr 2009 10:46:27 -0700
Message-ID: <bf46380904031046y51ba9eadw3110f590177380a4_at_mail.gmail.com>

 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

Received on Fri Apr 03 2009 - 12:46:27 CDT