Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> UTL_FILE help appending

UTL_FILE help appending

From: Pawan <pawanputrahanuman_at_yahoo.com>
Date: 20 May 2003 05:56:52 -0700
Message-ID: <84857442.0305200456.44798517@posting.google.com> 





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.
Received on Tue May 20 2003 - 07:56:52 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US