The Oracle (tm) Users' Co-Operative FAQ

Using dbms_output as a debugging tool, sometimes my output seems to get lost. Why does this happen ?


Author's name: Norman Dunbar

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 27/02/2003

Oracle version(s): 7.3.4 to 9.2.0

Why do I lose output from DBMS_OUTPUT debugging lines in my code and scripts ?

Back to index of questions


The three main drawbacks of using DBMS_OUTPUT to log progress or debugging messages are :

The following procedure logs details to DBMS_OUTPUT :

    CREATE OR REPLACE PROCEDURE debug_out(iText IN VARCHAR2)
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(iText);
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    /

This procedure at first glance looks useful, however, it has a flaw in that the exception handler simply covers up any errors that occur. This means that some of your debugging data can be lost if :

So if you are losing text from the DBMS_OUTPUT buffer, the reason is because you are ignoring any exceptions generated in DBMS_OUTPUT and you are hitting one or both the limits mentioned above, as the following demonstrates :

    SQL> SET SERVEROUT ON SIZE 1000000
    SQL> DECLARE
      2     TestData VARCHAR2(256)  := '*';
      3  BEGIN
      4      TestData := RPAD(TestData, 256, '*');
      5      debug_out(TestData);
      6* END;
    
    PL/SQL procedure successfully completed.

As you can see, the procedure 'worked' even though the line length is more than 255 bytes, but nothing has been written to the buffer. By commenting out the exception handler and its one line of code, the following results are obtained :

    ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
    ORA-06512: at "SYS.DBMS_OUTPUT", line 84
    ORA-06512: at "SYS.DBMS_OUTPUT", line 58
    ORA-06512: at "CMDB.DEBUG_OUT", line 4
    ORA-06512: at line 5

The following will also run quite happily until the buffer fills up. Then all new calls to write to the buffer will fail, but the error will go un-noticed because of the exception handler. You will get to see the first one million bytes written to the buffer, but nothing after that will show. (By disabling and re-enabling DBMS_OUTPUT, I'm flushing the buffer out.)

    SQL> execute dbms_output.disable;
    PL/SQL procedure successfully completed.
	
    SQL> execute dbms_output.enable(1000000);
    PL/SQL procedure successfully completed.

    SQL> DECLARE
      2     Filler VARCHAR2(250) := '*';
      3  BEGIN
      4     Filler := RPAD(Filler, 250, '*!*!');
      5     FOR x IN 1..4000
      6     LOOP
      7        Debug_Out(Filler);
      8     END LOOP;
      9     Debug_Out('BANG!');
     10* END;

    <Lots of dbms_output buffer contents removed here!>
	
    PL/SQL procedure successfully completed. 

Again, by removing the exception handler and its code then running the above again, I get an error after the one million bytes are displayed :

    ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
    ORA-06512: at "SYS.DBMS_OUTPUT", line 91
    ORA-06512: at "SYS.DBMS_OUTPUT", line 58
    ORA-06512: at "CMDB.DEBUG_OUT", line 4
    ORA-06512: at line 7

What these two simple tests show is that if data is going missing from your DBMS_OUTPUT and you are using direct calls to DBMS_OUTPUT.PUT_LINE in your procedures, then you must be ignoring errors somewhere and this could be indicative of more than just the missing debug messages problem. Best check through all your exception handling code and remove any calls to 'when others then null;' unless you are absolutely sure that it is appropriate.

There is another function call in the DBMS_OUTPUT package called GET_LINE (or GET_LINES to get more than one) which removes data from the buffer. Unfortunately, removing data from the buffer does not free up the space to allow more to be added, the one million byte limit still applies.

DBMS_OUTPUT is therefore only useful for very small amounts of debugging information, or script/procedure output. If you need more, you are advised to use UTL_FILE which has no output size limit and since 8i, is able to have user defined line lengths. There are a couple of references in the 'Further Reading' section below to previous FAQ answers on this very subject.


Further reading:

Oracle PL/SQL built in packages manual.

Why am I getting errors when I try to use the utl_file package to write a flat file ? an article describing how to best set up UTL_FILE for use.

How can I use UTL_FILE as a debugging aid ? an article which deals with using UTL_FILE as a debugging aid rather than using DBMS_OUTPUT.


Back to top

Back to index of questions