Quickly & Easily Output Debugging Data in IFS

Get debugging information from your custom events with one simple package.

This is one of the first packages I deploy whenever starting a contract with a new client. It's extremely simple and provides an easy way to get debugging information into a SQL output window or in a background job. Explanations for procedures can be found below, followed by the entire package code.

SQL Output Window

When most developers need to quickly display information relating to some code, DBMS_OUTPUT.Put_Line is the usual choice and it works great. One downside that I've come across is if you want to include variables or fields in your output, the DBMS_OUTPUT.Put_Line method becomes a jumbled mess of concatenations, making it slightly more difficult to quickly change some text or a variable or a field that's included in that long string.

The solution to this was to create a small procedure, Local_Output. This procedure works very similarly to IFS' built-in error procedures, which replace :P1, :P2, :P3 and :P4 in a string with input parameters. Another parameter of the procedure, put_or_line_, determines whether DBMS_OUTPUT.Put_Line or DBMS_OUTPUT.Put should be used.

I primarily use these outputs when developing and rarely have the need to use them in a production environment so to save unnecessarily writing data to the buffer or deleting any references to DBMS_OUTPUT when deploying to production, a function checks which database the code is running in and will only output data if it's not in production. Make sure you edit the database name in the code below to your production database name.

    /*---------------------------------------------------------------------------
    Method Name:   Is_NonLive_Environment
    Description:   Checks if environment is LIVE
    ---------------------------------------------------------------------------*/
    FUNCTION Is_NonLive_Environment RETURN BOOLEAN IS
    CURSOR get_database_ IS
        SELECT   UPPER(NVL(D.DB_UNIQUE_NAME, D.NAME)) AS DB
        FROM     SYS.V_$DATABASE D;
    db_  VARCHAR2(30);
    
    BEGIN
    OPEN get_database_;
    FETCH get_database_ INTO db_;
    CLOSE get_database_;
    IF db_ = 'IFSLIVE' THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
    END Is_NonLive_Environment;

Some outputs need to stand out more than others. Adding a couple more DBMS_OUTPUT.Put_Line calls with dashes as borders works well but wouldn't it be nice to have everything in one call and the right number of dashes added automatically? Local_Output_Header has the same :P1, :P2, :P3 and :P4 parameters as Local_Output but programatically adds nice borders.

Here's an example of these procedures in action:

BEGIN
    IFSAPP.SYCO_OUTPUT_API.Local_Output_Header('Customer Order (:P1) Lines Loop', 'ABC123');
    IFSAPP.SYCO_OUTPUT_API.Local_Output('Error on line :P1, rel :P2 - :P3', 'L', '10', '1', 'Quantity is zero.');
    IFSAPP.SYCO_OUTPUT_API.Local_Output('Error on line :P1, rel :P2 - :P3', 'L', '20', '1', 'Wanted delivery date is in the past.');
END;
----------------------------------------
    CUSTOMER ORDER (ABC123) LINES LOOP
----------------------------------------
Error on line 10, rel 1 - Quantity is zero.
Error on line 20, rel 1 - Wanted delivery date is in the past.

Background Job Outputs

A nice feature of posting deferred jobs is the ability to add messages to the Background Job window as the procedure is executing. As we want to add outputs as our code is executing but don't want to commit everything we've done, we need to use the AUTONOMOUS_TRANSACTION pragma. The Background_Output procedure performs the same :P1, :P2, :P3 and :P4 replacements as the local output procedures and sends the result to IFSAPP.TRANSACTION_SYS.Set_Status_Info, which uses the sys context to post the message to the correct background job.

By default, the messages are displayed as INFO messages but this can be changed by entering WARNING in the type_ parameter.

To quickly add spacing between background job messages, the Background_Output_Spacer procedure can be used, which simply adds a background job output filled with dashes.

Source Code

Package Specification

