Re: DBMS_OUTPUT Package

From: Craig R. McClanahan <crm_at_PERPETUA.DAT.COM>
Date: Sun, 19 Mar 1995 15:57:17 -0500
Message-Id: <9503192341.AA09521@alice.jcc.com>

>>>>> "Rick" == Rick Wiggins <jwiggins_at_HRFOCUS.ATT.COM> writes:

    Rick> I am writing some PL/SQL procedures (within a package) that
    Rick> use the DBMS_OUTPUT package.  These procedures use an outer
    Rick> join cursor and some brute force to compare the column
    Rick> values of corresponding rows in identically structured
    Rick> tables.  If any diffrences are found, DBMS_OUTPUT.PUT_LINE
    Rick> is used to note the differences.

    Rick> Occasionally, if there are alot of differences (i.e. alot of     Rick> output), I'll get messages like the following.

    Rick>         ERROR at line 1: ORA-20000: ORU-10027: buffer
    Rick> overflow, limit of 20000 bytes ORA-06512: at
    Rick> "SYS.DBMS_OUTPUT", line 100 ORA-06512: at "SYS.DBMS_OUTPUT",
    Rick> line 59 ORA-06512: at "AODBA.SIDD", line 639 ORA-06512: at
    Rick> line 2

I ran into this kind of problem all the time, until I discovered the SQL*Plus command:

        SET SERVEROUTPUT ON SIZE 1000000 to adjust the size of the output buffer. According to the book, one million is the largest acceptable value.

    Rick> In an attempt to overcome this situation, I've done the
    Rick> following for each output request in an attempt to keep from
    Rick> overflowing the buffer.

    Rick>         DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.ENABLE;

    Rick> DBMS_OUTPUT.PUT_LINE('some output');

    Rick> This doesn't help, is probably inefficient and may be a
    Rick> misuse of the intended purpose of both the DISBLE and ENABLE
    Rick> procedures.  Is the DBMS_OUTPUT package a good alternative
    Rick> for "external" communications (i.e. to a file or a human) if
    Rick> a significant volume of output is possible?  Being a
    Rick> relative PL/SQL newbie, I'd appreciate any advice.

    Rick> +=========================+====================================+
    Rick> | Rick Wiggins | | | Lead Software Developer | Voice:
    Rick> (910)379-5507 | | AT&T HRISO | FAX: (910)379-5981 | | Room
    Rick> 1553 | Internet: jwiggins_at_hrfocus!att!com | | 3330
    Rick> W. Friendly Ave.  | Compu$erve: 71673,1235 | | Greensboro,

    Rick> NC 27410 | |

    Rick> +=========================+====================================+


----------
Craig R. McClanahan                     EMAIL:  crm_at_dat.com
DAT Services                            Phone:  503-643-4331
Beaverton, OR, USA                      Fax:    503-526-6442

Received on Sun Mar 19 1995 - 18:41:46 CST