asked on

Extract data to text using 8i pl-sql

Hello wizards

I have worked in oracle 9i but 8i is new to me. I need to extract some data which i assume is in the record cursor to a text file output.

I have never extracted data to a text file using pl-sql. Some of the things I want to do in the pl-sql procedure for above task is e.g.

1)hardcode file name for every new text file created by this procedure ...

e.g. 'File name ABC/ref10029'

2) how to move to new line in the text file. e.g. first line would have file name , second would have record for employee and third line would have order details and something like that. My question is how to move to a new line while extracting data to a text file using oracle 8i pl-sql.

Thats for now.
Thanks for your help/code sample.

Acton Wang

use UTL_FILE package, in oracle 8i, you have to use UTL_FILE_DIR to specify the directories to access (in 9i , it is "create directory" as you know").

Acton Wang

refer to this:

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/utl_file.htm#998101

Acton Wang

this is a example for you :
--put c:\temp in UTL_FILE_DIR...

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('c:\temp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/

Ade101

ASKER

Lets say If i have 3 different datasets/cursors in my PL/SQL procedure. One has file header information, second has record header information and the third cursor has the actual data.

What I am trying to do is create a text file... write the file header cursor data in this text file in first line only once. then from next line write into text file from my second cursor i.e. record header which will repeat after after the group by Order changes. From third line I want to write my actual data in the text file..

the text output is something like this:

A)File Name , Version 1.0.0.1 Extracted by 'Developer Name' // //
B) AR-REALLOCATION / // DATE_01 // 2005/12/23 / / / //
C) ZBSEG 01656460 / / / / / / 20060214J101

where (A) is first line of text file which will be written once
(B) is the record's header and this will repeat once the DATE_01 will change for the group.
(C) these are the detail records.

Any help on this would be highly appreciated

Thanks a lot

ASKER CERTIFIED SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ade101

ASKER

what code i can use to create and test the extract file on my local development pc
thanks

Acton Wang

see my previous post ... there is an example.

Ade101

ASKER

hi

Its creating the output fine but i am receiving following error:

***************************
WRITE OUTPUT ERROR!!! -20000ORA-20000: ORU-10028: line length overflow, limit of
255 bytes per line

My each line of recod is approx 350 or cheracters. When i reduce the output string to hold something less then 255 then it works if more then it gives this error.

Is there a way i can increase this limit.

Thanks

Acton Wang

It seems that it is the error from DBMS_OUTPUT.PUT_LINE(..) .

Are you using it? it has limitation of 255 characters. remove those lines.

Ade101

ASKER

hmm` . i thought so .. else there should not be any limitation of how big line you are writing to the output file right? ... i'll test the output file and will get back if there is any problem ...

thanks alot

Ade101

ASKER

one more concer if you could help on this ....

i have written a flat file say ''abcflat'' using this package. The file has coma seperated records e.g "asd","06:12:23","AS2" etc ..
now before i exit my procedure i want to re-name this file and add a .CSV extension to it once it is created and before before it is creatred.

The reason for doing this is that if i hard code the file names and make it abcflat.CVS, it works fine but while i open this file in the note pad, i dont see the "". the records show up like asd,06:12:23,AS2 insted of "asd","06:12:23","AS2".

If i create the flat file first without any extention, it creates it and if i open it i see the data in a format as it is required i.e. "asd","06:12:23","AS2" and i can even change the extention into .CSV and so it works fine.

The alternate way i have found for doing what i want it first create it as a flat file and later once it is created just change the file name extention to .CSV...

Is it possible ///

thanks for your help
Ade

Ade101

ASKER

i am using 8i and there seem to be no UTL_FILE.frename function by which i can rename the file