Try to use before
DBMS_OUTPUT.ENABLE(1000000);
Juan Carlos <jc_va_at_hotmail.com> wrote in message
news:7mdu23$m7a$1_at_bgtnsc02.worldnet.att.net...
> I don't have a lot of experience with PL.SQL, but mananged to get the
> following to compile to do a delete from a table containing hundreds of
> thousands of records. The commit level is set artificially low here for
> testing. After running for a bit, it quits with an error shown after to
> code. Does it mean anything to anyone? I see in the doc that
DBMS_OUTUPUT
> writes to a buffer in the SGA. Do I need to purge this buffer somehow?
>
> SET SERVEROUTPUT ON
>
> DECLARE
> CURSOR PS_CURSOR IS
> SELECT
> ROWID
> FROM MY_TABLE
> WHERE SCHED = '5OE851';
> PS_REC PS_CURSOR%ROWTYPE;
> COMMIT_INTERVAL CONSTANT INTEGER := 10;
> RECORDS_PROCESSED INTEGER := 0;
> TOT_RECS INTEGER := 0;
> BEGIN
> OPEN PS_CURSOR;
> LOOP
> FETCH PS_CURSOR INTO PS_REC;
> EXIT WHEN PS_CURSOR%NOTFOUND;
> DELETE FROM MY_TABLE
> WHERE ROWID = PS_REC.ROWID;
> RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
> TOT_RECS := TOT_RECS + 1;
> IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
> COMMIT;
> DBMS_OUTPUT.put_line('Committed: ' || RECORDS_PROCESSED || '
Total:
> ' || TOT_RECS);
> RECORDS_PROCESSED := 0;
> END IF;
> END LOOP;
> COMMIT;
> DBMS_OUTPUT.put_line('Total: ' || TOT_RECS);
> CLOSE PS_CURSOR;
> END;
> /
>
> error message follows -------->
>
>
> Committed: 10 Total: 670
> Committed: 10 Total: 680
> Committed: 10 Total: 690
> Committed: 10 Total: 700
> Committed: 10 Total: 710
> Committed: 10 Total: 720
> DECLARE
> *
> ERROR at line 1:
> ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
> ORA-06512: at "SYS.DBMS_OUTPUT", line 106
> ORA-06512: at "SYS.DBMS_OUTPUT", line 65
> ORA-06512: at line 28
>
>
> SQL>
>
>
>
Received on Mon Jul 12 1999 - 19:09:07 CDT
