enhancements to dbms_output in 10g release 2

The well-known limitations of the DBMS_OUTPUT package have frustrated developers for years. In 10g Release 2 (10.2), Oracle has finally improved the package and we are now able to throw away our home-grown wrapper utilities. This short article introduces the new DBMS_OUTPUT.

line length

In versions prior to 10.2, the PUT and PUT_LINE procedures have an imposed limit of 255 bytes. This means that to display anything longer than this requires a "string chopping" utility (almost every Oracle developer must have written or borrowed one of these at some time). This is less than ideal, especially when using this package for screen-based debugging. In 10.2, this restriction has been relaxed to the more sensible 32,767 bytes (the same upper line-length as the equivalently-named UTL_FILE procedures).

Rather than fill this article with 32,767 bytes of dummy data, we'll simply add one more than the previous limits allowed to show that it works.

SQL> set lines 64
 
SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE( RPAD('x',256,'x') );
  3  END;
  4  /

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

PL/SQL procedure successfully completed.

To demonstrate the new linesize limit, we can try to output several bytes more than is possible. The error message we receive is the same as in previous versions except the reported limit has changed.

SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') || 'even more chars...' );
  3  END;
  4  /

BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767
bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 2

buffer limits

In previous versions of Oracle, we can specify the size of the output buffer either using the sqlplus setting set serveroutput on or the DBMS_OUTPUT.ENABLE procedure. The explicit size range for the buffer is the same in 10.2 (as seen in error messages below), but there is a new "UNLIMITED" alternative to the upper bound. In the following examples, it is interesting to note Oracle's interpretation of the high number we attempt to set, which gives us an indication of the real limits it is working to.

SQL> set serveroutput on size 1000

SP2-0547: size option 1000 out of range (2000 through 1000000)

SQL> set serveroutput on size 1000000000000

SP2-0547: size option 3567587328 out of range (2000 through 1000000)

The unconstrained set serveroutput on setting is now equivalent to set serveroutput on size unlimited. Both of these sqlplus commands execute "DBMS_OUTPUT.ENABLE(NULL)" which can be seen in the trace file if running with SQL trace on.

SQL> set serveroutput on size unlimited 

SQL> show serveroutput

serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

SQL> set serveroutput on
 
SQL> show serveroutput

serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

To show that the unlimited option works, we can attempt to output more than the limit for specified buffer size (1000000 bytes). The following example will output 1048544 bytes.

SQL> set lines 100
 
SQL> BEGIN
  2     FOR i IN 1 .. ((1000000/32767)+1) LOOP
  3        DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') );
  4     END LOOP;
  5  END;
  6  /

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
[many equivalent lines removed]
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

PL/SQL procedure successfully completed.

a cautionary note

Finally, it is worth noting that the unlimited option should be used with caution. To test this feature, I ran an infinite loop of 32K strings and after a couple of minutes encountered (among others) ORA-04030: out of process memory when trying to allocate 4120 bytes. This is to be expected as DBMS_OUTPUT continues to buffer its output until completion of the executing program unit. The various O/S errors appearing in my alert log, the complete performance seizure of my 2 Xeon CPU, 2Gb RAM PC and the appearance of TNS protocol adapter errors were slightly more startling!

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, August 2005

oracle-developer.net