Re: Creating SQL Script file from PL SQL

[Quoted] Can anyone give me an example of some code on how to actually "write" out the individual field values within the quotes of the INSERT statement? I understand that I will need to use UTL_FILE in order to create the flat-file, but what do I need to do in order to have each field be written out between quotes like the example below? Thanks

INSERT into some_table VALUES ('field1','field2',' field3');

DA Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3E75F631.F70D4528_at_exxesolutions.com>...
> 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
> write them out using UTL_FILE.
>
> And there are likely dozens more.
>
> Daniel Morgan
Received on Tue Mar 18 2003 - 14:24:03 CET