plog_pipe.sql

pl/sql中记log的函数

/*<TOAD_FILE_CHUNK>*/
CREATE OR REPLACE PACKAGE PLOG_PIPE
AS

/**
 * Read the next message from the given or default pipe, and return each 
 * Log4PlSql message component as an OUT variable.  Wait the specified number
 * of seconds before timing out.
 * 
 * @param pTIMEOUT			IN		NUMBER seconds to wait for the next pipe message
 * @param pID         		OUT	log message sequence number 
 * @param pLDATE      		OUT	date the message was logged, as a DATE 
 * @param pLHSECS     		OUT	hundredths of seconds for the pLDATE parameter
 * @param pLLEVEL     		OUT	Level code (number) of the message
 * @param pLSECTION   		OUT	log section
 * @param pLUSER      		OUT	user schema the log message originated from 
 * @param pCOMMAND    		OUT	Log4PlSql command - only one value, currently 
 * @param pLTEXTE     		OUT	Message text
 * @param pMDC_KEYS	 		OUT	delimited string of MDC keys
 * @param pMDC_VALUES 		OUT	delimited string of MDC values
 * @param pMDC_SEPARATOR	OUT	delimiter for MDC strings
 * @param pPIPE_NAME			IN		VARCHAR2 DEFAULT PLOGPARAM.DEFAULT_DBMS_PIPE_NAME
 * 
 * @return the return code from DBMS_PIPE.RECEIVE_MESSAGE.
 */
FUNCTION READ_MESSAGE(
    pTIMEOUT			IN		NUMBER                           ,
    pID         		OUT	TLOG.ID%type                     ,
    pLDATE      		OUT	TLOG.LDATE%type                  ,
    pLHSECS     		OUT	TLOG.LHSECS%type                 ,
    pLLEVEL     		OUT	TLOG.LLEVEL%type                 ,
    pLSECTION   		OUT	TLOG.LSECTION%type               ,
    pLUSER      		OUT	TLOG.LUSER%type                  ,
	 pCOMMAND    		OUT	VARCHAR2									,
    pLTEXTE     		OUT	TLOG.LTEXTE%type						,
	 pMDC_KEYS	 		OUT	VARCHAR2									,
	 pMDC_VALUES 		OUT	VARCHAR2									,
	 pMDC_SEPARATOR	OUT	VARCHAR2									,
	 pPIPE_NAME			IN		VARCHAR2 DEFAULT PLOGPARAM.DEFAULT_DBMS_PIPE_NAME
) RETURN NUMBER;



END PLOG_PIPE;
/
/*<TOAD_FILE_CHUNK>*/

CREATE OR REPLACE PACKAGE BODY PLOG_PIPE
AS

/**
Return log line data from the current pipe. 
The return value is the DBMS_PIPE.receive_message return value.
0 = success
1 = timeout (no message in pTIMEOUT seconds) 
3 = interrupt (don't know when that could happen - session killed?) 
 */
FUNCTION READ_MESSAGE(
    pTIMEOUT			IN		NUMBER                           ,
    pID         		OUT	TLOG.ID%type                     ,
    pLDATE      		OUT	TLOG.LDATE%type                  ,
    pLHSECS     		OUT	TLOG.LHSECS%type                 ,
    pLLEVEL     		OUT	TLOG.LLEVEL%type                 ,
    pLSECTION   		OUT	TLOG.LSECTION%type               ,
    pLUSER      		OUT	TLOG.LUSER%type                  ,
	 pCOMMAND    		OUT	VARCHAR2									,
    pLTEXTE     		OUT	TLOG.LTEXTE%type						,
	 pMDC_KEYS	 		OUT	VARCHAR2									,
	 pMDC_VALUES 		OUT	VARCHAR2									,
	 pMDC_SEPARATOR	OUT	VARCHAR2									,
	 pPIPE_NAME			IN		VARCHAR2 DEFAULT PLOGPARAM.DEFAULT_DBMS_PIPE_NAME
) RETURN NUMBER IS
	retval INTEGER;
	nextItemType INTEGER;
	tmp NUMBER;
	vPIPE_NAME varchar2(128) := pPIPE_NAME;
BEGIN
	if vPIPE_NAME is null then vPIPE_NAME := PLOGPARAM.DEFAULT_DBMS_PIPE_NAME; end if;
	
	retval := DBMS_PIPE.receive_message(vPIPE_NAME, pTIMEOUT);
	
	IF retval = 0 THEN
		DBMS_PIPE.unpack_message(pID);
		DBMS_PIPE.unpack_message(pLDATE);
		DBMS_PIPE.unpack_message(pLHSECS);
		DBMS_PIPE.unpack_message(pLLEVEL);
		DBMS_PIPE.unpack_message(pLSECTION);
		DBMS_PIPE.unpack_message(pLTEXTE);
		DBMS_PIPE.unpack_message(pLUSER);
		DBMS_PIPE.unpack_message(pCOMMAND);
		DBMS_PIPE.unpack_message(pMDC_KEYS);
		DBMS_PIPE.unpack_message(pMDC_VALUES);
		DBMS_PIPE.unpack_message(pMDC_SEPARATOR);
	END IF;
	
	RETURN retval; 
END READ_MESSAGE;

END PLOG_PIPE;
/