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
- A table to store log information
- A sequence to generate unique ID value to each record in the table
- A trigger to assign unique value to ID column
- A PL/SQL procedure with a logic to insert debug messages to debug table
- 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
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.