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

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

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,