Andrew Fraser DBA

Logger Oracle PL/SQL

A logger.write procedure as an extension to dbms_output.put_line.

It does a dbms_output.put_line, but also writes same message into a log table.

Unlike dbms_output, the writes to the log table are visible while the program is running, via an asynchronous autonomous transaction.

Output (both types) includes the line number and the name of calling package/procedure, for easier debugging.

Usage: in your pl/sql code, include lines like:

logger.write ;
logger.write ( 'Hello world!' ) ;
logger.write ( 'Inserted ' || TO_CHAR ( SQL%ROWCOUNT ) || ' rows.' ) ;

Initial setup:

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 ;
/