Debugging made easy with Autonomous Transaction

Autonomous transaction is a very useful feature of PL/SQL, specially when debugging apps issues. 
At times, traditional apps logging like GMFZT log, FND log etc.. may not be of much help.
Not many debugging statements in your flow is one such example.

You can use the autonomous transaction with the code sample as given below.

Pros:

• Won't disrupt the flow.
• No change to transactional data that is waiting for commits in the flow.
• Logs are inserted into table, so simple to see the log
• Best method to know variable values at a specific point in code

Cons:

• Has to modify the Package/procedure/function and recompile, which sometimes invalidate the dependent packages.

Code:
Run the following code only once to create the required table/sequence/procedure

CREATE TABLE oradebug_tbl 
(id NUMBER, 
message VARCHAR2(1000), 
edate DATE); CREATE SEQUENCE oradebug_seq 
START WITH 1 INCREMENT BY 1; CREATE OR REPLACE PROCEDURE insert_into_oradebug
(message IN VARCHAR2) AS pragma autonomous_transaction; BEGIN INSERT INTO oradebug_tbl VALUES (oradebug_seq.NEXTVAL, message,SYSDATE); COMMIT; END;

Sample Usage:

            DECLARE 

             l_temp date; 

     BEGIN 

             l_temp := TRUNC(SYSDATE); 

             insert_into_oradebug('Value of l_temp='||l_temp); 

     END;

     / 

Check the log:

SELECT * 
     FROM oradebug_tbl

ORDER BY id DESC