Re: Creating SQL Script file from PL SQL

George wrote:

> Hi Everyone. I have a dilemma I was hoping someone could help me out
> with: Essentially, I would like to run a giant SQL script which will
> select records needing to be deleted from a database. In order to
> "backup" this soon to be deleted data, I was hoping to capture all of
> this data inside of a flat-file in a SQL script format so I could
> simply rerun the flat-file whenever needed and re-insert the data.
> What I need to do is to figure out a way to create a bunch of INSERT
> statements within a flat-file, then have the results of my SELECT
> statements become those values separated by a comma. I would also
> prefer not to explicitly state each column name in my SELECT statement
> since my tables tend to have many fields.
>
> Here is an example of what I would like to do:
>
> SELECT * from some_table
>
> This query would result in 3 records, each with 5 fields....therefore,
> I would like to have my flat-file look something like this:
>
> INSERT into some_table VALUES (‘field1','field2',' field3',' field4','
> field5');
> INSERT into some_table VALUES (‘field1','field2',' field3',' field4','
> field5');
> INSERT into some_table VALUES (‘field1','field2',' field3',' field4','
> field5');
>
> Can this be done using PL/SQL?? Any help is much appreciated!!

Sybrand suggests one way. Here's one more.

Put on AFTER-DELETE trigger on the table and delete them. Let the trigger [Quoted] write them out using UTL_FILE.

And there are likely dozens more.

Daniel Morgan Received on Mon Mar 17 2003 - 17:22:09 CET