output in PL/SQL block ...

From: Edwin Tielen <edwint_at_sgi.com>
Date: Tue, 10 Apr 2001 16:14:54 +0200
Message-ID: <9av4hj$h1h$1_at_hercules.neu.sgi.com>

Hi,

[Quoted] I am a newby (PL/)SQL user and have installed TOAD as a development environment. I have created a program with a few cursors and some condition [Quoted] statements that should drive what data (fields) I want to export. Sofar I've [Quoted] been using dbms_output.put_line() function, but there is a limit to the buffer size and even increasing it's size will still halt my program with an [Quoted] overflow error. Dbms_output seems more suited for debugging purposes, but I've not been able to find any other output statement. Can anyone refer me in a direction? For clarity, I've included my PL/SQL code below.

Please reply also through email, since I am not a daily visitor of this group.

Thx for your brainpower.

-Edwin

declare

   cursor contract_cursor is

      select id,
      dflt_end_dt,

    objid
   from table_contract
   where dflt_start_dt <= to_date('06/30/2001', 'MM/DD/YYYY')

      and dflt_end_dt > to_date('04/01/2001', 'MM/DD/YYYY');

   cursor schedule_cursor(p_objid number) is

      select schedule_id,
    x_billing_app,
    fsvc_start_dt,
    fsvc_end_dt,
    objid
   from table_contr_schedule
   where schedule2contract = p_objid

      and upper(x_billing_app) like '%CZECH%';

   cursor per_amt_cursor(p_objid2 number) is

      select line_no,
      status,

    currency,
    gross_prd_amt,
    net_prc,
    pro_prc,
    bill_prd_amt,
    extract_trk_id,
    prd_start_dt,
    prd_end_dt,
    objid
      from table_period_amt
   where period2contr_schedule = p_objid2

      and prd_start_dt >= to_date('04/01/2001', 'MM/DD/YYYY')     and prd_start_dt < to_date('07/01/2001', 'MM/DD/YYYY')     and line_no = 0
    and status = 0;

   v_sys_date varchar2(30);
   v_per_amt_record per_amt_cursor%ROWTYPE;

begin

   select to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')    into v_sys_date
   from dual;

   dbms_output.put_line('Contract ID'                 ||
                        chr(9)                        ||
                  'Schedule ID'                 ||
                        chr(9)                        ||
                  'First Svc Start Date'        ||
                        chr(9)                        ||
                  'First Svc End Date'          ||
                        chr(9)                        ||


         'Period Start Date'           ||


                        chr(9)                        ||


         'Period End Date'             ||


                        chr(9)                        ||


         'Net Billed Amount'           ||


                        chr(9)                        ||


         'Status');

   dbms_output.put_line('Start Process at: ' || v_sys_date);

   for contr_record in contract_cursor loop

      for sched_record in schedule_cursor(contr_record.objid) loop

      open per_amt_cursor(sched_record.objid);

   loop

      fetch per_amt_cursor into v_per_amt_record;

   if per_amt_cursor%NOTFOUND then

            dbms_output.put_line(contr_record.id               ||
                           chr(9)                        ||
                     sched_record.schedule_id      ||
                           chr(9)                        ||
         sched_record.fsvc_start_dt    ||
                           chr(9)                        ||
          sched_record.fsvc_end_dt      ||
         chr(9)                        ||
            'No Billing records found.');

   else

         dbms_output.put_line(contr_record.id               ||
                           chr(9)                        ||
                     sched_record.schedule_id      ||
                           chr(9)                        ||
         sched_record.fsvc_start_dt    ||
                           chr(9)                        ||
          sched_record.fsvc_end_dt      ||
         chr(9)                        ||
            v_per_amt_record.prd_start_dt ||
                           chr(9)                        ||
            v_per_amt_record.prd_end_dt   ||
                           chr(9)                        ||
            v_per_amt_record.bill_prd_amt ||
                           chr(9)                        ||
            v_per_amt_record.status);
      end if;

   exit when per_amt_cursor%NOTFOUND;

   end loop;

   close per_amt_cursor;

      end loop;

   end loop;

   select to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')    into v_sys_date
   from dual;

   dbms_output.put_line('End Process at ' || v_sys_date);

end; Received on Tue Apr 10 2001 - 16:14:54 CEST