Re: Writing Multiple cursors and appending the results using UTL_FILE

I think that you should not ask to debug your code for another people. You should see more carefully error messages at the bottom. For example:
> ERROR at line 62:
> ORA-06550: line 62, column 31:
> PLS-00302: component 'CUST_CORP_ID' must be declared
 

Anyway, I saw some problem in your code.  

> The PL/SQL 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) := '00000Q.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'),'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','H70344','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'),'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','H70344','002620') AND
> v.cust_corp_id=forecast_rec.cust_corp_id
You have renamed to c1 from cust_corp_id at SELECT list.

   ---> select v.cust_corp_id c1, .....

> group by v.cust_corp_id, v.CUST_CORP_NM;
> lastyrsale_rec lastyrsale_cur%ROWTYPE;
> BEGIN
> l_last_filename := '9999999Q.XLS';
> IF (UTL_FILE.IS_OPEN(l_file)) THEN
> UTL_FILE.FCLOSE(l_file);
> END IF;
> 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);
> FOR i in forecast_cur
> LOOP
> l_cust := i.c2 || 'Q.XLS';
> l_custid := i.c1;
> IF l_cust <> l_last_filename THEN
> IF (UTL_FILE.IS_OPEN(l_file)) THEN
> IF forecast_cur%ROWCOUNT <> 1 AND l_custid <> l_custid_prev 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)||'|');
> END IF;
> l_custid_prev := i.c1;
> END IF;
> l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767);
> 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)||'|');
> 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 (UTL_FILE.IS_OPEN(l_file)) THEN
> IF lastyrsale_cur%ROWCOUNT <> 1 AND l_custid <> l_custid_prev
> THEN
> -- UTL_FILE.PUT_LINE(l_file,'Jan|Feb|Mar|Q1');
> UTL_FILE.PUT_LINE(l_file, nvl(i.lc3,0) ||'|'|| nvl(i.lc4,0)
There is no "i.lc3".
 ---> FOR i in forecast_cur  

Is it "j.lc3" or "i.c3"?  

> ||'|'||nvl(i.lc5,0)||'|');
> END IF;
> l_custid_prev := i.c1;
> END IF;
> l_file := UTL_FILE.FOPEN('/tmp',l_cust,'a',32767);
> UTL_FILE.PUT_LINE(l_file,'Jan|Feb|Mar|Q1');
> UTL_FILE.PUT_LINE(l_file, nvl(i.lc3,0) ||'|'|| nvl(i.lc4,0)
There is no "i.lc3". Is it "j.lc3" or "i.c3"?

> ||'|'||nvl(i.lc5,0)||'|');
> END IF;
> UTL_FILE.FCLOSE(l_file);
> 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;
> END;
> /
> ------------------------------
> THE CODE ENDS
> ----------------------------
> AM GETTING THE FOLLOWING ERROR
> ----
> SQL> _at_multiple_cursor.sql
> v.cust_corp_id=forecast_rec.cust_corp_id
> *
> ERROR at line 62:
> ORA-06550: line 62, column 31:
> PLS-00302: component 'CUST_CORP_ID' must be declared
> ORA-06550: line 40, column 1:
> PL/SQL: SQL Statement ignored
> ORA-06550: line 39, column 8:
> PLS-00341: declaration of cursor 'LASTYRSALE_CUR' is incomplete
> or malformed
> ORA-06550: line 64, column 18:
> PL/SQL: Item ignored
> ORA-06550: line 111, column 35:
> PLS-00302: component 'LC3' must be declared
> ORA-06550: line 111, column 3:
> PL/SQL: Statement ignored
> ORA-06550: line 117, column 35:
> PLS-00302: component 'LC3' must be declared
> ORA-06550: line 117, column 3:
> PL/SQL: Statement ignored
>
> Thanks in adavnce for the help.
Received on Wed May 21 2003 - 01:29:51 CEST