Re: UTL_FILE help appending

Pawan wrote:

> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3ECA62AB.1207BAAD_at_exxesolutions.com>...
>
> > Pull your SQL statements out of your procedure and run them in SQL*Plus to
> > debug.
>
> Thanks Daniel. I have run the SQLs on SQL prompt and even modified my
> code a little. Now I am able to run the code but then only it creates
> one(1) file instead of the 4 (as I am having 3 Cust_corp_id in the
> WHERE clause). This file is named 00000_Q.XLS and contains data fron
> the first cursor (forecast_cur) and nothing from the second
> cursor(lastyrsale_cur). Please help. I am new to Oracle.
> The 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) := '00000_Q.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'),'APR',B.FCST_AMT,null)) c6,
> ---(code snipped here)
> 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','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'),'MAR',d.dsar_frd_amt,null)) lc5,
> SUM(decode(to_char(d.dsar_date,'MON'),'APR',d.dsar_frd_amt,null)) lc6,
> ---(code snipped here)
> 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','002620')
> AND v.cust_corp_id=forecast_rec.c1
> group by v.cust_corp_id, v.CUST_CORP_NM;
> lastyrsale_rec lastyrsale_cur%ROWTYPE;
> BEGIN
> l_last_filename := '9999999_Q.XLS';
> 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);
> l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767);
> IF UTL_FILE.IS_OPEN(l_file) THEN DBMS_OUTPUT.PUT_LINE('File is Opened
> for Write');
> ELSE DBMS_OUTPUT.PUT_LINE('Still Closed');
> END IF;
> FOR i in forecast_cur
> LOOP
> l_cust := i.c2 || '_Q.XLS';
> l_custid := i.c1;
> IF l_cust <> l_last_filename 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)||'|');
> l_custid_prev := i.c1;
> 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 lastyrsale_cur%ROWCOUNT <> 1 AND l_custid <> l_custid_prev THEN
> UTL_FILE.PUT_LINE(l_file, nvl(j.lc3,0) ||'|'|| nvl(j.lc4,0)
> ||'|'||nvl(j.lc5,0)||'|');
> END IF;
> l_custid_prev := i.c1;
> END IF;
> 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;
> EXCEPTION
> WHEN utl_file.invalid_path THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
> WHEN utl_file.invalid_mode THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
> WHEN utl_file.invalid_filehandle THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
> WHEN utl_file.invalid_operation THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
> WHEN utl_file.read_error THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
> WHEN utl_file.write_error THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
> WHEN utl_file.internal_error THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
> WHEN OTHERS THEN
> RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
> END;
> /
> --------------------
> code ends

I responded to you off-line. You can't create four files with one FOPEN.

You need to set up a nested loop situation where the outer loop selects the customers one at a time and the inner loop performs the FOPEN, collects the information, writes it to the file, and then closes it.

--
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)