Oracle – Capture PL/SQL Output From DBMS_OUTPUT.PUT_LINE To Table Or File

Debugging PL/SQL Code:

Stepping through code is a basic necessity for programmers to test and verify logic flow in code.

Very few PL/SQL programmers use this capability. Most, if not all, rely on the capabilities of the Oracle supplied package DBMS_OUPUT. Specifically, the PUT_LINE procedure within DBMS_OUTPUT is used as a crutch for debugging needs.

The common alternate approach is to use UTL_FILE.PUT_LINE to write to a file. However, it is more than a hassle to wire up all of your procedures to do so by passing a file handle around and making sure it is properly initialized and closed.  One can imagine the complexity in cases where the code is several levels deep and is dispersed across multiple schemas. More often, people inherit pre-written code that is littered with DBMS_OUTPUT.PUT_LINE which has valuable information that will help aid troubleshooting.

When DBMS_OUTPUT is not ideal

Although, it is fine to use DBMS_OUTPUT in most cases, there are circumstances when it is not. Here are a few examples:

  • The output needs to be captured and saved for the record – Eg: the outputs of releases
  • Copying and pasting the output is not a viable/good solution
  • The run is not initiated in a fashion where the output is visible for someone to look at
  • e.g., SQL*Plus launched in silent mode without any output capture

Capturing DBMS_OUTPUT to file or table

Capturing what was written out to the buffer using DBMS_OUTPUT is not that hard. The same package comes with a less-used cousin named “GET_LINE” that can do the reverse of “PUT_LINE”. i.e., get the contents from the buffer to a variable which can then be output to a physical file or a table.

In the code listing below, the anonymous block writes out DBMS_OUPUT that we then capture after the fact (when the program is about to exit) to a file or a table.

Let us jump right into the code:

DECLARE

    -------------------------------------------------------------------------------------------------------------

    PROCEDURE prc_1
    IS
    BEGIN
        FOR i IN 1..10 LOOP
            DBMS_OUTPUT.PUT_LINE ('Procedure: prc_1: Step - ' || TO_CHAR(i) || CHR(13));
        END LOOP;
    END;

    -------------------------------------------------------------------------------------------------------------

    PROCEDURE prc_2
    IS
    BEGIN
        prc_1;

        FOR i IN 1..10 LOOP
            DBMS_OUTPUT.PUT_LINE ('Procedure: prc_2: Step - ' || TO_CHAR(i) || CHR(13));
        END LOOP;
    END;    

    -------------------------------------------------------------------------------------------------------------

    PROCEDURE prc_Write_Buffer_To_Log_Table(
                        p_Context                 IN VARCHAR2 DEFAULT NULL,
                        p_Truncate_Log_Table      IN VARCHAR2 DEFAULT 'N'
                    )
    IS PRAGMA AUTONOMOUS_TRANSACTION;
        v_Message   VARCHAR2(255);
        v_Status    INTEGER := 0;
    BEGIN

        IF NVL(p_Truncate_Log_Table, 'N') = 'Y' THEN
            EXECUTE IMMEDIATE 'TRUNCATE TABLE LOG_TABLE';
        END IF;

        LOOP
            EXIT WHEN v_Status = 1;

            --Status = 0 when call completes successfully, 1 when buffer is emtpy
            DBMS_OUTPUT.GET_LINE (v_Message, v_Status);    

            IF (v_Status = 0) THEN
                ---------------------------------------------------------------
                -- NO ....NO ....NO....CANNOT DO "DBMS_OUTPUT.PUT_LINE" here....
                ---------------------------------------------------------------
                --Reason: While reading from the buffer, if the buffer is also written to,
                --          lines written before that were not retrieved after the last GET_LINE call will be discarded
                --DBMS_OUTPUT.PUT_LINE ('Retrieved Line: ' || substr(v_Line,1,200));

                --Instead, insert into a table or write to a log file
                INSERT INTO LOG_TABLE (
                       CREATE_DATETIME, MESSAGE, CONTEXT)
                    VALUES ( SYSDATE,
                        v_Message,
                        p_Context);
            END IF;
        END LOOP;

        COMMIT;
    END;

    -------------------------------------------------------------------------------------------------------------

    PROCEDURE prc_Write_Buffer_To_Log_File(
                    p_Dir_Name      IN VARCHAR2,         --This is the Oracle directory object name. Create one if there is none.
                                                            --  CREATE DIRECTORY log_dir AS '/appl/gl/log'; GRANT READ,WRITE ON DIRECTORY log_dir TO some_user;
                    p_File_Name     IN VARCHAR2 DEFAULT 'dbms_ouput'|| '_' || TO_CHAR(SYSDATE, 'YYMMDD_HH24MISS') || '_' || SYS_CONTEXT('USERENV','SID'),
                    p_Write_Mode    IN VARCHAR2 DEFAULT 'a')  --r -- read text, w -- write text, a -- append text, rb -- read byte mode, wb -- write byte mode, ab -- append byte mode
    IS
        v_Message       VARCHAR2(255);
        v_Status        INTEGER := 0;
        v_File_Handle   UTL_FILE.FILE_TYPE;
    BEGIN
        v_File_Handle := UTL_FILE.FOPEN(p_Dir_Name, p_File_Name, p_Write_Mode);    

        LOOP
            EXIT WHEN v_Status = 1;

            --Status = 0 when call completes successfully, 1 when buffer is empty
            DBMS_OUTPUT.GET_LINE (v_Message, v_Status);            

            IF (v_Status = 0) THEN
                ---------------------------------------------------------------
                -- NO ....NO ....NO....CANNOT DO "DBMS_OUTPUT.PUT_LINE" here....
                ---------------------------------------------------------------
                --Reason: While reading from the buffer, if the buffer is also written to,
                --          lines written before that were not retrieved after the last GET_LINE call will be discarded
                --DBMS_OUTPUT.PUT_LINE ('Retrieved Line: ' || substr(v_Line,1,200));

                --Instead, insert into a table or write to a log file
                UTL_FILE.PUT_LINE(v_File_Handle, v_Message);

            END IF;
        END LOOP;        

        --Flush and close the file
        UTL_FILE.FFLUSH(v_File_Handle);
        UTL_FILE.FCLOSE(v_File_Handle);
    END;
    -------------------------------------------------------------------------------------------------------------

