Solved

Using Oracle Procedures to create a flat file

Posted on 2003-11-04
27
2,287 Views
Last Modified: 2008-01-09
I need to create an Oracle procedure that when run, will write the output to a flat file. The flat file will consist of a 'H'eader record followed by a 'D'etail record:

H99935...
D99935...
H99936...
D99936...


The '99935' in the header and detail are created using a sequence (NEXTVAL/CURRVAL).  I have the two queries that I need to use to create each of these records, but I'm struggling with how to write them to a file.  Can someone help?

Steve
0
Comment
Question by:ssmith001
  • 13
  • 10
  • 4
27 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9681135
use UTL_FILE package.

1. to use UTL_FILE package, set the UTL_FILE_DIR in the init<SID>.ora parameter file, this directory will be the one that UTL_FILE write to or read from
2. call the UTL_FILE.fopen() and UTL_FILE.put_line() and UTL_FILE.fclose() in the procedure.

for example ( in pseudocode):

CREATE OR REPLACE PROCEDURE <name>
AS
l_file UTL_FILE.fileType;
UTL_FILE.fopen('c:\temp', 'test.txt', 'w');

FOR i IN 1...1000 LOOP
         --retrieve the first header record.
         ---retrieve the first detail record.
         --call the utl.put_line() to write to the file.
END LOOP;
UTL_FILE.fclose();

END;
/


Hope this helps

                   
0
 

Author Comment

by:ssmith001
ID: 9681195
I was told by the DBA not to use the UTL_FILE as the userid I will be using won't have access to write files to the server.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9681296
as an alternative , you can use DBMS_OUTPUT.put_line() to write the output the Console and use SQLPLUS utility SPOOL to the file in your local machine.

 try this:
the following method is executed in SQLPLUS window: You can save all the following in a script and execute it in sqlplus.


set SEVEROUTPUT ON SIZE 1000000
set HEADING OFF
set FEEDBACK OFF
set PAGESIZE 0
set LINSIZE 300
set trimspool ON
SPOOL test.txt
begin
FOR i IN 1...1000 LOOP
        --retrieve the first header record.
        ---retrieve the first detail record.
        --call the DBMS_OUTPUT.put_line() to write to the console
END LOOP;
END;
/

SPOOL OFF


0
 

Author Comment

by:ssmith001
ID: 9681399
Thanks so much for your help. I truly appreciate it.   I need some clarification. To retrieve the header and detail records, do I simply insert the SQL statements in the appropriate section? Also, what would be the syntax to write the lines to the DMBS_OUTPUT?  What goes in the .put_line(  )?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9681439
for what goes into DBMS_OUTPUT.put_line(), any string, number, date format will be ok.
for example:

dbms_output.put_line('a string' || 30|| sysdate);

your first question: isn't that sth you should figure out yourself ? it's all about conditional control and looping structure.

Enjoy!!!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 9681994
Using PL\SQL procedures to create flat files from Oracle is one way to get the job done, but certainly not the only way, and not the best way for all cases.

Either your DBA is mistaken, or is intentionally misleading you, or you misunderstood what he/she said.  Utl_file uses the security of the account that runs the database (not the security of the logged-in user), so utl_file always has access to files on the server as long as they are specified in the init*.ora file or spfile with Oracle9, or as a "directory" in Oracle9.  Maybe the user doesn't have access to the file(s) that utl_file can create on the server, but that's not exactly what you said in the question.

The simplest (and maybe the fastest) way to get text files on a client machine is via a relatively simple SQL script and the "spool" command.

For a simple test, try this in SQL*Plus:
spool C:\temp\tab_list.txt
select owner, table_name from all_tables
where owner not like '%SYS%'
order by owner, table_name;
spool off;

If C:\temp is not valid on your PC, just change that to any valid, local directory.

In answer to your question, you will need something a bit more complicated like this:

