RE: Help with UTL_FILE

Tthe only change you need is to put the put_line's 
for your heading outside of the FOR NEXT loop.

put them after the BEGIN statement, and before the FOR in your loop.
by being inside the loop they will keep repeating...
the open of your output file should also be before the FOR, and after the
BEGIN
the close should be after the NEXT statement...
only things you want to repeat with the loop should be in the loop area

opening and closing and headers are things that are done one only, before
and after the loop..

-----Original Message-----
From: Sanjay Raj [mailto:[email protected]]
Sent: Friday, January 03, 2003 11:54 AM
To: Oracle
Subject: [oracle] Help with UTL_FILE


I am new to Oracle and with help from the Oracle
Manuals etc. I have managed to code my first PL/SQL.
But I cannot get it to do everything. 
Here is my requirement
I have a script (dedreport.sql) which joins 3-4 tables
and gets data. I need to spool the results of this
join into separate CSV (pipe delimited though) files.
I am able to achieve this through UTL_FILE. Now I need
to get the DATE and a HEADER record per file. As I
have it coded now the DATE and HEADER Record appear
before every record in a file - as it is within the
loop. I tried various options but cannot get it right.
Will appreciate if someone could help.
Here is my code
--------------------------
set pages 10000 lines 600
SET SERVEROUTPUT ON SIZE 1000000;
declare
l_last_filename varchar2(255) := '00000';
l_fso varchar2(25) := '00000.csv';
l_file UTL_FILE.FILE_TYPE;
cursor ded is
select
nvl(fsu.field_sales_org_id,'9999') c1,
nvl(fsu.fs_organization_name,'910000 OPEN SALES UNIT')
c2,
ded.fi_number c3,
nvl(ded.fi_name,'UNIDENTIFIED') c4,
ded.fi_city c5,
ded.fi_state c6,
ded.DEDUCTION_LST_UPD_DT c28
from
cso.field_sales_unit@cso_prod2 fsu,
spot.deductions ded,
spot.fi_ppl_resolved fpr,
spot.security_ppl ppl
where
fsu.field_sales_org_id(+) = ppl.field_sales_org_id and
ded.fi_number = ltrim(fpr.fi_number(+),'0') and
fpr.ppl_number = ppl.sap_plannable_account_id(+)
order by
fsu.field_sales_org_id,
ded.fi_number,
fpr.ppl_number,
ded.deduction_cd;
begin
for i in ded loop
l_fso := i.c1 || '.csv';
if l_fso <> l_last_filename
then
if (UTL_FILE.IS_OPEN(l_file))
then
UTL_FILE.FCLOSE(l_file);
end if;
l_file :=
UTL_FILE.FOPEN('/spot/prod/app/process',l_fso,'w',32767);
l_last_filename := l_fso;
end if;
UTL_FILE.PUT_LINE(l_file,i.c28);
UTL_FILE.PUT_LINE(l_file,
'Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State');
UTL_FILE.PUT_LINE(l_file,i.c1
||'|'|| i.c2 ||'|'||i.c3||'|'||i.c4||'|'||i.c5
||'|'||i.c6|| '|');
end loop;
if (UTL_FILE.IS_OPEN(l_file))
then
UTL_FILE.FCLOSE(l_file);
end if;
end;
/
-------------------------------------
AND HERE IS WHAT I GET IN ONE OF THE FILES.

-------------------------------------------
2003-01-02
Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
2003-01-02
Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
2003-01-02
Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
2003-01-02
Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
2003-01-02
Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
| 
-----------------------------------------
AND HERE IS WHAT I NEED
------------------------------------------
2003-01-02
Field Sales Org ID|Sales Office|Customer
Number|Customer Name|City|State

0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
|
0294|221002 AHOLD-BI LO|5002550|BI-LO LLC             
     |MAULDIN	|SC
| 
----------------------------------------------
So the DATE and the HEADER Record appear only once per
file and not before every record in a file.

Thanks in adavance.

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.




---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].