Many of the complex database procedures or packages are very difficult to trace. I have seen developer modifying the production code by putting entries dbms_output.put_line("xxxx");
Instead recommend following way.
Below procedure centrally logs the information into the log table and the debug information can be captured into the table. This is similar to log4j in Java. Developer need to call the below procedure from calling routines.
At the end of this program have sample examples.
Debug level : ln_debug_level ; This parameter can be set as 0 in Production ; 1 UAT and 2 or 9 in development environments.
CREATE OR REPLACE PROCEDURE log4db(error_level IN Number,
p_remarks IN VARCHAR2) AS
ln_debug_level number;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
ln_debug_level := 9;
if (error_level <= ln_debug_level) THEN
INSERT INTO log4db_log
(sequence_no, remarks)
Values
(TO_CHAR(sysdate, 'YYYYMMDDHH24MISS'), substr(p_remarks, 1, 2000));
COMMIT;
end if;
END log4db;
============================================
a) Exception block
EXCEPTION
log4db(0,'fatal error occurred..');
-- one may add whatever required to be logged e.g procedure / function name etc, line number etc.
============================================
b) Within code logic as information
log4db(2,'Value of variable employee is..'||v_empl);
============================================
c) warning messages
log4db(1, 'Computed Salary is Zero' );
============================================
Based on the value stored in database for ( ln_debug_level ) inserts will work !
Application Server Class/Program can read this table and write an log file
and delete the records on frequent basis so that log4db_log do not grow very large.
============================================