Skip to main content

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. :-)

Comments

  1. Thanks for the clear explanation, great example !
    My own result will be in between the example above and the Logger tool.

    👍👍👍

    ReplyDelete
  2. Hello Steven,
    thanks for the article! Just to point out a little cosmetic mistakes in the package body, the to_screen and to_table procedures should modify the send_to column, not the trace_on.

    ReplyDelete
    Replies
    1. Many thanks, Jozef. Good catch. Darn you, copy and paste! (that's right, it wasn;t MY fault :-) ). I have fixed those errors.

      Delete
  3. I always use util_file. it is much better

    ReplyDelete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p