Re: Creating SQL Script file from PL SQL

here is a probably inefficient script to dump data from a table to a .csv file. with a user specified file name and a user specified directory name to dump the data to (must be in init.ora)

Procedure DumpData(i_period_from in number,i_period_to in number,i_file_name in varchar2,i_directory in varchar2)

is

v_fileHandle UTL_FILE.FILE_TYPE;

v_record varchar2(32767);

cursor c_dump is

SELECT t.client,

t.TransType,

<etc>

FROM t

WHERE t.period >= i_period_from

AND t.period <= i_period_to

BEGIN v_fileHandle := UTL_FILE.FOPEN(i_directory,i_file_name,'w');

/* write header row to file */

v_record := 'Client,TransType,<etc>';

UTL_FILE.PUT_LINE(v_fileHandle,v_record);

For v_transaction in c_dump loop

/* build the record up column by column */

v_record :=
'"'||v_transaction.client||'","'||v_transaction.TransType||'","'||<etc>;

UTL_FILE.PUT_LINE(v_fileHandle,v_record);

end loop;

UTL_FILE.FCLOSE(v_fileHandle);

END DumpData;

cheers

--
Niall Litchfield
Oracle DBA
Audit Commission UK

"George" <gxb101_at_yahoo.com> wrote in message
news:b3c61414.0303180524.30c8a06f_at_posting.google.com...


> 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