Re: generating a text file of a table

From: sw <swilliam_at_flash.net>
Date: Fri, 15 Dec 2000 19:57:27 GMT
Message-ID: <HIu_5.8727$bw.829959@news.flash.net>

Another approach is to create a stored procedure and use the UTL_FILE package. I have pasted some sample code below. (Given this addition to init.ora [utl_file_dir = D:\oraFiles]). The code is also available at my website ( http://home.flash.net/~swilliam/PLunloadSQL.txt ). I am curios as to what all of your thoughts are on this approach. -sw

SAMPLE

This procedure creates a flat file with attributes from table <student>

CREATE OR REPLACE PROCEDURE unloadStudent AS -- Declare the variables

File Handle Declaration v_fileHandle UTL_FILE.FILE_TYPE;
The buffer to hold the record to be written to the file v_detailBuffer LONG;
Declare the cursor for all records in the request_text table CURSOR c_request_text IS SELECT STUDENT_ID, SALUTATION, FIRST_NAME, LAST_NAME, STREET_ADDRESS, ZIP, PHONE, EMPLOYER TO_CHAR(CREATE_DT, 'DD-MON-YYYY HH24:MI:SS') CREATE_DT, FROM STUDENT;

v_tempRecord c_student%ROWTYPE;

BEGIN

Open the file BEGIN v_fileHandle := UTL_FILE.FOPEN('X:\oraFiles','student.txt','w'); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN RAISE_APPLICATION_ERROR(-200001, 'File location or name is invalid'); WHEN UTL_FILE.INVALID_MODE THEN RAISE_APPLICATION_ERROR(-200002, 'The file open mode string is invalid'); WHEN UTL_FILE.INVALID_OPERATION THEN RAISE_APPLICATION_ERROR(-200003, 'The file could not be opened as requested'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-200004, 'Other error while opening file'); END;
Prepare the detail buffer BEGIN OPEN c_student; LOOP FETCH c_student into v_tempRecord; IF (c_student%NOTFOUND) then CLOSE c_student; EXIT; END IF;

v_detailBuffer :=
v_tempRecord.STUDENT_ID||','||v_tempRecord.SALUTATION||','||v_tempRecord.FIR ST_NAME||','|| v_tempRecord.LAST_NAME||','||v_tempRecord.STREET_ADDRESS||','||v_tempRecord. ZIP||','|| v_tempRecord.PHONE||','||v_tempRecord.EMPLOYER||','||v_tempRecord.CREATE_Dt;

Write the detail buffer to the file BEGIN UTL_FILE.PUT_LINE(v_fileHandle,v_detailBuffer); EXCEPTION WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-200005, 'Not a valid file handle while writing text'); WHEN UTL_FILE.INVALID_OPERATION THEN RAISE_APPLICATION_ERROR(-200006, 'File not open for writing'); WHEN UTL_FILE.WRITE_ERROR THEN RAISE_APPLICATION_ERROR(-200007, 'OS error during write operation'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-200008, 'Other error while writing text'); END;

END LOOP;

Close the file BEGIN UTL_FILE.FCLOSE(v_fileHandle); EXCEPTION WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-200013, 'Not a valid file handle while writing text'); WHEN UTL_FILE.INVALID_OPERATION THEN RAISE_APPLICATION_ERROR(-200014, 'OS error during write operation'); WHEN UTL_FILE.WRITE_ERROR THEN RAISE_APPLICATION_ERROR(-200015, 'Other error while writing text'); END; END; END; / <tshbedford_at_my-deja.com> wrote in message news:91comf$gtm$1_at_nnrp1.deja.com...
> Thanks Jay,
> I've got that working fine as command line entries but I can't get it
> to run as a script. Do you know the URL of an idiot's guide to
> writing and running scripts (I'm a total Oracle novice).
> Ultimately I want to be able to create csv files from a C++
> application.
> I'm surprised that an established product like Oracle hasn't got a
> simpler method of generating text files of data.
>
> Tim
>
>
> In article <3A3930A3.1AC74F61_at_pixar.com>,
> Jay Weiland <jay_at_pixar.com> wrote:
> >
> > --------------C3B6EDDC5C57E2489F4CA059
> > Content-Type: text/plain; charset=us-ascii
> > Content-Transfer-Encoding: 7bit
> >
> > Hey Tim,
> >
> > If you just want text and fixed width will do it for you:
> >
> > SQL> spool c:\temp\table_text.txt
> > SQL> select * from your_table_name;
> > .
> > .
> > .
> > SQL> spool off
> >
> > And you will have a simple text file with the output. If you
need CSV one solution would be change the select to:
> >
> > SQL> select field1 || ',' || feild2 || ',' || feild3 || ',' || field4
from your_table_name;
> >
> > Jay!!!
> >
> > tshbedford_at_my-deja.com wrote:
> >
> > > Oops, I thought my first message had failed to up load. Sorry for
repeating
> > > myself.
> > >
> > > Tim
> > >
> > > In article <91ab3b$h16$1_at_nnrp1.deja.com>,
> > > tshbedford_at_my-deja.com wrote:
> > > > Can anyone tell me how to generate a simple text file (csv) of a
table's
> > > > contents? thanks
> > > >
> > > > Tim
> > > >
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > > >
> > >
> > > Sent via Deja.com
> > > http://www.deja.com/
> >
> > --------------C3B6EDDC5C57E2489F4CA059
> > Content-Type: text/html; charset=us-ascii
> > Content-Transfer-Encoding: 7bit
> >
> > <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> > <html>
> > Hey Tim,
> >      If you just want text and fixed width will
> > do it for you:
> > <tt>SQL> spool c:\temp\table_text.txt</tt>
> > <tt>SQL> select * from your_table_name;</tt>
> > <tt>.</tt>
> > <tt>.</tt>
> > <tt>.</tt>
> > <tt>SQL> spool off</tt>
> >      And you will have a simple text file with
the
> > output.  If you need CSV one solution would be change the select
to:
> > <tt>SQL> select field1 || ',' || feild2 || ',' || feild3 || ',' ||
field4
> > from your_table_name;</tt>
> > Jay!!!
> > tshbedford_at_my-deja.com wrote:
> > <blockquote TYPE=CITE>Oops, I thought my first message had failed to
up
> > load. Sorry for repeating
> > myself.
> > Tim
> > In article <91ab3b$h16$1_at_nnrp1.deja.com>,
> >   tshbedford_at_my-deja.com wrote:
> > > Can anyone tell me how to generate a simple text file (csv) of a
> > table's
> > > contents? thanks
> > >
> > > Tim
> > >
> > > Sent via Deja.com
> > > <a href="http://www.deja.com/">http://www.deja.com/</a>
> > >
> > Sent via Deja.com
> > <a
href="http://www.deja.com/">http://www.deja.com/</a></blockquote>
> > </html>
> >
> > --------------C3B6EDDC5C57E2489F4CA059--
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/

Received on Fri Dec 15 2000 - 13:57:27 CST