Systems Engineering and RDBMS

Archive for September 18th, 2007

DBMS_OUTPUT enhancement in Oracle 10g

Posted by decipherinfosys on September 18, 2007

DBMS_OUTPUT is a widely used package in PL/SQL development.  At times, it is used for effective error logging and displaying the error messages on the screen and debugging as well in the absence of GUI development tools.

Prior to 10g R2, two procedures PUT and PUT_LINE were restricted by length of 255 bytes. Similarly buffer limit was also topped at 1,000,000 bytes. If line limit or buffer limit exceeds the pre-defined limit, Oracle used to throw an error.   The only workaround when dealing with strings more than 255 bytes, was to substring it and then display part of the string individually. Starting with 10g R2, Oracle lifted these restrictions. Line limit is extended to 32767 bytes from 255 bytes. Also, by default, buffer limit is set to unlimited. When ‘set serveroutput on’ statement is issued without any size specification or with size specification of unlimited, there is no upper limit.

Connect to SQL*Plus with proper credentials and run the following query.

v_Str1 VARCHAR2(300);
v_Str1 := lpad(‘ ‘,300,’A’);

Query will display 300 bytes without any error. Make sure that ‘SET SERVEROUTPUT ON’ command is issued prior to running the PL/SQL block. You can also check the current SERVEROUTPUT option in effect by issuing ‘SHOW SERVEROUTPUT’ command. Following is the result and command.

SQL> show serveroutput

Even though it looks very trivial, it helps during development stages as we don’t have to perform string manipulations in order to display it correctly.

Posted in Oracle | 1 Comment »