Re: UTL_FILE help appending

From: Pawan <pawanputrahanuman_at_yahoo.com>
Date: 27 May 2003 11:02:47 -0700
Message-ID: <84857442.0305271002.19d1aaee@posting.google.com>

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3ECA62AB.1207BAAD_at_exxesolutions.com>...  

> Pull your SQL statements out of your procedure and run them in SQL*Plus to
> debug.

Thanks Daniel. I have run the SQLs on SQL prompt and even modified my code a little. Now I am able to run the code but then only it creates one(1) file instead of the 4 (as I am having 3 Cust_corp_id in the WHERE clause). This file is named 00000_Q.XLS and contains data fron the first cursor (forecast_cur) and nothing from the second cursor(lastyrsale_cur). Please help. I am new to Oracle. 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(45) := '00000_Q.XLS';
l_custid                     varchar2(6) :=' ';
l_custnm                     varchar2(45) := ' ';
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 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,
---(code snipped here)
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;
-- prompt PRIOR_YEAR_ACTUAL_SALES
CURSOR lastyrsale_cur is
select
v.cust_corp_id lc1,
v.CUST_CORP_NM lc2,

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,
---(code snipped here)
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')
-- and v.cust_corp_id in ( '040116','007300','002620') AND v.cust_corp_id=forecast_rec.c1
group by v.cust_corp_id, v.CUST_CORP_NM; lastyrsale_rec lastyrsale_cur%ROWTYPE;
BEGIN

l_last_filename := '9999999_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;

DBMS_OUTPUT.PUT_LINE('Start of FOR loop '||l_cust); l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767); 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;
FOR i in forecast_cur
LOOP
l_cust := i.c2 || '_Q.XLS';
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;
END IF;

--#####################################################################################

--# Need to keep this file OPEN and then APPEND data returned from other Cursors.

--#######################################################################################

FOR j in lastyrsale_cur
LOOP
l_cust := i.c2 || '_Q.XLS';
l_custid := i.c1;

IF              l_cust <> l_last_filename THEN
   IF 	lastyrsale_cur%ROWCOUNT <> 1 AND l_custid <> l_custid_prev THEN

UTL_FILE.PUT_LINE(l_file, nvl(j.lc3,0) ||'|'|| nvl(j.lc4,0) ||'|'||nvl(j.lc5,0)||'|');

   END IF;
l_custid_prev := i.c1;
END IF;
l_last_filename := l_cust;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('End of FOR loop '||l_cust); IF (UTL_FILE.IS_OPEN(l_file)) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path'); WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode'); WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle'); WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation'); WHEN utl_file.read_error THEN
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
RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error'); WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error'); END;
/

---

code ends Received on Tue May 27 2003 - 13:02:47 CDT