Re: Running SQL from PL/SQL

From: Stephen Darlington <Stephen_Darlington_at_Earthling.Net>
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

Original text of this message