(I'm assuming you have a sequence named "my_seq", a header file named "header" and a detail_file name "detail".)

spool C:\temp\my_file.txt
select 'H'||my_seq.nextval, h.column_a, h.column_b [etc.] ||chr(10)
'D'||my_seq.currval, d.column_a, d.column_b  [etc.]
from header h, detail d
where d.[key_column] = h.[key_column];
spool off;

Note: The "chr(10)" will give you a linefeed after the header info

By default, SQL*Plus will give you fixed-length output.  If you want something else, like the trailing blanks trimmed, and/or a comma or other delimiter between each value that is also possible.  Just leave another comment here with more details if you need more help.

0
 

Author Comment

by:ssmith001
ID: 9682756
I have tried the CHR(10) already but it's not working. All I get are header records with no details. The SQL statement that produces the header comes from Table A, and the SQL statement that produces the detail comes from Table A and Table B. How can I use the CHR(10) to skip to a new line when the two queries go after data on different tables?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9682764
use the combination of CHR(10) and CHR(13).

0
 

Author Comment

by:ssmith001
ID: 9682786
Can you clarify what you are talking about? Here are the two queries:

For the header...

      select 'H' -- RECORD_TYPE ('H' = header record)
           ||'A' -- TRANS_CODE
             ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.NEXTVAL,'000009')) -- ID
             ||' DOM'
             ||' HOST' -- OTYPE
             ||' '||a.source -- SOURCE_ID
             ||' '||a.dest -- DEST_ID
             ||' $0.00USD' -- REVENUE and REVENUE_CURRENCY
             ||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- EARLY_DEL
             ||to_char(a.schedarrivdate,'YYYYMMDDHHMM') -- LATE_DEL
             ||to_char(a.availtoshipdate,'YYYYMMDDHHMM') -- EARLY_AVAIL
             ||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- LATE_AVAIL
             ||' ' -- GROUP_NAME
             ||'N' -- INBOUND
             ||'N' -- NOT_ROUTE_FLAG
             ||'2' -- CONS_FLAG
             ||'PPD' -- FREIGHT_TERMS
             ||'N' -- NOT_UPLOAD_FLAG
             ||'0' -- EXTERNAL_STAT
             ||'N' -- SHIP_MODE
             from stsc.recship a;

For the Detail...

      select 'P' -- RECORD_TYPE ('P' = commodity record)
             ||' ' -- BLANK
             ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.CURRVAL,'000009')) -- ID
             ||' DOM'
             ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.vol),6,0) ELSE '000000' END -- CUBE
             ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.wgt),9,0) ELSE '000000000' END -- WEIGHT
             ||lpad(a.qty,6,0) -- PIECES
             ||CASE WHEN b.UnitsPerPallet != 0 THEN lpad(round(a.qty/b.unitsperpallet),6,0) ELSE '000000' END -- PALLET
             ||CASE WHEN b.UOM != ' ' THEN ' '||b.UOM ELSE ' ' END -- PACKAGE_TYPE_ID
             ||' 1' -- LINE_NUMBER
             ||rpad(a.item,35) -- ITEM_ID
             from stsc.recship a, stsc.item b, stsc.loc c
             where a.item = b.item
             and a.source = c.loc
             and c.UDC_TPORT_LOAD_SW = 1;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9682838
