Re: Storing and running a sql script in the database.
Date: Mon, 06 Apr 2009 13:43:43 +0100
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>'
prompt 'spool tmp.lis'
select source from table
prompt 'spool off'
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!
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?
> Tom Terrian
-- 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-lReceived on Mon Apr 06 2009 - 07:43:43 CDT