Re: Running SQL from PL/SQL
Date: Sun, 27 Jun 1999 18:11:22 +0100
Message-ID: <7l5ocp$nrh$1_at_nclient1-gui.server.virgin.net>
Guy Gilstrap <guyg_at_eidensys.com> wrote in message
news:37739E49.3BAF0B85_at_eidensys.com...
> I have what seems like a simple problem. I want to create a PL/SQL
> procedure that accepts a file name and a Select string as parameters. I
> want the procedure to run the Select and spool the output to the text
> file. This seems simple but I cannot figure out how to call SQL from
> the PL/SQL. The only mechanism for Select seems to be SELECT INTO.
This is not very straight-forward in PL/SQL. For the PL/SQL to actually create and manipulate files you need to use the UTL_FILE package, but there are a number of limitations on the location of files and it's not terribly straight forward.
If you're not selecting a lot of data (less than a megabyte), you may be better using DBMS_OUTPUT and spooling the data to a file using SQL*Plus.
To do either, as another poster has noticed, you'll need the DBMS_SQL package to actually parse and execute the SQL string.
If you need a hand, give us a shout.
HTH,
--> Steve
Stephen Darlington (Stephen_Darlington_at_Earthling.Net) "I blame sex and paper for most of our problems" -- Scot Adams ----------------------------------------------------------------------------Received on Sun Jun 27 1999 - 19:11:22 CEST