Re: UTL_FILE help appending

Pawan wrote:

> Thanks Daniel and Rauf. With your help I am very close to getting the
> results that I want. I just need some help in "referencing a cursor"
> (if it is the right term).
> What is happening now is that in the CURSOR cust_cur I am getting the
> distinct cust_corp_nm (in the example I am selecting 3) and I want to
> reference the same cust_corp_nm in the WHERE clause of the other 2
> (forecast_cur and lastyrsale_cur ) cursor so that I get the results
> for the same customer.
> Where is the right place to do it - i.e. select the value of the
> cust_corp_nm into a variable and then use it in the other two cursors.
> The modified code is as under....
> Really appreciate your help.
>
> Pawan
> 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(35) := '00000_Q.XLS';
> l_custid varchar2(6) :=' ';
> l_custnm varchar2(35) := ' ';
> l_custcorpnm varchar2(35) := ' ';
> 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 cust_cur is
> select
> distinct cust_corp_nm
> from CSO.CUST_CORP_PPL_VW
> where cust_corp_id in ('040116','007300','002620');
> cust_rec cust_cur%ROWTYPE;
> 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,
> 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_nm= <NEED to REFERENCE THE CUST from CUST_CUR>
> and v.cust_corp_id in ( '040116','007300','002620')
> group by v.cust_corp_id, v.CUST_CORP_NM;
> forecast_rec forecast_cur%ROWTYPE;
> CURSOR lastyrsale_cur is
> select
> 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,
> 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_nm= <NEED to REFERENCE THE CUST from CUST_CUR>
> group by v.cust_corp_id, v.CUST_CORP_NM;
> lastyrsale_rec lastyrsale_cur%ROWTYPE;
> BEGIN
> OPEN cust_cur;
> LOOP
> FETCH cust_cur into l_cust;
> EXIT when cust_cur%NOTFOUND;
> l_cust:=l_cust||' 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;
> l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767);
> DBMS_OUTPUT.PUT_LINE('Start of FOR loop '||l_cust);
> 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;
> l_last_filename := '9999999_Q.XLS';
> FOR i in forecast_cur
> LOOP
> 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;
>
> FOR j in lastyrsale_cur
> LOOP
> UTL_FILE.PUT_LINE(l_file, nvl(j.lc3,0) ||'|'|| nvl(j.lc4,0)
> ||'|'||nvl(j.lc5,0)||'|');
> 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 LOOP;
> 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;
> /
> -----------------
> The code ends
> rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in message news:<92eeeff0.0305271421.3d6e6667_at_posting.google.com>...
> > pawanputrahanuman_at_yahoo.com (Pawan) wrote in message news:<84857442.0305271002.19d1aaee_at_posting.google.com>...
> >
> >
> > I posted this utl_file example in another thread where user wanted to
> > manage utl_file in a loop where only 50000 rows are written to each
> > file. Maybe it can help you.
> >
> > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=92eeeff0.0301131613.6c284a00%40posting.google.com&rnum=2
> >
> > Regards
> > /Rauf Sarwar

CURSOR parent_cur IS

   SELECT ...
   FROM ...; parent_rec parent_cur%ROWTYPE;

CURSOR child_cur is

   SELECT ...
   FROM ...
   WHERE column_name = parent_rec.column_name; -- this is what makes the nested cursor dependent

child_rec child_cur%ROWTYPE;

OPEN parent_cur
LOOP
   FETCH
   EXIT WHEN    FOPEN
   OPEN child_cur
   LOOP

      FETCH
      EXIT WHEN

      -- utl_file stuff here

   END LOOP;
   FCLOSE
   CLOSE child_cur;

END LOOP;
COMMIT;
CLOSE parent_cur;

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