Adding debug statements in a PL/SQL block/procedure/function

The most common strategy used for adding debug statement is to create a temp table for debug purpose and log the data into it via autonomous transaction.

Creating autonomous transaction is very important as your transaction with business logic may get rollback due to any unexpected error.

The below script can be run for adding the temp table and autonomous procedure for adding the logs.

enable_debug.sql :

CREATE TABLE DBUGTAB(ERROR_CODE NUMBER, MODULE_NAME VARCHAR2(4000), ERROR_MSG VARCHAR2(4000));  CREATE or REPLACE PACKAGE dbug as PROCEDURE debug(p_module_name VARCHAR2,p_error_msg in VARCHAR2);  END dbug;  /  CREATE OR REPLACE PACKAGE BODY dbug AS  PROCEDURE debug(p_module_name VARCHAR2, p_error_msg in VARCHAR2) is  PRAGMA AUTONOMOUS_TRANSACTION;     l_errornum    NUMBER;  BEGIN     SELECT nvl(max(ERROR_CODE),0) into l_errornum from dbugtab;     l_errornum := l_errornum+1;     INSERT into dbugtab(error_code, error_msg ,module_name)     values         (l_errornum, p_error_msg, p_module_name);     COMMIT;  END;  END dbug;  /

For adding the debug statements, the below sample can be followed:
dbug.debug('anbcdcd','adadad');

To query the debug statements in the order of their insertion:
SELECT * FROM dbugtab ORDER BY error_code;