BEGIN

    --Call a procedure that writes out using DBMS_OUTPUT.PUT_LINE
    prc_2;

    --
    --Can only do one or the other not both. i.e., capture to file or table because the buffer is cleared once it is read
    --      The two can be chained but the way it is above, both cannot be called
    --
    --Capture output to a log file
    --prc_Write_Buffer_To_Log_Table;
    --COMMIT;

    --Capture output to a log table
    prc_Write_Buffer_To_Log_File(
                p_Dir_Name      => 'LAPSEDCOVG',
                p_File_Name     => 'Janas_Log.log',
                p_Write_Mode    => 'w');

END;

In the example anonymous block above, there are two procedures that write out to DBMS_OUTPUT.  There are two additional support procedures that can then serialize the buffer contents to a file or a table. The code is pretty self-explanatory.

Precautions

Here are some precautions

  • Always reserve enough space in the buffer for dbms_output – The following will reserve 100000 characters

dbms_output.enable(100000);

  • If writing to a file, make sure that the DIRECTORY object exits and that the user writing out has adequate permissions to it.

See code comments related to CREATE DIRECTORY and GRANT

    • When writing to a table, make sure the output table exists. In this case, the table was pre-created using
CREATE TABLE LOG_TABLE
(
  CREATE_DATETIME  DATE DEFAULT SYSDATE NOT NULL,
  MESSAGE          VARCHAR2(255) NOT NULL,
  CONTEXT          VARCHAR2(127)
);

  • PUT_LINE has a limitation of 255 characters max
  • You can retrieve more than one line using the alternate version – DBMS_OUPTPUT.GET_LINES to get multiple lines in one shot
  • Can only do one or the other not both. i.e., capture to file or table because the buffer is cleared once it is read. The two can be chained but the way it is above, both cannot be called.

Suggestions

Now, armed with this information, you can add a call to the prc_Write_Buffer_To_* procedure at the end of your top level procedure to capture the final output to either a file or a table without changing your code or without having to rely on a fancy tool.

Please do not rush to copy this code in to every release/module. It will be prudent to add this to a common package and have the functionality available to everyone.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s