From owner-oracle-l@CCVM.SUNYSB.EDU Thu Mar 16 17:49:19 1995 Received: from ccvm.sunysb.edu by alice.jcc.com; (5.65/1.1.8.2/01Aug94-0142PM) id AA31023; Thu, 16 Mar 1995 17:49:15 -0500 Message-Id: <9503162249.AA31023@alice.jcc.com> Received: from CCVM.SUNYSB.EDU by CCVM.sunysb.edu (IBM VM SMTP V2R2) with BSMTP id 2853; Thu, 16 Mar 95 17:30:59 EST Received: from CCVM.SUNYSB.EDU (NJE origin LISTSERV@SBCCVM) by CCVM.SUNYSB.EDU (LMail V1.2a/1.8a) with BSMTP id 3415; Thu, 16 Mar 1995 12:34:28 -0500 Date: Thu, 16 Mar 1995 12:27:52 -0500 Reply-To: "ORACLE database mailing list." Sender: "ORACLE database mailing list." From: Rick Wiggins Subject: DBMS_OUTPUT Package X-To: oracle-l@ccvm.sunysb.edu To: Multiple recipients of list ORACLE-L I am writing some PL/SQL procedures (within a package) that use the DBMS_OUTPUT package. These procedures use an outer join cursor and some brute force to compare the column values of corresponding rows in identically structured tables. If any diffrences are found, DBMS_OUTPUT.PUT_LINE is used to note the differences. Occasionally, if there are alot of differences (i.e. alot of output), I'll get messages like the following. ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 100 ORA-06512: at "SYS.DBMS_OUTPUT", line 59 ORA-06512: at "AODBA.SIDD", line 639 ORA-06512: at line 2 In an attempt to overcome this situation, I've done the following for each output request in an attempt to keep from overflowing the buffer. DBMS_OUTPUT.DISABLE; DBMS_OUTPUT.ENABLE; DBMS_OUTPUT.PUT_LINE('some output'); This doesn't help, is probably inefficient and may be a misuse of the intended purpose of both the DISBLE and ENABLE procedures. Is the DBMS_OUTPUT package a good alternative for "external" communications (i.e. to a file or a human) if a significant volume of output is possible? Being a relative PL/SQL newbie, I'd appreciate any advice. +=========================+====================================+ | Rick Wiggins | | | Lead Software Developer | Voice: (910)379-5507 | | AT&T HRISO | FAX: (910)379-5981 | | Room 1553 | Internet: jwiggins@hrfocus!att!com | | 3330 W. Friendly Ave. | Compu$erve: 71673,1235 | | Greensboro, NC 27410 | | +=========================+====================================+