Re: Writing Multiple cursors and appending the results using UTL_FILE

Thanks Sir. I had already fixed that problem. But I am now getting some other errors. I am looking into it.

Thanks

tonkuma_at_jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0305201529.9537239_at_posting.google.com>...
> 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 - 14:43:11 CEST