Writing Multiple cursors and appending the results using UTL_FILE

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

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;

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,

cso.daily_dsar_vw d,
cso.buying_customer b,
cso.cust_corp_ppl_vw v

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

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

ORA-06550: line 62, column 31:
PLS-00302: component 'CUST_CORP_ID' must be declared
ORA-06550: line 40, column 1:

ORA-06550: line 111, column 35:
PLS-00302: component 'LC3' must be declared
ORA-06550: line 111, column 3:

ORA-06550: line 117, column 35:
PLS-00302: component 'LC3' must be declared
ORA-06550: line 117, column 3:

Thanks in adavnce for the help. Received on Tue May 20 2003 - 01:22:23 CEST