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

You are not clear on "how to run it", I am assuming that because you talk of finding the script on the OS you still want to run it from sqlplus perhaps by just supplying a name. For instance if your script were:

col object_name for a30
col owner for a30
select object_name,owner from dba_objects where object_name='TEST';

assume this script is called test.sql now then simply create a table with a structure similar to sys.source$ and load the source into the table.

Then have a sqlplus script called; say run.sql that does:

accept choice prompt 'enter script name>'

spool tmp.sql
prompt 'spool tmp.lis'
select source from table
where name='&&choice'
/
prompt 'spool off'
spool off
_at__at_tmp.sql

You can make it more complicated by creating a PL/SQL annonymous block to give more control over the choices etc in terms of which scripts to run and use PL/SQL to create the logic.

If you want it completely in the database, create the logic as a procedure and have it write the temp file out to the OS and to run sqlplus using Java system command. This is of course a security risk!

cheers

pete
Terrian, Thomas J Mr CTR DLA J6DIB 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?
>
> Thanks,
> Tom Terrian
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Pete Finnigan
Director
PeteFinnigan.com Limited

Specialists in database security.

If you need help to audit or secure an Oracle database, please ask for
details of our courses and consulting services

Phone: +44 (0)1904 791188
Fax  : +44 (0)1904 791188
Mob  : +44 (0)7742 114223
email: pete_at_petefinnigan.com
site : http://www.petefinnigan.com

Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No       : 4664901
VAT No.          : 940 6681 14

Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of PeteFinnigan.com Limited.  This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.

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