CREATE OR REPLACE PACKAGE SYCO_OUTPUT_API IS

    module_  CONSTANT VARCHAR2(25) := 'FNDBAS';
    lu_name_ CONSTANT VARCHAR(25) := 'SycoOutputApi';

    -----------------------------------------------------------------------------
    -------------------- LU SPECIFIC PUBLIC METHODS -----------------------------
    -----------------------------------------------------------------------------
    /*---------------------------------------------------------------------------
    Local Outputs
    ---------------------------------------------------------------------------*/
    PROCEDURE Local_Output(text_        IN VARCHAR2,
                            put_or_line_ IN VARCHAR2 DEFAULT 'LINE',
                            p1_          IN VARCHAR2 DEFAULT NULL,
                            p2_          IN VARCHAR2 DEFAULT NULL,
                            p3_          IN VARCHAR2 DEFAULT NULL,
                            p4_          IN VARCHAR2 DEFAULT NULL);
                            
    PROCEDURE Local_Output_Header(text_        IN VARCHAR2,
                                    p1_          IN VARCHAR2 DEFAULT NULL,
                                    p2_          IN VARCHAR2 DEFAULT NULL,
                                    p3_          IN VARCHAR2 DEFAULT NULL,
                                    p4_          IN VARCHAR2 DEFAULT NULL);
                                    
    /*---------------------------------------------------------------------------
    Background Outputs
    ---------------------------------------------------------------------------*/
    PROCEDURE Background_Output (text_   IN VARCHAR2,
                                type_   IN VARCHAR2 DEFAULT 'INFO',
                                p1_     IN VARCHAR2 DEFAULT NULL,
                                p2_     IN VARCHAR2 DEFAULT NULL,
                                p3_     IN VARCHAR2 DEFAULT NULL,
                                p4_     IN VARCHAR2 DEFAULT NULL);
                                
    PROCEDURE Background_Output_Spacer;
    
    -----------------------------------------------------------------------------
    -------------------- FOUNDATION1 METHODS ------------------------------------
    -----------------------------------------------------------------------------
    PROCEDURE Init;

END SYCO_OUTPUT_API;

Package Body

