Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my Bulletproof PL/SQL presentation, which includes this slide.

That first item in the list caught his attention:

Never put calls to DBMS_OUTPUT.PUT_LINE in your application code.

So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-)

But in the meantime, allow me to explain.

First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen.

[Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.]

Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen?

Not without a lot of difficulty, that's for sure. I will talk below about what you want to use instead of DBMS_OUTPUT.PUT_LINE (instead of writing output to a screen, in other words), but really, I should modify my advice as follows:

There should be at most one call to DBMS_OUTPUT.PUT_LINE in your application code.

Notice my reference to "application code." By this, I mean come you are writing code to be deployed into production, as opposed to demonstration or "let's try this" scripts.

So, now maybe it should appear just once? Steven, explain yourself already.

OK, first my reasons for avoiding widespread use of DBMS_OUTPUT.PUT_LINE, then suggestions on what to do instead.

The downsides of DBMS_OUTPUT.PUT_LINE include....

• You won't see anything unless you have enabled server output (example in SQL*Plus: SET SERVEROUTPUT ON). In some editors, it is enabled by default. In others, you must take action.
• Will only accept a string or a datatype that can be implicitly converted to a string.
• The buffer is volatile: if for any reason your session terminates, that output is gone forever.
• Production environments and screen output do not mix. In other words, you will not be able to see this output in production.
• When a PL/SQL block terminates with an unhandled exception, text in the DBMS_OUTPUT buffer might not be flushed out to the screen (depends on the host environment).
• It is a very crude debugging/tracing mechanism. Writing to the buffer is either enabled or disabled - globally in your application. No nuances beyond that.
• In some IDEs (editors), you have to explicitly enable output (outside your application code) before you will see anything.

I bet readers of this blog post will chime in with other drawbacks to this built-in.

As the slide above implies, my comment about this built-in came in the context of tracing - getting real-time feedback on what your code is doing.

Here's an example of a program that uses DBMS_OUTPUT.PUT_LINE for tracing.

PROCEDURE do_stuff (min_sal_in IN NUMBER)
IS
BEGIN
   DBMS_OUTPUT.put_line (min_sal_in);

   FOR rec IN (SELECT *
                 FROM employees
                WHERE salary >= min_sal_in)
   LOOP
      DBMS_OUTPUT.put_line ('employee_id = ' || rec.employee_id);
      do_more_stuff (rec);
      DBMS_OUTPUT.put_line ('new salary = ' || rec.salary);
   END LOOP;
END;

Suppose I took this approach. I don't always want to see the output; I'm really only interested when debugging and testing the code. No problem, you might say. Just disable server output. Sure - but then you also don't see the output from this program that is not trace-related.

And when the code is deployed to production, the limitations of DBMS_OUTPUT are undeniable: you have no ability to trace whatever your users are doing that might be causing a problem.

Even worse, a very common pattern followed by developers is that they (OK, we - I still do it now and again when I am feeling very lazy) put in calls to DBMS_OUTPUT.PUT_LINE all over their code while they are getting it to work (ie, testing, sort of).

Then when they feel they've got it all fixed, they remove the calls to DBMS_OUTPUT.PUT_LINE. After all, all those output statements are making a mess of the code - and they're not even really a part of the application.

PROCEDURE do_stuff (min_sal_in IN NUMBER)
IS
BEGIN
   FOR rec IN (SELECT *
                 FROM employees
                WHERE salary >= min_sal_in)
   LOOP
      do_more_stuff (rec);
   END LOOP;
END;

Ahhhhh, much better. :-)

On the one hand, you might say: "Well, that's good then, right, Steven? You said not to put it in your code."

On the other hand, I might say: "Maybe - but only if that's because you really have removed all the bugs from your code and none will ever be introduced."

The bottom line is that any high quality piece of code should and will include instrumentation/tracing. Moving code into production without this ability leaves you blind when users have problems. So you definitely want to keep that tracing in, but you definitely do not want to use DBMS_OUTPUT.PUT_LINE - or at least rely only on the built-in to get the job done.

What should you do instead? Build your own wrapper around the built-in or use a an existing trace utility like the open source Logger. And if you can't use Logger for whatever reason, use it as model for your own.

