Wednesday, October 12, 2005

DBMS_OUTPUT.PUT_LINE

DBMS_OUTPUT.PUT_LINE allows you to write information to a buffer throughout the execution of a trigger/procedure. That information is available to be read by a trigger/procedure (using GET_LINE(S)), or dumped to SQL*Plus upon completion of execution.

One of the most common misconceptions is that PUT_LINE writes data immediately to SQL*Plus. That is not true. PUT_LINE only puts it in the buffer. You will not see it before the block has executed. I can prove that with this example (note: you must load the user_lock package for this):

scott@Robert> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Going to sleep for 10 seconds...');
3 USER_LOCK.SLEEP(1000);
4 DBMS_OUTPUT.PUT_LINE('Woke up after 10 seconds.');
5 END;
6 /
Going to sleep for 10 seconds...
Woke up after 10 seconds.

You will have seen both messages come out after 10 seconds as opposed to one before and one after.

Despite the fact that it doesn't write messages throughout its progress, PUT_LINE can still make a useful debugging tool. I like the way that the messages can be kept but easily disabled by using DBMS_OUTPUT.DISABLE. Any PUT_LINE messages are silently ignored if you have DISABLEd DBMS_OUTPUT (or failed to ENABLE).

To see the messages, you need to call DBMS_OUTPUT.ENABLE. The only parameter is buffer_size, which, if NULL, will default to 20000. The buffer size can be anywhere from 2000 to 1000000.

scott@Robert> BEGIN
2 DBMS_OUTPUT.DISABLE;
3 DBMS_OUTPUT.PUT_LINE('Disabled');
4 DBMS_OUTPUT.ENABLE;
5 DBMS_OUTPUT.PUT_LINE('Enabled');
6 END;
7 /
Enabled

PL/SQL procedure successfully completed.

Incidentally, SQL*Plus's SET SERVEROUTPUT ON will call DBMS_OUTPUT.ENABLE. You can even use SIZE with that command. SET SERVEROUTPUT also includes formatting options, such as FORMAT WRAPPED, WORD_WRAPPED and TRUNCATE (along with a SET LINESIZE) to get the output the way you want it. [EDIT: Fixed Typo]

There are two common errors related to DBMS_OUTPUT.PUT_LINE. The first one is trying to put more than 255 characters per line.

scott@Robert> DECLARE
2 l_string VARCHAR2(300);
3 BEGIN
4 l_string := '1234567890';
5 l_string := l_string || l_string || l_string || l_string || l_string;
6 l_string := l_string || l_string || l_string || l_string || l_string || l_string;
7 DBMS_OUTPUT.PUT_LINE(l_string);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 7

The solution here is to use DBMS_OUTPUT.NEW_LINE to split it up into lines. 255 is a hard limit, if you really want to print a line with more than that, you can write your own package that does the same thing as DBMS_OUTPUT. That is actually a very common thing to do. Tom Kyte's has a handy one in Appendix A of "Expert One-on-One Oracle."

The second common error is overfilling your buffer.

scott@Robert> BEGIN
2 DBMS_OUTPUT.ENABLE(2000);
3 FOR i IN 1..1000 LOOP
4 DBMS_OUTPUT.PUT_LINE('This is line ' || i);
5 END LOOP;
6 END;
7 /
This is line 1

This is line 105
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 4

The solution here is increase the size of your buffer, using ENABLE. The maximum size is 1000000 and that is a hard limit. Once again, you can write your own package as a workaround.

This example also illustrated that even if you have an exception, the contents of the buffer until that point is still available.

The alternative to writing your own package is to write your messages to a table. Then you can query the table at any time to see your debug messages. DBMS_PIPE is another option to consider.

I will close with two more interesting "gotchas" for DBMS_OUTPUT.PUT_LINE.

scott@Robert> BEGIN
2 DBMS_OUTPUT.PUT_LINE(' What happened to my leading spaces?');
3 END;
4 /
What happened to my leading spaces?


This is an SQL*Plus Gotcha. Just be sure to use FORMAT WRAPPED, like so:

scott@Robert> SET SERVEROUTPUT ON FORMAT WRAPPED
scott@Robert> BEGIN
2 DBMS_OUTPUT.PUT_LINE(' There they are!');
3 END;
4 /
There they are!


Here is the second gotcha.

scott@Robert> DECLARE
2 l_bool BOOLEAN;
3 BEGIN
4 l_bool := TRUE;
5 DBMS_OUTPUT.PUT_LINE(l_bool);
6 END;
7 /
DBMS_OUTPUT.PUT_LINE(l_bool);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

DBMS_OUTPUT.PUT_LINE is not overloaded for Booleans. The solution is to either to write your own package (as mentioned above), or convert from Boolean type to something else for the PUT_LINE call.

For more information, this package is described in (among other places) Oracle's Supplied PL/SQL Packages Reference Guide, Chapter 43:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612.pdf

And here is Dan Morgan's Quick Reference:
http://www.psoug.org/reference/dbms_output.html

Comments:
I think those hard limits have gone in 10gR2

http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html

Cheers,

Doug
 
...SQL*Plus's SHOW SERVEROUTPUT ON will call...

I believe you meant SET SERVEROUTPUT ON.

And a follow-up to Doug's comment, here is a quote from the Oracle 10gR2 documentation:

PL/SQL programmers frequently use DBMS_OUTPUT and, in Oracle Database 10g Release 1 and earlier, were constrained by the 255 byte limit. When using SQL*Plus, most programmers are regularly caught by the small default overall limit and sometimes by the current 1,000,000 maximum overall limit. In Release 2, the line length limit is increased to 32,767 bytes and the overall limit is removed altogether.

 
is there any place we can set SET SERVEROUT ON make it permanent
 
Yes, anand. To do this automatically in SQL*Plus, you are thinking of the glogin.sql file, which can be found in your sqlplus/admin directory.

You can learn more about the glogin.sql file by checking the SQL*Plus User's Guide and Reference.

http://oracleheva1.oracle.com/docs/cd/B10501_01/server.920/a90842.pdf
 
How to truncate a variable in Oracle
 
what do you mean by truncating a variable... is it a variable in pl/sql block or somewhat else..
 
How can we see the DBMS_OUTPUT info from a procedure that is called using a DBLINK?
 
This comment has been removed by a blog administrator.
 
hi,

I like your blog. I am also interested in Oracle. I am preparing for 1Z0-007 exam, which is SQL.

Can you please help me with most probable questions?

Thanks
 
You look like Matthew Tarzwell. Are you related
 
use this to break into e.g. 240 char/lines:

ioffset := 1;
iammount := 240;
while ioffset < length(sQueryText) loop
DBMS_OUTPUT.PUT_LINE( substr(sQueryText, ioffset, iammount ) );
ioffset := ioffset + iammount ;
iammount := least(240,length(sQueryText) - iammount);
end loop;
 
Glad they increased the message length and got rid of the buffer limit in 10gR2, but they really should have done this a long long time ago.
 
Thanks for the info.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?