CREATE OR REPLACE PACKAGE PLOG IS /** * package name : PLOG *
*
*See : http://log4plsql.sourceforge.net *
*
*Objectif : Generic tool of log in a Oracle database *same prototype and functionality that log4j. *http://jakarta.apache.org/log4j *


* for exemple and documentation See: http://log4plsql.sourceforge.net/docs/UserGuide.html * * Default table of log level * 1 The OFF has the highest possible rank and is intended to turn off logging.
* 2 The FATAL level designates very severe error events that will presumably lead the application to abort.
* 3 The ERROR level designates error events that might still allow the application to continue running.
* 4 The WARN level designates potentially harmful situations.
* 5 The INFO level designates informational messages that highlight the progress of the application at coarse-grained level.
* 6 The DEBUG Level designates fine-grained informational events that are most useful to debug an application.
* 7 The ALL has the lowest possible rank and is intended to turn on all logging.
* * *



*All data is store in TLOG table
* ID number,
* LDate DATE default sysdate,
* LHSECS number,
* LLEVEL number,
* LSECTION varchar2(2000),
* LTEXTE varchar2(2000),
* LUSER VARCHAR2(30),
* CONSTRAINT pk_TLOG PRIMARY KEY (id)
*


* * *@headcom *
*
*
*History who date comment *V0 Guillaume Moulard 08-AVR-98 Creation *V1 Guillaume Moulard 16-AVR-02 Add DBMS_PIPE funtionnality *V1.1 Guillaume Moulard 16-AVR-02 Increase a date log precision for bench user hundredths of seconds of V$TIMER *V2.0 Guillaume Moulard 07-MAY-02 Extend call prototype for more by add a default value *V2.1 Guillaume Moulard 07-MAY-02 optimisation for purge process *V2.1.1 Guillaume Moulard 22-NOV-02 patch bug length message identify by Lu Cheng *V2.2 Guillaume Moulard 23-APR-03 use automuns_transaction use Dan Catalin proposition *V2.3 Guillaume Moulard 30-APR-03 Add is[Debug|Info|Warn|Error]Enabled requested by Dan Catalin *V2.3.1 jan-pieter 27-JUN-03 supp to_char(to_char line ( line 219 ) *V3 Guillaume Moulard 05-AUG-03 *update default value of PLOGPARAM.DEFAULT_LEVEL -> DEBUG * *new: log in alert.log, trace file (thank to andreAs for information) * *new: log with DBMS_OUTPUT (Wait -> SET SERVEROUTPUT ON) * *new: log full_call_stack * *upd: now is possible to log in table and in log4j * *upd: ctx and init funtion parameter. * *new: getLOG4PLSQVersion return string Version * * use dynamique *upd: create of PLOGPARAM for updatable parameter * *new: getLevelInText return the text level for one level * ************************************************************** * I read a very interesting article write by Steven Feuerstein * - Handling Exceptional Behavior - * this 2 new features is inspired direcly by this article * ************************************************************** * * new: assert procedure * * new: new procedure error prototype from log SQLCODE and SQLERRM *V3.1 Guillaume Moulard 23-DEC-03 add functions for customize the log level *V3.1.1 Guillaume Moulard 29-JAN-04 increase perf : propose by Detlef *V3.1.2 Guillaume Moulard 02-FEV-04 *new: Log4JbackgroundProcess create a thread for each database connexion *V3.1.2 Guillaume Moulard 02-FEV-04 *new: Log4JbackgroundProcess create a thread for each database connexion *V3.1.2.1 Guillaume Moulard 12-FEV-04 *BUG: bad version number, bad log with purge and isXxxxEnabled Tx to Pascal Mwakuye *V3.1.2.2 Guillaume Moulard 27-FEV-04 *BUG: pbs with call stack *V3.2 Greg Woolsey 29-MAR-04 add MDC (Mapped Domain Context) Feature *
*
* Copyright (C) LOG4PLSQL project team. All rights reserved.
*
* This software is published under the terms of the The LOG4PLSQL
* Software License, a copy of which has been included with this
* distribution in the LICENSE.txt file.
* see:

* */ ------------------------------------------------------------------- -- Constants (no modification please) ------------------------------------------------------------------- NOLEVEL CONSTANT NUMBER := -999.99 ; DEFAULTEXTMESS CONSTANT VARCHAR2(20) := 'GuillaumeMoulard'; ------------------------------------------------------------------- -- Constants (tools general parameter) -- you can update regard your context ------------------------------------------------------------------- -- in V3 this section is now store in plogparam. Is note necessary for -- the end user to update this curent package. ------------------------------------------------------------------- -- Constants (tools internal parameter) ------------------------------------------------------------------- -- The OFF has the highest possible rank and is intended to turn off logging. LOFF CONSTANT number := 10 ; -- The FATAL level designates very severe error events that will presumably lead the application to abort. LFATAL CONSTANT number := 20 ; -- The ERROR level designates error events that might still allow the application to continue running. LERROR CONSTANT number := 30 ; -- The WARN level designates potentially harmful situations. LWARN CONSTANT number := 40 ; -- The INFO level designates informational messages that highlight the progress of the application at coarse-grained level. LINFO CONSTANT number := 50 ; -- The DEBUG Level designates fine-grained informational events that are most useful to debug an application. LDEBUG CONSTANT number := 60 ; -- The ALL has the lowest possible rank and is intended to turn on all logging. LALL CONSTANT number := 70 ; -- raise constante ERR_CODE_DBMS_PIPE CONSTANT NUMBER := -20503; MES_CODE_DBMS_PIPE CONSTANT VARCHAR2(100) := 'error DBMS_PIPE.send_message. return code :'; ------------------------------------------------------------------- -- Public declaration of package ------------------------------------------------------------------- TYPE LOG_CTX IS RECORD ( -- Context de log isDefaultInit BOOLEAN default FALSE , LLEVEL TLOG.LLEVEL%type , LSECTION TLOG.LSECTION%type , LTEXTE TLOG.LTEXTE%type , USE_LOG4J BOOLEAN , USE_OUT_TRANS BOOLEAN , USE_LOGTABLE BOOLEAN , USE_ALERT BOOLEAN , USE_TRACE BOOLEAN , USE_DBMS_OUTPUT BOOLEAN , INIT_LSECTION TLOG.LSECTION%type , INIT_LLEVEL TLOG.LLEVEL%type , DBMS_PIPE_NAME VARCHAR2(255) , DBMS_OUTPUT_WRAP PLS_INTEGER ); ------------------------------------------------------------------- -- Public Procedure and function ------------------------------------------------------------------- /** For use a log debug level */ PROCEDURE debug ( pTEXTE IN TLOG.LTEXTE%type default null -- log text ); PROCEDURE debug ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pTEXTE IN TLOG.LTEXTE%type default null -- log text ); /** For use a log info level */ PROCEDURE info ( pTEXTE IN TLOG.LTEXTE%type default null -- log text ); PROCEDURE info ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pTEXTE IN TLOG.LTEXTE%type default null -- log text ); /** For use a log warning level */ PROCEDURE warn ( pTEXTE IN TLOG.LTEXTE%type default null -- log text ); PROCEDURE warn ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pTEXTE IN TLOG.LTEXTE%type default null -- log text ); /** For use a log error level new V3 call without argument or only with one context, SQLCODE - SQLERRM is log. */ PROCEDURE error ( pTEXTE IN TLOG.LTEXTE%type default null -- log text ); PROCEDURE error ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pTEXTE IN TLOG.LTEXTE%type default null -- log text ); /** For use a log fatal level */ PROCEDURE fatal ( pTEXTE IN TLOG.LTEXTE%type default null -- log text ); PROCEDURE fatal ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pTEXTE IN TLOG.LTEXTE%type default null -- log text ); /** Generique procedure (use only for define your application level DEFINE_APPLICATION_LEVEL=TRUE) */ PROCEDURE log ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pLEVEL IN TLOG.LLEVEL%type , -- log level pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text ); PROCEDURE log ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pLEVEL IN TLOGLEVEL.LCODE%type , -- log level pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text ); PROCEDURE log ( pLEVEL IN TLOG.LLEVEL%type , -- log level pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text ); PROCEDURE log ( pLEVEL IN TLOGLEVEL.LCODE%type , -- log level pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text ) ; /** context initialisation @param pSECTION default = NULL => PLSQL CALL STACK @param pLEVEL default = PLOGPARAM.DEFAULT_LEVEL -> LDEBUG @param pLOG4J default = PLOGPARAM.DEFAULT_USE_LOG4J -> FALSE (If true backgroun process is require) @param pLOGTABLE default = PLOGPARAM.DEFAULT_LOG_TABLE -> TRUE @param pOUT_TRANS default = PLOGPARAM.DEFAULT_LOG_OUT_TRANS -> TRUE @param pALERT default = PLOGPARAM.DEFAULT_LOG_ALERT -> FALSE @param pTRACE default = PLOGPARAM.DEFAULT_LOG_TRACE -> FALSE @param pDBMS_OUTPUT default = PLOGPARAM.DEFAULT_DBMS_OUTPUT -> FALSE @return new context LOG_CTX */ FUNCTION init ( pSECTION IN TLOG.LSECTION%type default NULL , -- root of the tree section pLEVEL IN TLOG.LLEVEL%type default PLOGPARAM.DEFAULT_LEVEL , -- log level (Use only for debug) pLOG4J IN BOOLEAN default PLOGPARAM.DEFAULT_USE_LOG4J, -- if true the log is send to log4j pLOGTABLE IN BOOLEAN default PLOGPARAM.DEFAULT_LOG_TABLE, -- if true the log is insert into tlog pOUT_TRANS IN BOOLEAN default PLOGPARAM.DEFAULT_LOG_OUT_TRANS, -- if true the log is in transactional log pALERT IN BOOLEAN default PLOGPARAM.DEFAULT_LOG_ALERT, -- if true the log is write in alert.log pTRACE IN BOOLEAN default PLOGPARAM.DEFAULT_LOG_TRACE, -- if true the log is write in trace file pDBMS_OUTPUT IN BOOLEAN default PLOGPARAM.DEFAULT_DBMS_OUTPUT, -- if true the log is send in standard output (DBMS_OUTPUT.PUT_LINE) pDBMS_PIPE_NAME IN VARCHAR2 default PLOGPARAM.DEFAULT_DBMS_PIPE_NAME, -- pDBMS_OUTPUT_WRAP IN PLS_INTEGER default PLOGPARAM.DEFAULT_DBMS_OUTPUT_LINE_WRAP ) RETURN LOG_CTX; /** Sections management : init a new section */ PROCEDURE setBeginSection ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pSECTION IN TLOG.LSECTION%type -- log text ); /** Sections management : get a current section @return current section */ FUNCTION getSection ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN TLOG.LSECTION%type; /** Sections management : get a default section @return current section */ FUNCTION getSection RETURN TLOG.LSECTION%type; /** Sections management : close a Section
without pSECTION : clean all section */ PROCEDURE setEndSection ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pSECTION IN TLOG.LSECTION%type default 'EndAllSection' -- log text ); /** Levels Management : increase level
it is possible to dynamically update with setLevell the level of log
call of setLevel without paramettre repositions the levels has that specifier
in the package
erreur possible : -20501, 'Set Level not in LOG predefine constantes'
*/ PROCEDURE setLevel ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pLEVEL IN TLOG.LLEVEL%type default NOLEVEL -- Higher level to allot dynamically ); PROCEDURE setLevel ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pLEVEL IN TLOGLEVEL.LCODE%type -- Higher level to allot dynamically ); /** Levels Management : Get a current level */ FUNCTION getLevel ( pCTX IN LOG_CTX -- Context ) RETURN TLOG.LLEVEL%type; /** Levels Management : Get a default level */ FUNCTION getLevel RETURN TLOG.LLEVEL%type; /** Levels Management : return true if current level is Debug */ FUNCTION isDebugEnabled ( pCTX IN LOG_CTX -- Context ) RETURN boolean; /** Levels Management : return true if default level is Debug */ FUNCTION isDebugEnabled RETURN boolean; /** Levels Management : return true if current level is Info */ FUNCTION isInfoEnabled ( pCTX IN LOG_CTX -- Context ) RETURN boolean; /** Levels Management : return true if default level is Info */ FUNCTION isInfoEnabled RETURN boolean; /** Levels Management : return true if current level is Warn */ FUNCTION isWarnEnabled ( pCTX IN LOG_CTX -- Context ) RETURN boolean; /** Levels Management : return true if default level is Warn */ FUNCTION isWarnEnabled RETURN boolean; /** Levels Management : return true if current level is Error */ FUNCTION isErrorEnabled ( pCTX IN LOG_CTX -- Context ) RETURN boolean; /** Levels Management : return true if default level is Error */ FUNCTION isErrorEnabled RETURN boolean; /** Levels Management : return true if current level is Fatal */ FUNCTION isFatalEnabled ( pCTX IN LOG_CTX -- Context ) RETURN boolean; /** Levels Management : return true if default level is Fatal */ FUNCTION isFatalEnabled RETURN boolean; /** Transactional management : define a transaction mode
parameter transactional mode
TRUE => Log in transaction
FALSE => Log out off transaction
*/ PROCEDURE setTransactionMode ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inTransaction IN boolean default TRUE -- TRUE => Log in transaction -- FALSE => Log out off transaction ); /** Transactional management : retun a transaction mode
TRUE => Log in transaction
FALSE => Log out off transaction
*/ FUNCTION getTransactionMode ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN boolean; /** Transactional management : retun a default transaction mode
TRUE => Log in transaction
FALSE => Log out off transaction
*/ FUNCTION getTransactionMode RETURN boolean; /** USE_LOG4J management : define a USE_LOG4J destination mode
TRUE => Log is send to log4j
FALSE => Log is not send to log4j
*/ PROCEDURE setUSE_LOG4JMode ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inUSE_LOG4J IN boolean default TRUE -- TRUE => Log is send to USE_LOG4J -- FALSE => Log is not send to USE_LOG4J ); /** USE_LOG4J management : retun a USE_LOG4J mode
TRUE => Log is send to USE_LOG4J
FALSE => Log is not send to USE_LOG4J
*/ FUNCTION getUSE_LOG4JMode ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN boolean; /** USE_LOG4J management : retun a USE_LOG4J mode
TRUE => Log is send to USE_LOG4J
FALSE => Log is not send to USE_LOG4J
*/ FUNCTION getUSE_LOG4JMode RETURN boolean; /** LOG_TABLE management : define a LOG_TABLE destination mode
TRUE => Log is send to LOG_TABLE
FALSE => Log is not send to LOG_TABLE
*/ PROCEDURE setLOG_TABLEMode ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inLOG_TABLE IN boolean default TRUE -- TRUE => Log is send to LOG_TABLE -- FALSE => Log is not send to LOG_TABLE ); /** LOG_TABLE management : retun a LOG_TABLE mode
TRUE => Log is send to LOG_TABLE
FALSE => Log is not send to LOG_TABLE
*/ FUNCTION getLOG_TABLEMode ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN boolean; /** LOG_TABLE management : retun a LOG_TABLE mode
TRUE => Log is send to LOG_TABLE
FALSE => Log is not send to LOG_TABLE
*/ FUNCTION getLOG_TABLEMode RETURN boolean; /** LOG_ALERT management : define a LOG_ALERT destination mode
TRUE => Log is send to LOG_ALERT
FALSE => Log is not send to LOG_ALERT
*/ PROCEDURE setLOG_ALERTMode ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inLOG_ALERT IN boolean default TRUE -- TRUE => Log is send to LOG_ALERT -- FALSE => Log is not send to LOG_ALERT ); /** LOG_ALERT management : retun a LOG_ALERT mode
TRUE => Log is send to LOG_ALERT
FALSE => Log is not send to LOG_ALERT
*/ FUNCTION getLOG_ALERTMode ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN boolean; /** LOG_ALERT management : retun a LOG_ALERT mode
TRUE => Log is send to LOG_ALERT
FALSE => Log is not send to LOG_ALERT
*/ FUNCTION getLOG_ALERTMode RETURN boolean; /** LOG_TRACE management : define a LOG_TRACE destination mode
TRUE => Log is send to LOG_TRACE
FALSE => Log is not send to LOG_TRACE
*/ PROCEDURE setLOG_TRACEMode ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inLOG_TRACE IN boolean default TRUE -- TRUE => Log is send to LOG_TRACE -- FALSE => Log is not send to LOG_TRACE ); /** LOG_TRACE management : retun a LOG_TRACE mode
TRUE => Log is send to LOG_TRACE
FALSE => Log is not send to LOG_TRACE
*/ FUNCTION getLOG_TRACEMode ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN boolean; /** LOG_TRACE management : retun a LOG_TRACE mode
TRUE => Log is send to LOG_TRACE
FALSE => Log is not send to LOG_TRACE
*/ FUNCTION getLOG_TRACEMode RETURN boolean; /** DBMS_OUTPUT management : define a DBMS_OUTPUT destination mode
TRUE => Log is send to DBMS_OUTPUT
FALSE => Log is not send to DBMS_OUTPUT
*/ PROCEDURE setDBMS_OUTPUTMode ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inDBMS_OUTPUT IN boolean default TRUE -- TRUE => Log is send to DBMS_OUTPUT -- FALSE => Log is not send to DBMS_OUTPUT ); /** DBMS_OUTPUT management : retun a DBMS_OUTPUT mode
TRUE => Log is send to DBMS_OUTPUT
FALSE => Log is not send to DBMS_OUTPUT
*/ FUNCTION getDBMS_OUTPUTMode ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN boolean; /** DBMS_OUTPUT management : retun a DBMS_OUTPUT mode
TRUE => Log is send to DBMS_OUTPUT
FALSE => Log is not send to DBMS_OUTPUT
*/ FUNCTION getDBMS_OUTPUTMode RETURN boolean; /** assert log a messge is pCondition is FALSE if pRaiseExceptionIfFALSE = TRUE the message is raise
@param pCTX IN OUT NOCOPY LOG_CTX -> Context @param pCONDITION IN BOOLEAN -> error condition @param pLogErrorMessageIfFALSE IN VARCHAR2 default 'assert condition error' -> message if pCondition is true @param pLogErrorCodeIfFALSE IN NUMBER default -20000 -> error code is pCondition is true range -20000 .. -20999 @param pRaiseExceptionIfFALSE IN BOOLEAN default FALSE -> if true raise pException_in if pCondition is true @param pLogErrorReplaceError IN BOOLEAN default FALSE -> TRUE, the error is placed on the stack of previous errors. If FALSE (the default), the error replaces all previous errors (see Oracle Documentation RAISE_APPLICATION_ERROR) @return log a messge if pCondition is FALSE. If pRaiseExceptionIfFALSE = TRUE the message is raise */ PROCEDURE assert ( pCONDITION IN BOOLEAN , -- error condition pLogErrorMessageIfFALSE IN VARCHAR2 default 'assert condition error' , -- message if pCondition is true pLogErrorCodeIfFALSE IN NUMBER default -20000 , -- error code is pCondition is true range -20000 .. -20999 pRaiseExceptionIfFALSE IN BOOLEAN default FALSE , -- if true raise pException_in if pCondition is true pLogErrorReplaceError in BOOLEAN default FALSE -- TRUE, the error is placed on the stack of previous errors. -- If FALSE (the default), the error replaces all previous errors -- see Oracle Documentation RAISE_APPLICATION_ERROR ); /** assert log a messge is pCondition is FALSE if pRaiseExceptionIfFALSE = TRUE the message is raise
@param pCTX IN OUT NOCOPY LOG_CTX -> Context @param pCONDITION IN BOOLEAN -> error condition @param pLogErrorMessageIfFALSE IN VARCHAR2 default 'assert condition error' -> message if pCondition is true @param pLogErrorCodeIfFALSE IN NUMBER default -20000 -> error code is pCondition is true range -20000 .. -20999 @param pRaiseExceptionIfFALSE IN BOOLEAN default FALSE -> if true raise pException_in if pCondition is true @param pLogErrorReplaceError IN BOOLEAN default FALSE -> TRUE, the error is placed on the stack of previous errors. If FALSE (the default), the error replaces all previous errors (see Oracle Documentation RAISE_APPLICATION_ERROR) @return log a messge if pCondition is FALSE. If pRaiseExceptionIfFALSE = TRUE the message is raise */ PROCEDURE assert ( pCTX IN OUT NOCOPY LOG_CTX , -- Context pCONDITION IN BOOLEAN , -- error condition pLogErrorMessageIfFALSE IN VARCHAR2 default 'assert condition error' , -- message if pCondition is true pLogErrorCodeIfFALSE IN NUMBER default -20000 , -- error code is pCondition is true range -20000 .. -20999 pRaiseExceptionIfFALSE IN BOOLEAN default FALSE , -- if true raise pException_in if pCondition is true pLogErrorReplaceError in BOOLEAN default FALSE -- TRUE, the error is placed on the stack of previous errors. -- If FALSE (the default), the error replaces all previous errors -- see Oracle Documentation RAISE_APPLICATION_ERROR ); /** full_call_stack log result of dbms_utility.format_call_stack
some time is necessary for debug code. */ PROCEDURE full_call_stack; PROCEDURE full_call_stack ( pCTX IN OUT NOCOPY LOG_CTX -- Context ); /** getLOG4PLSQVersion return a string with a current version
*/ FUNCTION getLOG4PLSQVersion return varchar2; /** getLevelInText return a string with a level in send in parameter
*/ FUNCTION getLevelInText ( pLevel TLOG.LLEVEL%type default PLOGPARAM.DEFAULT_LEVEL ) return varchar2; /** getTextInLevel return a level with a String in send in parameter
*/ FUNCTION getTextInLevel ( pCode TLOGLEVEL.LCODE%type ) return TLOG.LLEVEL%type ; /** DBMS_PIPE_NAME management */ FUNCTION getDBMS_PIPE_NAME ( pCTX IN OUT NOCOPY LOG_CTX -- Context ) RETURN varchar2; FUNCTION getDBMS_PIPE_NAME RETURN varchar2; PROCEDURE setDBMS_PIPE_NAME ( pCTX IN OUT NOCOPY LOG_CTX , -- Context inDBMS_PIPE_NAME IN VARCHAR2 ); ------------------------------------------------------------------- -- ------------------------------------------------------------------- /** admin functionality : delete rows in table TLOG and commit */ PROCEDURE purge ; PROCEDURE purge ( pCTX IN OUT NOCOPY LOG_CTX -- Context ); /** admin functionality : delete rows in table TLOG with date max and commit */ PROCEDURE purge ( pCTX IN OUT NOCOPY LOG_CTX , -- Context DateMax IN Date -- All record to old as deleted ); END PLOG; / sho error ------------------------------------------------------------------- -- End of document -------------------------------------------------------------------