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.
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.
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.
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;
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.
Ahhhhh, much better. :-)
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;
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.
My package specification is, hopefully, self-explanatory:
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 ) /
Let's take a look, finally, at the package body.
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; /
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.
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; /
You can download my Super Simple Trace Utility from LiveSQL. But you'd be much better off with Logger. :-)
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;