Re: Storing and running a sql script in the database.
- From: Pete Finnigan <pete@xxxxxxxxxxxxxxxx>
- To: Tom.Terrian.ctr@xxxxxxx
- Date: Mon, 06 Apr 2009 13:43:43 +0100
Hi Tom,
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
@@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 @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
>
>
> --
> //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@xxxxxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
