Re: UTL_FILE help appending

Pawan wrote:

> I have a simple question but as a beginner I am stuck.
> I need to create a report in Excel which has 5 secions (not 5
> worksheets but only one). There should be one flat file per customer
> and it should be named after the customer (if the customer is Walmart
> then the file should be walmart.XLS). The Excel report should finally
> look like
>
> This is Section I
>
> <Current year>
> <Name of the Customer>
>
>
> VOLUME First Quarter
> Jan Feb Mar Q1
> Target /Quota C11 C21 C31 C41
> Forecast C12 C22 C32 C42
> Previous Year C13 C23 C33 C43
> Actual Sales C14 C24 C34 C44
> % Actual vs Target C15 C25 C35 C45
> <other Calculated field>
> <other Calculated field>
> <other Calculated field>
>
> This is Section II
>
> SPEND Current Year
> Product1 Product2 Product3 Other Products
> Budget
> Plan
> Previous Year
> Actual
> % Actual vs Budget
> <other Calculated field>
> <other Calculated field>
> <other Calculated field>
>
> SECTION III
> ....
> SECTION IV
> ......
> SECTION V
> ....
>
> I have tried and written a PL/SQL and am getting errors. Please help
> me debug it. I need to use (I think) the APPEND option of the
> UTL_FILE.FOPEN but don't know how to. I have SQL queries for all the
> sections but the code shows only 2 for the first section of the
> report.
>
> 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
> 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)
> ||'|'||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)
> ||'|'||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> @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.

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

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)