?
Solved

Extract data to text using 8i pl-sql

Posted on 2006-04-24
12
Medium Priority
?
731 Views
Last Modified: 2012-05-05
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.

0
Comment
Question by:Ade101
  • 6
  • 6
12 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16525244
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16525248
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16525283
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Ade101
ID: 16535064
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
 
LVL 19

Accepted Solution

by:
actonwang earned 300 total points
ID: 16535083
sure,

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

Author Comment

by:Ade101
ID: 16581124
what code i can use to create and test the extract file on my local development pc
thanks
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16581264
see my previous post ... there is an example.
0
 

Author Comment

by:Ade101
ID: 16611041
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16611145
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
 

Author Comment

by:Ade101
ID: 16611907
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
 

Author Comment

by:Ade101
ID: 16659491
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
 

Author Comment

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question