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.

Ade101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

actonwangCommented:
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").
0
actonwangCommented:
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;
    /
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ade101Author Commented:
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
0
actonwangCommented:
sure,

    as I noted, you can do that using UTL_FILE package.
    follow my steps and try it ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ade101Author Commented:
what code i can use to create and test the extract file on my local development pc
thanks
0
actonwangCommented:
see my previous post ... there is an example.
0
Ade101Author Commented:
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
0
actonwangCommented:
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.
0
Ade101Author Commented:
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
0
Ade101Author Commented:
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
0
Ade101Author Commented:
i am using 8i and there seem to be no UTL_FILE.frename function by which i can rename the file
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.