Re: generating a text file of a table

in article HIu_5.8727$bw.829959_at_news.flash.net, sw at swilliam_at_flash.net wrote on 12/15/00 12:57 PM:

> 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;
> /

You can also do the same within forms. But instead of using the UTL_FILE package, you use TEXT_IO package.

I don't know why they name them different. Perhaps designed by different teams.

Ric. Received on Fri Dec 15 2000 - 21:52:40 CST