65.9K
CodeProject is changing. Read more.
Home

Logging error, debug, info messages into log tables

starIconstarIconstarIconstarIconstarIcon

5.00/5 (1 vote)

Jun 3, 2013

CPOL
viewsIcon

6500

Log4DB

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 
  /* Debug Level 0->Error, 1->Warning, 2->Info, 9 - all */
  ln_debug_level number; 
  PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  ln_debug_level := 9; -- This can cached into a table as parameter  ;
  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. 
============================================