Re: output in PL/SQL block ...

"Edwin Tielen" <edwint_at_sgi.com> wrote in message news:9av4hj$h1h$1_at_hercules.neu.sgi.com...
> Hi,
>
> 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
> statements that should drive what data (fields) I want to export. Sofar
 I've
> 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
> 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;
>
>

The usual (and only) solution would be to use the utl_file package. It has the same functionality and somewhat less limitations. You need however to set an init.ora parameter called utl_file_dir for this work. The directory is a directory *on the server* Alternatively it would be of course possible to write diagnostics messages to a table, but usually that's not the best solution as this is going to create extra db maintenance.

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Apr 15 2001 - 14:58:59 CEST