Systems Engineering and RDBMS

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.

DECLARE
v_Str1 VARCHAR2(300);
BEGIN
v_Str1 := lpad(‘ ‘,300,’A’);
DBMS_OUTPUT.PUT_LINE(v_Str1);
END;
/

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
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

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.

One Response to “DBMS_OUTPUT enhancement in Oracle 10g”

  1. […] then you have to make sure that you are not constrained by serveroutput limitation. In 10g, Oracle lifted the earlier limitation of 255 bytes in ‘set serveroutput on’ […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: