enhancements to dbms_output in 10g release 2

Oracle Database Developer Choice Awards

I'm pleased to announce that I've been selected as a finalist in the inaugural Oracle Database Developer Choice Awards. To quote, these awards "celebrate and recognize technical expertise and contributions in the Oracle Database community".

I've been selected for the PL/SQL category, primarily for the articles and utilities that I share here on oracle-developer.net. I'm very pleased to be recognised by the Oracle Community in this way and if you have found oracle-developer.net to be a helpful and/or interesting resource, then I'd appreciate your vote (my voting page is here).

More information on the Awards, including an explanatory video, the rules, other categories and finalists can all be found on the main page.

Thanks for your support!

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
  2     DBMS_OUTPUT.PUT_LINE( RPAD('x',256,'x') );
  3  END;
  4  /

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.

  2     DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') || 'even more chars...' );
  3  END;
  4  /
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

SQL> set serveroutput on
SQL> show serveroutput

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
  2     FOR i IN 1 .. ((1000000/32767)+1) LOOP
  3        DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') );
  4     END LOOP;
  5  END;
  6  /
[many equivalent lines removed]

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!

further reading

Further information on the new DBMS_OUTPUT package can be found in the Supplied Packages and Types Reference.

source code

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

Adrian Billington, August 2005

Back to Top