Re: Help with UTL_FILE

With the help from David Romeo, I was able to solve my problem. The new 
code looks like
-----------------
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);
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');
end if;
l_last_filename := l_fso;
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;
/
------------------------
Thanks

> 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 protected].