Using Oracle Procedures to create a flat file

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
ssmith001Asked:
Who is Participating?
 
seazodiacConnect With a Mentor Commented:
markgeer ask you to close this question, accept one of answer if you think it's correct, then assign points.
0
 
seazodiacCommented:
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
 
ssmith001Author Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
seazodiacCommented:
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
 
ssmith001Author Commented:
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
 
seazodiacCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ssmith001Author Commented:
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
 
seazodiacCommented:
use the combination of CHR(10) and CHR(13).

0
 
ssmith001Author Commented:
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
 
seazodiacCommented:
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
 
ssmith001Author Commented:
I did and I only get the header records with no details.
0
 
seazodiacCommented:
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
 
ssmith001Author Commented:
That query return roughly 20K rows. Here are a few of them:

P RS1104008166 DOM000000000000010000080000000 EA 1AA0174R1
P RS1104008166 DOM000000000000007000050000000 EA 1AA0175R1
0
 
seazodiacCommented:
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
 
ssmith001Author Commented:
Done. All I get it the header record w/o the detail records
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ssmith001Author Commented:
When broken apart, the second query returns 1000's of rows.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ssmith001Author Commented:
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
 
ssmith001Author Commented:
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
 
seazodiacCommented:
this seems awfully eaiser than your first question.

try this:

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

0
 
ssmith001Author Commented:
Again, all I get is the 'H' rows with no 'P' rows
0
 
ssmith001Author Commented:
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
 
seazodiacCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
ssmith001Author Commented:
This is my 1st time using this, so I'm not sure I understand what you are asking me to do.
0
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.

All Courses

From novice to tech pro — start learning today.