How to Generate Debug Logging for Custom Extensions

It would be tiresome for most of us to debug issues especially in extensions when we have no clue of flow of process execution and also the values of variables in intermittent stages. Debugging becomes more complex when most of custom logic is in huge pl/sql scripts.

Today in this article I will show how to implement debugging feature which I regularly use in the custom extensions I develop.

To generate debugging mechanism we need

1. A table to store log information
2. A sequence to generate unique ID value to each record in the table
3. A trigger to assign unique value to ID column
4. A PL/SQL procedure with a logic to insert debug messages to debug table
5. A PL/SQL statement to call the PL/SQL Debug procedure

Create Table to store Debug Messages

CREATE TABLE XX_DEBUG_LOG
  (
    id             NUMBER,
    component_name VARCHAR2(220),
    log_text       VARCHAR2(220),
    creation_date  VARCHAR2(220) DEFAULT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MM:SS')
  );

creation_date column has default value as sysdate which acts like a time stamp.

Create a Sequence to generate unique ID

CREATE sequence XX_DEBUG_LOG_SEQ
 start with 1 
 increment BY 1 
 NOCYCLE 
 NOCACHE;

Create a Trigger to assign unique value to ID column

CREATE OR REPLACE TRIGGER XX_DEBUG_LOG_t1 before
  INSERT ON XX_DEBUG_LOG FOR EACH row BEGIN IF :new.id IS NULL THEN
  SELECT XX_DEBUG_LOG_seq.nextval INTO :new.id FROM dual;
END IF;
END;
/

Create a Procedure to insert Debug Message

CREATE OR REPLACE
PROCEDURE XX_INSERT_LOG_P(
    p_component_name VARCHAR2 ,
    p_log_text       VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT
  INTO XX_DEBUG_LOG
    (
      component_name,
      log_text
    )
    VALUES
    (
      p_component_name,
      p_log_text
    );
  COMMIT;
END;
/

This procedure is run as an PRAGMA AUTONOMOUS_TRANSACTION so that it will run independently of current session and the COMMIT statement in this procedure would not impact the parent session which calls this procedure. Since this is an AUTONOMOUS transaction debug messages will be captured even if there is any ROLLBACK statement in the parent session.

Create PL/SQL statement to call the PL/SQL Debug procedure

XX_INSERT_LOG_P('comp_name','log message');

Example

DECLARE
  CURSOR EMP_CUR
  IS
    SELECT * FROM EMP;
  --
  c_comp_name VARCHAR2(20) := 'LIST EMP';
  --
BEGIN
  --
  XX_INSERT_LOG_P(c_comp_name,'in begin block');
  --
  FOR I IN EMP_CUR
  LOOP
    BEGIN
      --
      XX_INSERT_LOG_P(c_comp_name,'in loop '||i.ename);
      dbms_output.put_line(i.ename);
      --
    EXCEPTION
    WHEN OTHERS THEN
      XX_INSERT_LOG_P(C_COMP_NAME,'in Exception - '||I.ENAME||' Err - '||SQLERRM);
    END;
  END LOOP;
END;

Content in Debug Log table:

In this was you can use this debugging mechanism in extensions like workflows, forms/OA pages, Forms Personalizations, Business Event Subscriptions, what not.

Hope you liked this article, do leave a comment if you have questions.