Oracle PL/SQL Best Practices

Name

BIP-01: Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.

Synopsis

I am very glad that Oracle provided DBMS_OUTPUT in Version 2 of PL/SQL. Before that, it was difficult to debug code, because there was no easy way to trace program execution to the screen. However, the implementation of DBMS_OUTPUT leaves much to be desired. Here are my complaints:

1. It’s a productivity disaster. You have to type 20 characters just to ask PL/SQL to show you something.

2. The overloading is inadequate. You can pass only single strings, dates, or numbers. You can’t pass it a Boolean value, nor can you pass it multiple values to be displayed (without doing the concatenation yourself).

3. If you try to display a string with more than 255 characters, you get one of two errors: ORA-20000 (a.k.a. ORU-10028 line length overflow) or ORA-06502 (numeric or value error). I don’t know about you, but a whole lot of my strings are longer than 255 bytes.

4. Your program can display a maximum of 1 million lines—and it can be lots less if you forget to specify a high number in your SET SERVEROUTPUT command in SQL*Plus (resulting in an out-of-buffer error).

5. You don’t see anything on your screen until your PL/SQL program has finished executing—whether that takes five minutes or five hours.

When you are faced with a utility such as DBMS_OUTPUT that is simultaneously necessary and faulty, you should say out loud (it will make you feel better):

I am fed up and I am not going to take it anymore!

Specifically, set a rule that ...