So what do I mean by "build your own wrapper around the built-in"? I mean that the only way to get output to your screen is through this package and sometimes you certainly want that. But most of the time, and especially in production, you'd much rather send that trace information to a table, where the data persists and anyone with the privileges can query from it. And you need to be able to turn tracing on and off as needed, even while the application is running. It would also be nice if you could easily trace a Boolean value.

To give you a sense of what this wrapper might look like, I offer to your the Super Simple Trace Utility. First, I have two tables, one to store the current configuration for the utility, the other to hold the trace information itself.

CREATE TABLE trace_config
(
   send_to    VARCHAR2 (1),
   trace_on   VARCHAR2 (1)
)
/

INSERT INTO trace_config (send_to, trace_on)
     VALUES ('T', 'N')
/

COMMIT
/

CREATE TABLE trace_table
(
   message      VARCHAR2 (4000),
   callstack    CLOB,
   created_by   VARCHAR2 (30),
   created_at   DATE
)
/

My package specification is, hopefully, self-explanatory:

CREATE OR REPLACE PACKAGE trace_mgr
IS
   PROCEDURE to_screen;
   PROCEDURE to_table;

   PROCEDURE turn_on;
   PROCEDURE turn_off;

   FUNCTION sending_to_screen RETURN BOOLEAN;

   FUNCTION trace_is_on RETURN BOOLEAN;

   PROCEDURE put (msg_in IN VARCHAR2);
   PROCEDURE put (boolean_in IN BOOLEAN);
END;
/

Let's take a look, finally, at the package body.

CREATE OR REPLACE PACKAGE BODY trace_mgr
IS
   PROCEDURE to_screen
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      UPDATE trace_config SET send_to = 'S';
      COMMIT;
   END;

   PROCEDURE to_table
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      UPDATE trace_config SET send_to = 'T';
      COMMIT;
   END;

   PROCEDURE turn_on
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      UPDATE trace_config
         SET trace_on = 'Y';

      COMMIT;
   END;

   PROCEDURE turn_off
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      UPDATE trace_config SET trace_on = 'N';
      COMMIT;
   END;

   FUNCTION trace_is_on
      RETURN BOOLEAN
   IS
      l_trace   trace_config.trace_on%TYPE;
   BEGIN
      SELECT trace_on INTO l_trace FROM trace_config;
      RETURN l_trace = 'Y';
   END;

   FUNCTION sending_to_screen
      RETURN BOOLEAN
   IS
      l_send_to   trace_config.send_to%TYPE;
   BEGIN
      SELECT send_to INTO l_send_to FROM trace_config;
      RETURN l_send_to = 'S';
   END;

   PROCEDURE put (msg_in IN VARCHAR2)
   IS
      FUNCTION fullmsg
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN    ' At: '
                || TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS')
                || ' Msg: '
                || msg_in;
      END;
   BEGIN
      IF trace_is_on
      THEN
         IF sending_to_screen
         THEN
            DBMS_OUTPUT.put_line (fullmsg);
         ELSE
            INSERT INTO trace_table (MESSAGE,
                                     callstack,
                                     created_by,
                                     created_at)
                 VALUES (msg_in,
                         DBMS_UTILITY.format_call_stack,
                         USER,
                         SYSDATE);
         END IF;
      END IF;
   END;

   PROCEDURE put (boolean_in IN BOOLEAN)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
         CASE boolean_in
            WHEN TRUE THEN 'TRUE'
            WHEN FALSE THEN 'FALSE'
            ELSE 'NULL'
         END);
   END;
END;
/

Now my do_stuff procedure can be converted easily over to the following, and I control the behavior of the trace package from outside of this procedure.

PROCEDURE do_stuff (min_sal_in IN NUMBER)
IS
BEGIN
   trace_mgr.put (min_sal_in);

   FOR rec IN (SELECT *
                 FROM employees
                WHERE salary>= min_sal_in)
   LOOP
      trace_mgr.put ('employee_id = ' || rec.employee_id);
      do_more_stuff (rec);
      trace_mgr.put ('new salary = ' || rec.salary);
   END LOOP;
END;

You can download my Super Simple Trace Utility from LiveSQL. But you'd be much better off with Logger. :-)