Andrew Fraser DBA

Logger alternative to dbms_output Oracle Log4j

Simple logger.write alternative to dbms_output.put_line.

Call with:
logger.write ( ‘hello world!’ ) ;
or even just:
logger.write ;

It does the dbms_output.put_line but also writes asynchronously to a log_table, and gives info on calling line number and procedure name.

CREATE TABLE log_table ( dt DATE , item VARCHAR2(4000) , source VARCHAR2(4000) , line NUMBER ) ;
CREATE OR REPLACE PACKAGE logger AS
PROCEDURE write ( i_item IN VARCHAR2 DEFAULT NULL ) ;
END logger ;
/
CREATE OR REPLACE PACKAGE BODY logger AS
PROCEDURE write ( i_item IN VARCHAR2 DEFAULT NULL ) IS
   l_line CONSTANT NUMBER := utl_call_stack.unit_line ( dynamic_depth => 2 ) ;
   l_source CONSTANT VARCHAR2(4000) := utl_call_stack.concatenate_subprogram ( qualified_name => utl_call_stack.subprogram ( dynamic_depth => 2 ) ) ;
   PRAGMA AUTONOMOUS_TRANSACTION ;
BEGIN
   dbms_output.put_line ( i_item
      || ' - line '
      || TO_CHAR ( l_line )
      || ' '
      || LOWER ( l_source )
      || ' - '
      || TO_CHAR ( SYSDATE , 'Dy DD-Mon-YYYY HH24:MI:SS' )
      ) ;
   INSERT INTO log_table t ( t.item , t.line , t.source , t.dt )
   VALUES ( SUBSTR ( i_item , 1 , 4000 )
      , l_line
      , l_source
      , SYSDATE
      ) ;
   COMMIT WRITE BATCH NOWAIT ;
EXCEPTION WHEN OTHERS THEN NULL ;  -- don't want any failures to write logging info to break the entire calling pl/sql.
END write ;
END logger ;
/