do it in one query, try this:


   select 'H' -- RECORD_TYPE ('H' = header record)
         ||'A' -- TRANS_CODE
          ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.NEXTVAL,'000009')) -- ID
           ||' DOM'
          ||' HOST' -- OTYPE
          ||' '||a.source -- SOURCE_ID
          ||' '||a.dest -- DEST_ID
          ||' $0.00USD' -- REVENUE and REVENUE_CURRENCY
          ||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- EARLY_DEL
          ||to_char(a.schedarrivdate,'YYYYMMDDHHMM') -- LATE_DEL
          ||to_char(a.availtoshipdate,'YYYYMMDDHHMM') -- EARLY_AVAIL
          ||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- LATE_AVAIL
          ||' ' -- GROUP_NAME
          ||'N' -- INBOUND
          ||'N' -- NOT_ROUTE_FLAG
          ||'2' -- CONS_FLAG
          ||'PPD' -- FREIGHT_TERMS
          ||'N' -- NOT_UPLOAD_FLAG
          ||'0' -- EXTERNAL_STAT
          ||'N' -- SHIP_MODE
          || CHR(10)||CHR(13)
          || 'P' -- RECORD_TYPE ('P' = commodity record)
          ||' ' -- BLANK
          ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.CURRVAL,'000009')) -- ID
           ||' DOM'
          ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.vol),6,0) ELSE '000000' END -- CUBE
          ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.wgt),9,0) ELSE '000000000' END -- WEIGHT
          ||lpad(a.qty,6,0) -- PIECES
          ||CASE WHEN b.UnitsPerPallet != 0 THEN lpad(round(a.qty/b.unitsperpallet),6,0) ELSE '000000' END -- PALLET
          ||CASE WHEN b.UOM != ' ' THEN ' '||b.UOM ELSE ' ' END -- PACKAGE_TYPE_ID
          ||' 1' -- LINE_NUMBER
          ||rpad(a.item,35) -- ITEM_ID
          from stsc.recship a, stsc.item b, stsc.loc c
          where a.item = b.item
          and a.source = c.loc
          and c.UDC_TPORT_LOAD_SW = 1;
0
 

Author Comment

by:ssmith001
ID: 9682881
I did and I only get the header records with no details.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9682952
can you post the results of this query? how many rows are returned?


    select 'P' -- RECORD_TYPE ('P' = commodity record)
          ||' ' -- BLANK
          ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.CURRVAL,'000009')) -- ID
           ||' DOM'
          ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.vol),6,0) ELSE '000000' END -- CUBE
          ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.wgt),9,0) ELSE '000000000' END -- WEIGHT
          ||lpad(a.qty,6,0) -- PIECES
          ||CASE WHEN b.UnitsPerPallet != 0 THEN lpad(round(a.qty/b.unitsperpallet),6,0) ELSE '000000' END -- PALLET
          ||CASE WHEN b.UOM != ' ' THEN ' '||b.UOM ELSE ' ' END -- PACKAGE_TYPE_ID
          ||' 1' -- LINE_NUMBER
          ||rpad(a.item,35) -- ITEM_ID
          from stsc.recship a, stsc.item b, stsc.loc c
          where a.item = b.item
          and a.source = c.loc
          and c.UDC_TPORT_LOAD_SW = 1;


0
 

Author Comment

by:ssmith001
ID: 9683072
That query return roughly 20K rows. Here are a few of them:

P RS1104008166 DOM000000000000010000080000000 EA 1AA0174R1
P RS1104008166 DOM000000000000007000050000000 EA 1AA0175R1
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 9683540
can you copy and paste this to your SQLPLUS window?