CREATE OR REPLACE PACKAGE BODY SYCO_OUTPUT_API IS
    -----------------------------------------------------------------------------
    ------------------ LU SPECIFIC PRIVATE METHOD DECLARATIONS ------------------
    -----------------------------------------------------------------------------
    FUNCTION Is_NonLive_Environment RETURN BOOLEAN;
    
    -----------------------------------------------------------------------------
    ----------------------- LU SPECIFIC PRIVATE METHODS -------------------------
    ----------------------------------------------------------------------------- 
    /*---------------------------------------------------------------------------
    Method Name:   Is_NonLive_Environment
    Description:   Checks if environment is LIVE
    ---------------------------------------------------------------------------*/
    FUNCTION Is_NonLive_Environment RETURN BOOLEAN IS
    CURSOR get_database_ IS
        SELECT   UPPER(NVL(D.DB_UNIQUE_NAME, D.NAME)) AS DB
        FROM     SYS.V_$DATABASE D;
    db_  VARCHAR2(30);
    
    BEGIN
    OPEN get_database_;
    FETCH get_database_ INTO db_;
    CLOSE get_database_;
    IF db_ = 'IFSLIVE' THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
    END Is_NonLive_Environment;
    
    -----------------------------------------------------------------------------
    -------------------- LU SPECIFIC PUBLIC METHODS -----------------------------
    -----------------------------------------------------------------------------
    /*---------------------------------------------------------------------------
    Method Name:   Local_Output
    Description:   Creates a DBMS_OUTPUT 
    ---------------------------------------------------------------------------*/
    PROCEDURE Local_Output(text_        IN VARCHAR2,
                            put_or_line_ IN VARCHAR2 DEFAULT 'LINE',
                            p1_          IN VARCHAR2 DEFAULT NULL,
                            p2_          IN VARCHAR2 DEFAULT NULL,
                            p3_          IN VARCHAR2 DEFAULT NULL,
                            p4_          IN VARCHAR2 DEFAULT NULL) IS
    temp_ VARCHAR2(32000);
    BEGIN
    IF Is_NonLive_Environment THEN
        temp_ := text_;
        temp_ := replace(temp_, ':P1', p1_);
        temp_ := replace(temp_, ':P2', p2_);
        temp_ := replace(temp_, ':P3', p3_);
        temp_ := replace(temp_, ':P4', p4_);
        IF UPPER(SUBSTR(put_or_line_,1,1)) = 'L' THEN
        DBMS_OUTPUT.PUT_LINE(temp_);
        ELSIF UPPER(SUBSTR(put_or_line_,1,1)) = 'P' THEN
        DBMS_OUTPUT.PUT(temp_);
        ELSE
        RETURN;
        END IF;
    END IF;
    END Local_Output;
    
    /*---------------------------------------------------------------------------
    Method Name:   Local_Output
    Description:   Creates a big DBMS_OUTPUT with title bars
    ---------------------------------------------------------------------------*/
    PROCEDURE Local_Output_Header(text_        IN VARCHAR2,
                                p1_          IN VARCHAR2 DEFAULT NULL,
                                p2_          IN VARCHAR2 DEFAULT NULL,
                                p3_          IN VARCHAR2 DEFAULT NULL,
                                p4_          IN VARCHAR2 DEFAULT NULL) IS
    temp_ VARCHAR2(32000);
    len_  NUMBER;
    BEGIN
    IF Is_NonLive_Environment THEN
        temp_ := text_;
        temp_ := replace(temp_, ':P1', p1_);
        temp_ := replace(temp_, ':P2', p2_);
        temp_ := replace(temp_, ':P3', p3_);
        temp_ := replace(temp_, ':P4', p4_);
        len_  := LENGTH(temp_) + 5;
        FOR i IN 1..len_ LOOP
        DBMS_OUTPUT.PUT('-');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('-');
        DBMS_OUTPUT.PUT_LINE(UPPER('   ' || temp_));
        FOR i IN 1..len_ LOOP
        DBMS_OUTPUT.PUT('-');
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('-');
    END IF;
    END Local_Output_Header;
    
    /*---------------------------------------------------------------------------
    Method Name:   Background_Output
    Description:   Outputs text to background job
    ---------------------------------------------------------------------------*/
    PROCEDURE Background_Output (text_   IN VARCHAR2,
                                type_   IN VARCHAR2 DEFAULT 'INFO',
                                p1_     IN VARCHAR2 DEFAULT NULL,
                                p2_     IN VARCHAR2 DEFAULT NULL,
                                p3_     IN VARCHAR2 DEFAULT NULL,
                                p4_     IN VARCHAR2 DEFAULT NULL) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    temp_ VARCHAR2(32000);
    BEGIN
    temp_ := text_;
    temp_ := replace(temp_, ':P1', p1_);
    temp_ := replace(temp_, ':P2', p2_);
    temp_ := replace(temp_, ':P3', p3_);
    temp_ := replace(temp_, ':P4', p4_);
    IFSAPP.TRANSACTION_SYS.Set_Status_Info(SUBSTR(temp_, 0, 2000), type_);
    COMMIT;
    END Background_Output;
    
    /*---------------------------------------------------------------------------
    Method Name:   Background_Output_Spacer
    Description:   Outputs spacer to background job
    ---------------------------------------------------------------------------*/
    PROCEDURE Background_Output_Spacer IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    IFSAPP.TRANSACTION_SYS.Set_Status_Info('------------------------', 'INFO');
    COMMIT;
    END Background_Output_Spacer;
    
    -----------------------------------------------------------------------------
    ---------------------------- FOUNDATION1 METHODS ----------------------------
    -----------------------------------------------------------------------------
    PROCEDURE Init IS
    BEGIN
    NULL;
    END Init;

END SYCO_OUTPUT_API;

To find out more about working with us, please get in touch.