| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: UTL_FILE help appending
Thanks Daniel and Rauf. With your help I am very close to getting the
results that I want. I just need some help in "referencing a cursor"
(if it is the right term).
What is happening now is that in the CURSOR cust_cur I am getting the
distinct cust_corp_nm (in the example I am selecting 3) and I want to
reference the same cust_corp_nm in the WHERE clause of the other 2
(forecast_cur and lastyrsale_cur ) cursor so that I get the results
for the same customer.
Where is the right place to do it - i.e. select the value of the
cust_corp_nm into a variable and then use it in the other two cursors.
The modified code is as under....
Really appreciate your help.
Pawan
The code starts here
set pages 10000;
set lines 600;
set serveroutput on size 1000000;
DECLARE
l_file UTL_FILE.file_type; l_last_filename varchar2(255) := '0000000'; l_cust varchar2(35) := '00000_Q.XLS'; l_custid varchar2(6) :=' '; l_custnm varchar2(35) := ' '; l_custcorpnm varchar2(35) := ' '; l_custid_prev varchar2(6) := ' '; l_fcst_mth1 number(13,2) := 0.00; l_fcst_mth2 number(13,2) := 0.00; l_fcst_mth3 number(13,2) := 0.00; l_fcst_qtr number(13,2) := 0.00; l_lastyrsale_mth1 number(13,2) := 0.00; l_lastyrsale_mth2 number(13,2) := 0.00; l_lastyrsale_mth3 number(13,2) := 0.00; l_lastyrsale_qtr number(13,2) := 0.00;CURSOR cust_cur is
cust_rec cust_cur%ROWTYPE;
CURSOR forecast_cur is
select
v.cust_corp_id c1, v.CUST_CORP_NM c2, SUM(decode(to_char(b.FCST_DT,'MON'),'JAN',b.FCST_AMT,null)) c3, SUM(decode(to_char(b.FCST_DT,'MON'),'FEB',b.FCST_AMT,null)) c4, SUM(decode(to_char(b.FCST_DT,'MON'),'MAR',b.FCST_AMT,null)) c5, SUM(decode(to_char(b.FCST_DT,'MON'),'APR',B.FCST_AMT,null)) c6, SUM(decode(to_char(b.FCST_DT,'MON'),'DEC',B.FCST_AMT,null)) c14 from cso.FCST b, cso.cust_corp_ppl_vw v where b.cust_corp_id= v.cust_corp_id and to_char(b.FCST_DT, 'YYYY/MM') in ( '2003/01', '2003/02','2003/03')
and v.cust_corp_id in ( '040116','007300','002620') group by v.cust_corp_id, v.CUST_CORP_NM; forecast_rec forecast_cur%ROWTYPE;
SUM(decode(to_char(d.dsar_date,'MON'),'JAN',d.dsar_frd_amt,null)) lc3,
SUM(decode(to_char(d.dsar_date,'MON'),'FEB',d.dsar_frd_amt,null)) lc4,
SUM(decode(to_char(d.dsar_date,'MON'),'MAR',d.dsar_frd_amt,null)) lc5,
SUM(decode(to_char(d.dsar_date,'MON'),'APR',d.dsar_frd_amt,null)) lc6,
SUM(decode(to_char(d.dsar_date,'MON'),'DEC',d.dsar_frd_amt,null)) lc14
from cso.daily_dsar_vw d,
cso.buying_customer b,
cso.cust_corp_ppl_vw v
where
d.customer_id = b.customer_id
and b.corporation_id = v.cust_corp_id
and to_char(d.dsar_date,'YYYY/MM') in ('2002/01', '2002/02',
'2002/03')
group by v.cust_corp_id, v.CUST_CORP_NM; lastyrsale_rec lastyrsale_cur%ROWTYPE;BEGIN
FETCH cust_cur into l_cust; EXIT when cust_cur%NOTFOUND; l_cust:=l_cust||' Q.XLS'; l_custid := ' '; l_custid_prev := ' '; l_fcst_mth1 := 0.00; l_fcst_mth2 := 0.00; l_fcst_mth3 := 0.00; l_fcst_qtr := 0.00; l_lastyrsale_mth1 := 0.00; l_lastyrsale_mth2 := 0.00; l_lastyrsale_mth3 := 0.00; l_lastyrsale_qtr := 0.00;l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767); DBMS_OUTPUT.PUT_LINE('Start of FOR loop '||l_cust); IF UTL_FILE.IS_OPEN(l_file) THEN DBMS_OUTPUT.PUT_LINE('File is Opened for Write');
ELSE DBMS_OUTPUT.PUT_LINE('Still Closed');
END IF;
l_last_filename := '9999999_Q.XLS';
FOR i in forecast_cur
LOOP
l_custid := i.c1;
IF l_cust <> l_last_filename THEN
UTL_FILE.PUT_LINE(l_file,'Jan|Feb|Mar|Q1'); UTL_FILE.PUT_LINE(l_file, nvl(i.c3,0) ||'|'|| nvl(i.c4,0) ||'|'||nvl(i.c5,0)||'|'); l_custid_prev := i.c1;
LOOP UTL_FILE.PUT_LINE(l_file, nvl(j.lc3,0) ||'|'|| nvl(j.lc4,0) ||'|'||nvl(j.lc5,0)||'|'); l_last_filename := l_cust; END LOOP;
IF (UTL_FILE.IS_OPEN(l_file)) THEN UTL_FILE.FCLOSE(l_file);
RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
WHEN utl_file.internal_error THEN
 |
 |