select 'H' -- RECORD_TYPE ('H' = header record)
        ||'A' -- TRANS_CODE
         ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.NEXTVAL,'000009')) -- ID
           ||' DOM'
         ||' HOST' -- OTYPE
         ||' '||a.source -- SOURCE_ID
         ||' '||a.dest -- DEST_ID
         ||' $0.00USD' -- REVENUE and REVENUE_CURRENCY
         ||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- EARLY_DEL
         ||to_char(a.schedarrivdate,'YYYYMMDDHHMM') -- LATE_DEL
         ||to_char(a.availtoshipdate,'YYYYMMDDHHMM') -- EARLY_AVAIL
         ||to_char(a.schedshipdate,'YYYYMMDDHHMM') -- LATE_AVAIL
         ||' ' -- GROUP_NAME
         ||'N' -- INBOUND
         ||'N' -- NOT_ROUTE_FLAG
         ||'2' -- CONS_FLAG
         ||'PPD' -- FREIGHT_TERMS
         ||'N' -- NOT_UPLOAD_FLAG
         ||'0' -- EXTERNAL_STAT
         ||'N' -- SHIP_MODE
         || CHR(10)||CHR(13)
         || 'P' -- RECORD_TYPE ('P' = commodity record)
         ||' ' -- BLANK
         ||'RS'||to_char(sysdate,'MMDD')||trim(to_char(MFU_INFC_STSC.I490_ORDER_NUM.CURRVAL,'000009')) -- ID
           ||' DOM'
         ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.vol),6,0) ELSE '000000' END -- CUBE
         ||CASE WHEN b.vol != 0 THEN lpad(round(a.qty*b.wgt),9,0) ELSE '000000000' END -- WEIGHT
         ||lpad(a.qty,6,0) -- PIECES
         ||CASE WHEN b.UnitsPerPallet != 0 THEN lpad(round(a.qty/b.unitsperpallet),6,0) ELSE '000000' END -- PALLET
         ||CASE WHEN b.UOM != ' ' THEN ' '||b.UOM ELSE ' ' END -- PACKAGE_TYPE_ID
         ||' 1' -- LINE_NUMBER
         ||rpad(a.item,35) -- ITEM_ID
         from stsc.recship a, stsc.item b, stsc.loc c
         where a.item = b.item
         and a.source = c.loc
         and c.UDC_TPORT_LOAD_SW = 1;
0
 

Author Comment

by:ssmith001
ID: 9686190
Done. All I get it the header record w/o the detail records
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 9686422
You only need one query to do this (as seazodiac suggested), since your second query includes the table your first query is based on.  It looks like there may be a problem with the join criteria in your second query.  If you try that by itself, does it return any rows?
0
 

Author Comment

by:ssmith001
ID: 9686542
When broken apart, the second query returns 1000's of rows.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 9686712
If that is the number of rows you expect, then modify that second query to also get the header values you need and put the chr(10) character between the header and detail info.

If that number of rows is not what you expect, then you need to modify that second query to get the number of rows you want.
0
 

Author Comment

by:ssmith001
ID: 9687328
I may have missed somthing in your last comment. Isn't putting them together into 1 query and separating the header and detail by a CHR(10), exactly what I have already done?
0
 

Author Comment

by:ssmith001
ID: 9687378
Let me back up and shaw you what I'm looking to do, as it appears that there is some confusion. For example, I have the following records in a table:

ITEM         SOURCE          DEST

A12345     80001             80012
B45678     80010             80035

What I need from the query is this data in this format (header rec followed by detail rec):

H 80001 80012
P A12345
H 80010 80035
P B45678
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9687420
this seems awfully eaiser than your first question.

try this:

select 'H'||' ' || source || ' ' ||dest || chr(10)||chr(13)
          'P'|| ' ' || item
FROM <table_name>

0
 

Author Comment

by:ssmith001
ID: 9687489
Again, all I get is the 'H' rows with no 'P' rows
0
 

Author Comment

by:ssmith001
ID: 9687723
Crap!!!  I had SET WRAP=OFF which is what was causing the detail record not to appear. Thanks SO MUCH for all your help. This has been a learning experience!

Thanks!!

Steve
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9687758
what a wild goose chase!!!
I have NOT thought of that...
But if you post the complete result from the beginning, we will definitely resolve this faster.

Your output will get TRUNCATED , and you should notice this early....


I am glad you found this cause....
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 9688443
OK, if you got the help you were looking for, then it is expected that you will accept one (or more) of the responses as the answer.  Can you do that please?
0
 

Author Comment

by:ssmith001
ID: 9688463
This is my 1st time using this, so I'm not sure I understand what you are asking me to do.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 9688804
markgeer ask you to close this question, accept one of answer if you think it's correct, then assign points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now