On Fri, Apr 3, 2009 at 9:52 AM, Terrian, Thomas J Mr CTR DLA J6DIB < Tom.Terrian.ctr@xxxxxxx> 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? > > 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