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 ;
/
Leave a Reply