Hi,
There is a small change in INSTR function or with my nls caratere set.
Now in my enviroment there is no problem.
Regards,
Guillaume Moulard
[EMAIL PROTECTED]
Project : LOG4PLSQL : Oracle Database Loggin tools
see : http://log4plsql.sourceforge.net/
and : http://sourceforge.net/mailarchive/forum.php?forum=log4plsql-all-info or
http://www.mail-archive.com/[EMAIL PROTECTED]
for [EMAIL PROTECTED]
-----Message d'origine-----
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Envoy� : jeudi 26 f�vrier 2004 15:25
� : [EMAIL PROTECTED]
Objet : [log4plsql] (no subject)
Hi,
I'm just starting to use log4plsql in our new application - think it's a great tool!
I'm having trouble getting call stack logging to work by default.
I'm running version 3.1.2.1, on Oracle 9i.
I've written the following very simple test code.
Create or replace package insert_error as
procedure insert_error;
end insert_error;
Create or replace package outer_insert_error as
procedure outer_insert_error;
end outer_insert_error;
create or replace package body insert_error as
procedure insert_error is
begin
plog.fatal('insert');
end insert_error;
end insert_error;
create or replace package body outer_insert_error as
procedure outer_insert_error is
begin
insert_error.insert_error();
end outer_insert_error;
end outer_insert_error;
I then run this using
begin
outer_insert_error.outer_insert_error();
end;
/
And doing a select from vlog, I would expect to see some information regarding the
call stack, but in fact what I get is as
follows:
Can anyone suggest an answer?
Thanks in advance for your help,
Scott Robinson.
CREATE OR REPLACE
PACKAGE BODY PLOG IS
-------------------------------------------------------------------
--
-- Nom package : PLOG
--
-- Objectif : plog code
--
-- Version : 3.0
-------------------------------------------------------------------
-- see package spec for history
-------------------------------------------------------------------
-------------------------------------------------------------------
-- Variable global priv� au package
-------------------------------------------------------------------
/*
* 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: <http://log4plsql.sourceforge.net> */
-------------------------------------------------------------------
LOG4PLSQL_VERSION VARCHAR2(200) := '3.1.2.1';
-------------------------------------------------------------------
-- Code priv� au package
-------------------------------------------------------------------
-------------------------------------------------------------------
--------------------------------------------------------------------
FUNCTION getNextID
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
) RETURN TLOG.ID%type
IS
temp number;
BEGIN
select SLOG.nextval into temp from dual;
return temp;
end getNextID;
----------------------------------------------------------------------
--function instrLast(ch1 varchar2, ch2 varchar2) return number
--is
--ret number := 0;
--begin
-- FOR i IN REVERSE 0..length(Ch1) LOOP
-- if instr(substr(ch1,i),ch2) > 0 then
-- return i;
-- end if;
-- end loop;
-- return ret;
--end;
--------------------------------------------------------------------
FUNCTION calleurname return varchar2
IS
endOfLine constant char(1) := chr(10);
endOfField constant char(1) := chr(32);
nbrLine number;
ptFinLigne number;
ptDebLigne number;
ptDebCode number;
pt1 number;
cpt number;
allLines varchar2(4000);
resultat varchar2(4000);
Line varchar2(4000);
UserCode varchar2(4000);
myName varchar2(2000) := '.PLOG';
begin
allLines := dbms_utility.format_call_stack;
cpt := 2;
ptFinLigne := length(allLines);
ptDebLigne := ptFinLigne;
While ptFinLigne > 0 and ptDebLigne > 83 loop
ptDebLigne := INSTR (allLines, endOfLine, -1, cpt) + 1 ;
cpt := cpt + 1;
-- traite ligne
Line := SUBSTR(allLines, ptDebLigne, ptFinLigne - ptDebLigne);
ptDebCode := INSTR (Line, endOfField, -1, 1);
UserCode := SUBSTR(Line, ptDebCode+1);
IF instr(UserCode,myName) = 0 then
IF cpt > 3 then
resultat := resultat||'.';
end IF;
resultat := resultat||UserCode;
end if;
ptFinLigne := ptDebLigne - 1;
end loop;
return resultat;
end calleurname;
--------------------------------------------------------------------
FUNCTION getDefaultContext
-- Cette fonction est priv�, Elle retourne le contexte par default
-- quand il n'est pas pr�ciss�
RETURN LOG_CTX
IS
newCTX LOG_CTX;
lSECTION TLOG.LSECTION%type;
BEGIN
lSECTION := calleurname;
newCTX := init (pSECTION => lSECTION);
RETURN newCTX;
END getDefaultContext;
--------------------------------------------------------------------
PROCEDURE checkAndInitCTX(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
IS
lSECTION TLOG.LSECTION%type;
BEGIN
IF pCTX.isDefaultInit = FALSE THEN
lSECTION := calleurname;
pCTX := init (pSECTION => lSECTION);
END IF;
END;
--------------------------------------------------------------------
procedure addRow
(
pID in TLOG.id%type,
pLDate in TLOG.ldate%type,
pLHSECS in TLOG.lhsecs%type,
pLLEVEL in TLOG.llevel%type,
pLSECTION in TLOG.lsection%type,
pLUSER in TLOG.luser%type,
pLTEXTE in TLOG.ltexte%type
)
is
begin
insert into TLOG
(
ID ,
LDate ,
LHSECS ,
LLEVEL ,
LSECTION ,
LUSER ,
LTEXTE
) VALUES (
pID,
pLDate,
pLHSECS,
pLLEVEL,
pLSECTION,
pLUSER,
pLTEXTE
);
end;
--------------------------------------------------------------------
procedure addRowAutonomous
(
pID in TLOG.id%type,
pLDate in TLOG.ldate%type,
pLHSECS in TLOG.lhsecs%type,
pLLEVEL in TLOG.llevel%type,
pLSECTION in TLOG.lsection%type,
pLUSER in TLOG.luser%type,
pLTEXTE in TLOG.ltexte%type
)
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
addRow
(
pID => pID,
pLDate => pLDate,
pLHSECS => pLHSECS,
pLLEVEL => pLLEVEL,
pLSECTION => pLSECTION,
pLUSER => pLUSER,
pLTEXTE => pLTEXTE
);
commit;
exception when others then
PLOG.ERROR;
rollback;
raise;
end;
--------------------------------------------------------------------
PROCEDURE log
-- procedure priv� pour int�grer les donn�es dans la table
-- RAISE : -20503 'error DBMS_PIPE.send_message.
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pID IN TLOG.ID%type ,
pLDate IN TLOG.LDATE%type ,
pLHSECS IN TLOG.LHSECS%type ,
pLLEVEL IN TLOG.LLEVEL%type ,
pLSECTION IN TLOG.LSECTION%type ,
pLUSER IN TLOG.LUSER%type ,
pLTEXTE IN TLOG.LTEXTE%type
)
IS
ret number;
LLTEXTE TLOG.LTEXTE%type ;
pt number;
BEGIN
IF pCTX.isDefaultInit = FALSE THEN
plog.error('please is necessary to use plog.init for yours contexte.');
END IF;
IF PLTEXTE is null then
LLTEXTE := 'SQLCODE:'||SQLCODE ||' SQLERRM:'||SQLERRM;
ELSE
BEGIN
LLTEXTE := pLTEXTE;
EXCEPTION
WHEN VALUE_ERROR THEN
ASSERT (pCTX, length(pLTEXTE) <= 2000, 'Log Message id:'||pID||' too
long. ');
LLTEXTE := substr(pLTEXTE, 0, 2000);
WHEN OTHERS THEN
PLOG.FATAL;
END;
END IF;
IF pCTX.USE_LOGTABLE = TRUE then
IF pCTX.USE_OUT_TRANS = FALSE then
addRow
(
pID => pID,
pLDate => pLDate,
pLHSECS => pLHSECS,
pLLEVEL => pLLEVEL,
pLSECTION => pLSECTION,
pLUSER => pLUSER,
pLTEXTE => LLTEXTE
);
ELSE
addRowAutonomous
(
pID => pID,
pLDate => pLDate,
pLHSECS => pLHSECS,
pLLEVEL => pLLEVEL,
pLSECTION => pLSECTION,
pLUSER => pLUSER,
pLTEXTE => LLTEXTE
);
END IF;
END IF;
IF pCTX.USE_LOG4J = TRUE then
DBMS_PIPE.pack_message(pID);
DBMS_PIPE.pack_message(TO_CHAR(pLDATE, 'DD month YYYY
HH24:MI:SS')||':'||LTRIM(TO_CHAR(MOD(pLHSECS,100),'09')));
DBMS_PIPE.pack_message(pLLEVEL);
DBMS_PIPE.pack_message(pLSECTION);
DBMS_PIPE.pack_message(LLTEXTE);
DBMS_PIPE.pack_message(pLUSER);
DBMS_PIPE.pack_message('SAVE_IN_LOG');
ret := DBMS_PIPE.send_message(pCTX.DBMS_PIPE_NAME);
IF RET <> 0 then
raise_application_error(ERR_CODE_DBMS_PIPE, MES_CODE_DBMS_PIPE || RET);
END IF;
END IF;
IF pCTX.USE_ALERT = TRUE then
sys.dbms_system.ksdwrt(2,'PLOG:'||TO_CHAR(pLDATE, 'YYYY-MM-DD
HH24:MI:SS')||':'||LTRIM(TO_CHAR(MOD(pLHSECS,100),'09'))||' user: '||PLUSER||' level:
'||getLevelInText(pLLEVEL)||' logid: '||pID ||' '||pLSECTION);
sys.dbms_system.ksdwrt(2,substr(LLTEXTE,0,1000));
if (length(LLTEXTE) >= 1000) then
sys.dbms_system.ksdwrt(2,substr(LLTEXTE,1000));
end if;
END IF;
IF pCTX.USE_TRACE = TRUE then
sys.dbms_system.ksdwrt(1,'PLOG:'||TO_CHAR(pLDATE, 'YYYY-MM-DD
HH24:MI:SS')||':'||LTRIM(TO_CHAR(MOD(pLHSECS,100),'09'))||' user: '||PLUSER||' level:
'||getLevelInText(pLLEVEL)||' logid: '||pID ||' '||pLSECTION);
sys.dbms_system.ksdwrt(1,substr(LLTEXTE,0,1000));
if (length(LLTEXTE) >= 1000) then
sys.dbms_system.ksdwrt(1,substr(LLTEXTE,1000));
end if;
END IF;
IF pCTX.USE_DBMS_OUTPUT = TRUE then
sys.DBMS_OUTPUT.PUT_LINE(TO_CHAR(pLDATE,
'HH24:MI:SS')||':'||LTRIM(TO_CHAR(MOD(pLHSECS,100),'09'))||'-'||getLevelInText||'-'||pLSECTION);
pt := 0;
while pt <= length(LLTEXTE) loop
sys.DBMS_OUTPUT.PUT_LINE(substr(LLTEXTE,pt,255));
pt := pt + 255;
end loop;
END IF;
end log;
-------------------------------------------------------------------
-------------------------------------------------------------------
-- Code public du package
-------------------------------------------------------------------
-------------------------------------------------------------------
--------------------------------------------------------------------
FUNCTION init
-- initialisation du contexte
(
pSECTION IN TLOG.LSECTION%type default NULL , --
log 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 --
)
RETURN LOG_CTX
IS
pCTX LOG_CTX;
BEGIN
pCTX.isDefaultInit := TRUE;
pCTX.LSection := nvl(pSECTION, calleurname);
pCTX.INIT_LSECTION := pSECTION;
pCTX.LLEVEL := pLEVEL;
pCTX.INIT_LLEVEL := pLEVEL;
pCTX.USE_LOG4J := pLOG4J;
pCTX.USE_OUT_TRANS := pOUT_TRANS;
pCTX.USE_LOGTABLE := pLOGTABLE;
pCTX.USE_ALERT := pALERT;
pCTX.USE_TRACE := pTRACE;
pCTX.USE_DBMS_OUTPUT := pDBMS_OUTPUT;
pCTX.DBMS_PIPE_NAME := pDBMS_PIPE_NAME;
return pCTX;
end init;
--------------------------------------------------------------------
PROCEDURE setBeginSection
-- initialisation d'un debut de niveaux hierarchique de log
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pSECTION IN TLOG.LSECTION%type -- Texte du log
) IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.LSection := pCTX.LSection||PLOGPARAM.DEFAULT_Section_sep||pSECTION;
end setBeginSection;
--------------------------------------------------------------------
FUNCTION getSection
-- renvoie la section en cours
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN TLOG.LSECTION%type
IS
BEGIN
return pCTX.LSection;
end getSection;
--------------------------------------------------------------------
FUNCTION getSection
-- renvoie la section en cours
RETURN TLOG.LSECTION%type
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getSection(pCTX =>generiqueCTX) ;
end getSection;
--------------------------------------------------------------------
PROCEDURE setEndSection
-- fin d'un niveau hierarchique de log et dee tout c'est sup�rieur
-- par default [/]
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pSECTION IN TLOG.LSECTION%type default 'EndAllSection' -- Texte du log
) IS
BEGIN
checkAndInitCTX(pCTX);
if pSECTION = 'EndAllSection' THEN
pCTX.LSection := nvl(pCTX.INIT_LSECTION, calleurname);
RETURN;
END IF;
pCTX.LSection := substr(pCTX.LSection,1,instr(UPPER(pCTX.LSection),
UPPER(pSECTION), -1)-2);
end setEndSection;
-------------------------------------------------------------------
PROCEDURE setTransactionMode
-- utlisation des log dans ou en dehors des transactions
-- TRUE => Les log sont dans la transaction
-- FALSE => les log sont en dehors de la transaction
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
inTransaction IN boolean default TRUE -- TRUE => Les log
sont dans la transaction,
-- FALSE => les log
sont en dehors de la transaction
)
IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.USE_OUT_TRANS := inTransaction;
end setTransactionMode;
-------------------------------------------------------------------
FUNCTION getTransactionMode
-- TRUE => Les log sont dans la transaction
-- FALSE => les log sont en dehors de la transaction
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN boolean
IS
BEGIN
return pCTX.USE_OUT_TRANS;
end getTransactionMode;
-------------------------------------------------------------------
FUNCTION getTransactionMode
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getTransactionMode(pCTX => generiqueCTX);
end getTransactionMode;
-------------------------------------------------------------------
PROCEDURE setUSE_LOG4JMode
--TRUE => Log is send to USE_LOG4J
--FALSE => Log is not send to USE_LOG4J
(
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
)
IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.USE_LOG4J := inUSE_LOG4J;
end setUSE_LOG4JMode;
-------------------------------------------------------------------
FUNCTION getUSE_LOG4JMode
--TRUE => Log is send to USE_LOG4J
--FALSE => Log is not send to USE_LOG4J
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN boolean
IS
BEGIN
return pCTX.USE_LOG4J;
end getUSE_LOG4JMode;
-------------------------------------------------------------------
FUNCTION getUSE_LOG4JMode
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getTransactionMode(pCTX => generiqueCTX);
end getUSE_LOG4JMode;
-------------------------------------------------------------------
PROCEDURE setLOG_TABLEMode
--TRUE => Log is send to LOG_TABLEMODE
--FALSE => Log is not send to LOG_TABLEMODE
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
inLOG_TABLE IN boolean default TRUE -- TRUE => Log is send
to LOG_TABLEMODE,
-- FALSE => Log is not
send to LOG_TABLEMODE
)
IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.USE_LOGTABLE := inLOG_TABLE;
end setLOG_TABLEMode;
-------------------------------------------------------------------
FUNCTION getLOG_TABLEMode
--TRUE => Log is send to LOG_TABLEMODE
--FALSE => Log is not send to LOG_TABLEMODE
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN boolean
IS
BEGIN
return pCTX.USE_LOGTABLE;
end getLOG_TABLEMode;
-------------------------------------------------------------------
FUNCTION getLOG_TABLEMode
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getTransactionMode(pCTX => generiqueCTX);
end getLOG_TABLEMode;
-------------------------------------------------------------------
PROCEDURE setLOG_ALERTMode
--TRUE => Log is send to LOG_ALERT
--FALSE => Log is not send to LOG_ALERT
(
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
)
IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.USE_ALERT := inLOG_ALERT;
end setLOG_ALERTMode;
-------------------------------------------------------------------
FUNCTION getLOG_ALERTMode
--TRUE => Log is send to LOG_ALERT
--FALSE => Log is not send to LOG_ALERT
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN boolean
IS
BEGIN
return pCTX.USE_ALERT;
end getLOG_ALERTMode;
-------------------------------------------------------------------
FUNCTION getLOG_ALERTMode
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getTransactionMode(pCTX => generiqueCTX);
end getLOG_ALERTMode;
-------------------------------------------------------------------
PROCEDURE setLOG_TRACEMode
--TRUE => Log is send to LOG_TRACE
--FALSE => Log is not send to LOG_TRACE
(
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
)
IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.USE_TRACE := inLOG_TRACE;
end setLOG_TRACEMode;
-------------------------------------------------------------------
FUNCTION getLOG_TRACEMode
--TRUE => Log is send to LOG_TRACE
--FALSE => Log is not send to LOG_TRACE
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN boolean
IS
BEGIN
return pCTX.USE_TRACE;
end getLOG_TRACEMode;
-------------------------------------------------------------------
FUNCTION getLOG_TRACEMode
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getTransactionMode(pCTX => generiqueCTX);
end getLOG_TRACEMode;
-------------------------------------------------------------------
PROCEDURE setDBMS_OUTPUTMode
--TRUE => Log is send to DBMS_OUTPUT
--FALSE => Log is not send to DBMS_OUTPUT
(
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
)
IS
BEGIN
checkAndInitCTX(pCTX);
pCTX.USE_DBMS_OUTPUT := inDBMS_OUTPUT;
end setDBMS_OUTPUTMode;
-------------------------------------------------------------------
FUNCTION getDBMS_OUTPUTMode
--TRUE => Log is send to DBMS_OUTPUT
--FALSE => Log is not send to DBMS_OUTPUT
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN boolean
IS
BEGIN
checkAndInitCTX(pCTX);
return pCTX.USE_DBMS_OUTPUT;
end getDBMS_OUTPUTMode;
-------------------------------------------------------------------
FUNCTION getDBMS_OUTPUTMode
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getTransactionMode(pCTX => generiqueCTX);
end getDBMS_OUTPUTMode;
-------------------------------------------------------------------
PROCEDURE setLevel
-- il est possible de modifier avec setLevell dynamiquement le niveau de log
-- l'appel de setLevel sans paramettre re-poossitionne le niveaux a celuis specifier
-- dans le package.
-- erreur possible : -20501, 'Set Level not in LOG predefine constantes'
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pLEVEL IN TLOG.LLEVEL%type default NOLEVEL -- Level sup�rieur
attribuer dynamiquement
) IS
nbrl number;
BEGIN
checkAndInitCTX(pCTX);
IF pLEVEL = NOLEVEL then
pCTX.LLEVEL := pCTX.INIT_LLEVEL;
END IF;
select count(*) into nbrl FROM TLOGLEVEL where TLOGLEVEL.LLEVEL=pLEVEL;
IF nbrl > 0 then
pCTX.LLEVEL := pLEVEL;
ELSE
raise_application_error(-20501, 'SetLevel ('||pLEVEL||') not in TLOGLEVEL
table');
END IF;
EXCEPTION
WHEN OTHERS THEN
PLOG.ERROR;
end setLevel;
PROCEDURE setLevel
-- il est possible de modifier avec setLevell dynamiquement le niveau de log
-- l'appel de setLevel sans paramettre re-poossitionne le niveaux a celuis specifier
-- dans le package.
-- erreur possible : -20501, 'Set Level not in LOG predefine constantes'
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pLEVEL IN TLOGLEVEL.LCODE%type -- Level sup�rieur
attribuer dynamiquement
) IS
nbrl number;
BEGIN
setLevel (pCTX, getTextInLevel(pLEVEL));
end setLevel;
-------------------------------------------------------------------
FUNCTION getLevel
-- Retourne le level courant
(
pCTX IN LOG_CTX -- Context
)
RETURN TLOG.LLEVEL%type
IS
BEGIN
return pCTX.LLEVEL;
end getLevel;
FUNCTION getLevel
RETURN TLOG.LLEVEL%type
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getLevel( pCTX => generiqueCTX);
end getLevel;
-------------------------------------------------------------------------
FUNCTION islevelEnabled
-- fonction outil appeler par les is[Debug|Info|Warn|Error]Enabled
(
pCTX IN LOG_CTX, -- Context
pLEVEL IN TLOG.LLEVEL%type -- Level a tester
)
RETURN boolean
IS
BEGIN
if getLevel(pCTX) >= pLEVEL then
return TRUE;
else
return FALSE;
end if;
end islevelEnabled;
FUNCTION islevelEnabled
(
pLEVEL IN TLOG.LLEVEL%type -- Level a tester
)
RETURN boolean
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return islevelEnabled( pCTX => generiqueCTX, pLEVEL => pLEVEL);
end islevelEnabled;
-------------------------------------------------------------------
FUNCTION isFatalEnabled RETURN boolean is begin return
islevelEnabled(getTextInLevel('FATAL')); end;
FUNCTION isErrorEnabled RETURN boolean is begin return
islevelEnabled(getTextInLevel('ERROR')); end;
FUNCTION isWarnEnabled RETURN boolean is begin return
islevelEnabled(getTextInLevel('WARN')) ; end;
FUNCTION isInfoEnabled RETURN boolean is begin return
islevelEnabled(getTextInLevel('INFO')) ; end;
FUNCTION isDebugEnabled RETURN boolean is begin return
islevelEnabled(getTextInLevel('DEBUG')); end;
FUNCTION isFatalEnabled ( pCTX IN LOG_CTX ) RETURN boolean is begin return
islevelEnabled(pCTX, getTextInLevel('FATAL')); end;
FUNCTION isErrorEnabled ( pCTX IN LOG_CTX ) RETURN boolean is begin return
islevelEnabled(pCTX, getTextInLevel('ERROR')); end;
FUNCTION isWarnEnabled ( pCTX IN LOG_CTX ) RETURN boolean is begin return
islevelEnabled(pCTX, getTextInLevel('WARN')) ; end;
FUNCTION isInfoEnabled ( pCTX IN LOG_CTX ) RETURN boolean is begin return
islevelEnabled(pCTX, getTextInLevel('INFO')) ; end;
FUNCTION isDebugEnabled ( pCTX IN LOG_CTX ) RETURN boolean is begin return
islevelEnabled(pCTX, getTextInLevel('DEBUG')); end;
--------------------------------------------------------------------
PROCEDURE purge
-- Purge de la log
IS
tempLogCtx PLOG.LOG_CTX;
BEGIN
purge(tempLogCtx);
end purge;
--------------------------------------------------------------------
PROCEDURE purge
-- Purge de la log
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
) IS
BEGIN
checkAndInitCTX(pCTX);
execute immediate ('truncate table tlog');
purge(pCTX, sysdate+1);
end purge;
--------------------------------------------------------------------
PROCEDURE purge
-- Purge de la log avec date max
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
DateMax IN Date -- Tout les
enregistrements supperieur a
-- la date sont purg�
) IS
tempLogCtx PLOG.LOG_CTX := PLOG.init(pSECTION => 'plog.purge', pLEVEL =>
PLOG.LINFO);
BEGIN
checkAndInitCTX(pCTX);
delete from tlog where ldate < DateMax;
INFO(tempLogCtx, 'Purge by user:'||USER);
end purge;
--------------------------------------------------------------------
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
) IS
lId TLOG.ID%type ;
lLSECTION TLOG.LSECTION%type := getSection(pCTX);
lLHSECS TLOG.LHSECS%type ;
m varchar2(100);
BEGIN
checkAndInitCTX(pCTX);
IF pLEVEL > getLevel(pCTX) THEN
RETURN;
END IF;
lId := getNextID(pCTX);
select HSECS into lLHSECS from V$TIMER;
log ( pCTX =>pCTX,
pID =>lId,
pLDate =>sysdate,
pLHSECS =>lLHSECS,
pLLEVEL =>pLEVEL,
pLSECTION =>lLSECTION,
pLUSER =>user,
pLTEXTE =>pTEXTE
);
end log;
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
) IS
BEGIN
LOG(pLEVEL => getTextInLevel(pLEVEL), pCTX => pCTX, pTEXTE => pTEXTE);
end log;
PROCEDURE log
(
pLEVEL IN TLOG.LLEVEL%type , -- log level
pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text
) IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
LOG(pLEVEL => pLEVEL, pCTX => generiqueCTX, pTEXTE => pTEXTE);
end log;
PROCEDURE log
(
pLEVEL IN TLOGLEVEL.LCODE%type , -- log level
pTEXTE IN TLOG.LTEXTE%type default DEFAULTEXTMESS -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel(pLEVEL), pTEXTE => pTEXTE);
end log;
--------------------------------------------------------------------
PROCEDURE debug
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('DEBUG'), pCTX => pCTX, pTEXTE => pTEXTE);
end debug;
PROCEDURE debug
(
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('DEBUG'), pTEXTE => pTEXTE);
end debug;
--------------------------------------------------------------------
PROCEDURE info
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('INFO'), pCTX => pCTX, pTEXTE => pTEXTE);
end info;
PROCEDURE info
(
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('INFO'), pTEXTE => pTEXTE);
end info;
--------------------------------------------------------------------
PROCEDURE warn
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('WARN'), pCTX => pCTX, pTEXTE => pTEXTE);
end warn;
PROCEDURE warn
(
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('WARN'), pTEXTE => pTEXTE);
end warn;
--------------------------------------------------------------------
PROCEDURE error
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('ERROR'), pCTX => pCTX, pTEXTE => pTEXTE);
end error;
PROCEDURE error
(
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('ERROR'), pTEXTE => pTEXTE);
end error;
--------------------------------------------------------------------
PROCEDURE fatal
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('FATAL'), pCTX => pCTX, pTEXTE => pTEXTE);
end fatal;
PROCEDURE fatal
(
pTEXTE IN TLOG.LTEXTE%type default null -- log text
) IS
BEGIN
LOG(pLEVEL => getTextInLevel('FATAL'), pTEXTE => pTEXTE);
end fatal;
--------------------------------------------------------------------
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
)
IS
BEGIN
checkAndInitCTX(pCTX);
IF not islevelEnabled(pCTX, PLOGPARAM.DEFAULT_ASSET_LEVEL) then
RETURN;
END IF;
IF NOT pCONDITION THEN
LOG (pLEVEL => PLOGPARAM.DEFAULT_ASSET_LEVEL, pCTX => pCTX, pTEXTE =>
'AAS'||pLogErrorCodeIfFALSE||': '||pLogErrorMessageIfFALSE);
IF pRaiseExceptionIfFALSE THEN
raise_application_error(pLogErrorCodeIfFALSE, pLogErrorMessageIfFALSE,
pLogErrorReplaceError);
END IF;
END IF;
END assert;
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
)
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
assert (
pCTX => generiqueCTX,
pCONDITION => pCONDITION,
pLogErrorCodeIfFALSE => pLogErrorCodeIfFALSE,
pLogErrorMessageIfFALSE => pLogErrorMessageIfFALSE,
pRaiseExceptionIfFALSE => pRaiseExceptionIfFALSE,
pLogErrorReplaceError => pLogErrorReplaceError );
END assert ;
--------------------------------------------------------------------
PROCEDURE full_call_stack
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
full_call_stack (Pctx => generiqueCTX);
END full_call_stack;
PROCEDURE full_call_stack (
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
IS
BEGIN
checkAndInitCTX(pCTX);
LOG (pLEVEL => PLOGPARAM.DEFAULT_FULL_CALL_STACK_LEVEL, pCTX => pCTX, pTEXTE =>
dbms_utility.format_call_stack );
END full_call_stack;
--------------------------------------------------------------------
FUNCTION getLOG4PLSQVersion return varchar2
IS
begin
return LOG4PLSQL_VERSION;
end getLOG4PLSQVersion;
--------------------------------------------------------------------
FUNCTION getLevelInText (
pLevel TLOG.LLEVEL%type default PLOGPARAM.DEFAULT_LEVEL
) return varchar2
IS
ret varchar2(1000);
BEGIN
SELECT LCODE into ret
FROM TLOGLEVEL
WHERE LLEVEL = pLevel;
RETURN ret;
EXCEPTION
WHEN OTHERS THEN
return 'UNDEFINED';
END getLevelInText;
--------------------------------------------------------------------
FUNCTION getTextInLevel (
pCode TLOGLEVEL.LCODE%type
) return TLOG.LLEVEL%type
IS
ret TLOG.LLEVEL%type ;
BEGIN
SELECT LLEVEL into ret
FROM TLOGLEVEL
WHERE LCODE = pCode;
RETURN ret;
EXCEPTION
WHEN OTHERS THEN
return PLOGPARAM.DEFAULT_LEVEL;
END getTextInLevel;
FUNCTION getDBMS_PIPE_NAME
(
pCTX IN OUT NOCOPY LOG_CTX -- Context
)
RETURN varchar2
IS
BEGIN
return pCTX.DBMS_PIPE_NAME;
END getDBMS_PIPE_NAME;
FUNCTION getDBMS_PIPE_NAME
RETURN varchar2
IS
generiqueCTX PLOG.LOG_CTX := PLOG.getDefaultContext;
BEGIN
return getDBMS_PIPE_NAME( pCTX => generiqueCTX);
end getDBMS_PIPE_NAME;
PROCEDURE setDBMS_PIPE_NAME
(
pCTX IN OUT NOCOPY LOG_CTX , -- Context
inDBMS_PIPE_NAME IN VARCHAR2
)
IS
BEGIN
pCTX.DBMS_PIPE_NAME := inDBMS_PIPE_NAME;
END setDBMS_PIPE_NAME;
--------------------------------------------------------------------
--------------------------------------------------------------------
END PLOG;
/
sho error
-------------------------------------------------------------------
-- End of document
-------------------------------------------------------------------
create or replace function fct_test_log return number
is
pCTX PLOG.LOG_CTX := PLOG.init(null, PLOG.LDEBUG, TRUE, TRUE);
begin
PLOG.debug (pCTX, 'message for debug');
PLOG.info (pCTX, 'message for information');
PLOG.warn (pCTX, 'message for warning ');
PLOG.error (pCTX, 'message for error');
PLOG.fatal (pCTX, 'message for fatal');
return 0;
end fct_test_log;
/
sho error
create or replace procedure proc_test_log
is
pCTX PLOG.LOG_CTX := PLOG.init(null, PLOG.LDEBUG, TRUE, TRUE);
temp number;
begin
temp := fct_test_log;
PLOG.debug (pCTX, 'message for debug');
PLOG.info (pCTX, 'message for information');
PLOG.warn (pCTX, 'message for warning ');
PLOG.error (pCTX, 'message for error');
PLOG.fatal (pCTX, 'message for fatal');
end proc_test_log;
/
sho error
create or replace procedure proc_test_log2 (pCTX in out PLOG.LOG_CTX)
is
begin
PLOG.error (pCTX, 'in sub section');
end proc_test_log2;
/
sho error
declare
pCTX PLOG.LOG_CTX := PLOG.init(null, PLOG.LDEBUG, TRUE, TRUE);
begin
PLOG.SetBeginSection (pCTX, 'codePart1');
PLOG.debug (pCTX, 'message for debug');
PLOG.info (pCTX, 'message for information');
PLOG.warn (pCTX, 'message for warning ');
PLOG.error (pCTX, 'message for error');
PLOG.fatal (pCTX, 'message for fatal');
PLOG.SetBeginSection (pCTX, 'codePart2');
PLOG.info (pCTX, 'information message');
PLOG.SetEndSection (pCTX);
proc_test_log;
PLOG.SetBeginSection (pCTX, 'call->proc_test_log');
proc_test_log2 (pCTX);
PLOG.SetEndSection (pCTX);
End;
/
/* No result un SQLPUS.
but in backgroundProcess output
E:\GGM\perso\log4plsql\Log4plsql\cmd>setlocal
E:\GGM\perso\log4plsql\Log4plsql\cmd>call setVariable.bat
variable setup
2003-06-07 01:39:27,878 INFO (backgroundProcess) Start
[ ][main][ Run.java:59]
2003-06-07 01:39:27,888 DEBUG (backgroundProcess) log4plsql.properties :
.\properties\log4plsql.xml [ ][main][ Run.java:60]
2003-06-07 01:39:27,888 DEBUG (backgroundProcess) log4j.properties :
.\\properties\\log4j.properties [ ][main][
Run.java:61]
2003-06-07 01:39:27,898 DEBUG (ReaderDBThread) ReaderLogDataBase : Connect
[ ][main][ReaderThread.java:38]
2003-06-07 01:39:29,481 DEBUG (ReaderDBThread) begin loop
[ ][Thread-0][ReaderThread.java:52]
2003-06-07 01:39:38,784 DEBUG (log4plsql.SCOTT.block.codePart1) message for debug
[DatabaseLoginDate:07 juin 2003 01:39:38: 30][Thread-0][
?:?]
2003-06-07 01:39:38,834 INFO (log4plsql.SCOTT.block.codePart1) message for
information [DatabaseLoginDate:07 juin 2003
01:39:38: 30][Thread-0][ ?:?]
2003-06-07 01:39:38,874 WARN (log4plsql.SCOTT.block.codePart1) message for warning
[DatabaseLoginDate:07 juin 2003 01:39:38:
31][Thread-0][ ?:?]
2003-06-07 01:39:38,924 ERROR (log4plsql.SCOTT.block.codePart1) message for error
[DatabaseLoginDate:07 juin 2003 01:39:38: 31][Thread-0][
?:?]
2003-06-07 01:39:38,954 FATAL (log4plsql.SCOTT.block.codePart1) message for fatal
[DatabaseLoginDate:07 juin 2003 01:39:38: 31][Thread-0][
?:?]
2003-06-07 01:39:39,004 INFO (log4plsql.SCOTT.block.codePart1.codePart2) information
message [DatabaseLoginDate:07 juin 2003 01:39:38:
31][Thread-0][ ?:?]
2003-06-07 01:39:39,034 DEBUG
(log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG.SCOTT.FCT_TEST_LOG) message for debug
[DatabaseLoginDate:07 juin 2003 01:39:38: 31][Thread-0][
?:?]
2003-06-07 01:39:39,074 INFO
(log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG.SCOTT.FCT_TEST_LOG) message for information
[DatabaseLoginDate:07 juin 2003 01:39:38:
31][Thread-0][ ?:?]
2003-06-07 01:39:39,104 WARN
(log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG.SCOTT.FCT_TEST_LOG) message for warning
[DatabaseLoginDate:07 juin 2003 01:39:38:
31][Thread-0][ ?:?]
2003-06-07 01:39:39,135 ERROR
(log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG.SCOTT.FCT_TEST_LOG) message for error
[DatabaseLoginDate:07 juin 2003 01:39:38: 31][Thread-0][
?:?]
2003-06-07 01:39:39,175 FATAL
(log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG.SCOTT.FCT_TEST_LOG) message for fatal
[DatabaseLoginDate:07 juin 2003 01:39:38: 32][Thread-0][
?:?]
2003-06-07 01:39:39,315 DEBUG (log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG) message for
debug [DatabaseLoginDate:07 juin 2003 01:39:38:
32][Thread-0][ ?:?]
2003-06-07 01:39:39,345 INFO (log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG) message for
information [DatabaseLoginDate:07 juin 2003
01:39:38: 32][Thread-0][ ?:?]
2003-06-07 01:39:39,385 WARN (log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG) message for
warning [DatabaseLoginDate:07 juin 2003 01:39:38:
32][Thread-0][ ?:?]
2003-06-07 01:39:39,425 ERROR (log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG) message for
error [DatabaseLoginDate:07 juin 2003 01:39:38:
32][Thread-0][ ?:?]
2003-06-07 01:39:39,455 FATAL (log4plsql.SCOTT.block.SCOTT.PROC_TEST_LOG) message for
fatal [DatabaseLoginDate:07 juin 2003 01:39:38:
32][Thread-0][ ?:?]
2003-06-07 01:39:39,495 ERROR (log4plsql.SCOTT.block.call->proc_test_log) in sub
section [DatabaseLoginDate:07 juin 2003 01:39:38:
32][Thread-0][ ?:?]
*/
create or replace function funcTSec return boolean is
begin
PLOG.error ('error in funcTSec');
return true;
end;
/
create or replace package ptSec is
procedure ptSecPrc;
end;
/
create or replace package body ptSec is
procedure ptSecPrc is
r boolean;
begin
PLOG.error ('error in ptSec');
r := funcTSec;
end;
end;
/
Exec PLOG.purge;
Exec ptSec.ptSecPrc;
select LSECTION||'->'||LTEXTE from tlog;
SQL> select LSECTION||'->'||LTEXTE from tlog;
LSECTION||'->'||LTEXTE
--------------------------------------------------------------------------------
plog.purge->Purge By SCOTT
block.SCOTT.PTSEC->error in ptSec
block.SCOTT.PTSEC.SCOTT.FUNCTSEC->error in funcTSec
create or replace procedure procTSec (cpt number default 0) is
pCTX PLOG.LOG_CTX := PLOG.init(pLEVEL => PLOG.LDEBUG);
begin
PLOG.INFO (pCTX, cpt);
if cpt < 100 then
procTSec ( cpt + 1 );
end if;
end;
/
Exec PLOG.purge;
Exec procTSec;
set linesize 4000
set pagesize 1000
select LTEXTE||'-'||length(LSECTION)||'-'||LSECTION from tlog;
/*
SQL> select LTEXTE||'-'||length(LSECTION)||'-'||LSECTION from tlog;
LTEXTE||'-'||LENGTH(LSECTION)||'-'||LSECTION
Purge by user:SCOTT-10-plog.purge
0-20-block.SCOTT.PROCTSEC
1-35-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC
2-50-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC
3-65-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC
4-80-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC
5-95-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC
LTEXTE||'-'||LENGTH(LSECTION)||'-'||LSECTION
6-110-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC
7-125-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.S
8-140-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.S
9-155-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.S
10-170-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
11-185-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
12-200-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
LTEXTE||'-'||LENGTH(LSECTION)||'-'||LSECTION
13-215-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
14-230-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
15-245-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
16-260-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
17-275-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
18-290-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
19-305-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
LTEXTE||'-'||LENGTH(LSECTION)||'-'||LSECTION
20-320-block.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.SCOTT.PROCTSEC.
*/
create or replace procedure pts009 is begin PLOG.ERROR ('009'); pts010 ; end;
/
create or replace procedure pts008 is begin PLOG.ERROR ('008'); pts009 ; end;
/
create or replace procedure pts007 is begin PLOG.ERROR ('007'); pts008 ; end;
/
create or replace procedure pts006 is begin PLOG.ERROR ('006'); pts007 ; end;
/
create or replace procedure pts005 is begin PLOG.ERROR ('005'); pts006 ; end;
/
create or replace procedure pts004 is begin PLOG.ERROR ('004'); pts005 ; end;
/
create or replace procedure pts003 is begin PLOG.ERROR ('003'); pts004 ; end;
/
create or replace procedure pts002 is begin PLOG.ERROR ('002'); pts003 ; end;
/
create or replace procedure pts001 is begin PLOG.ERROR ('001'); pts002 ; end;
/
SET SERVEROUTPUT ON SIZE 1000000
set linesize 2000
create or replace procedure pts010
is
ms varchar2(2000);
pCTX PLOG.LOG_CTX := PLOG.INIT ;
begin
PLOG.full_call_stack;
end;
/
Exec PLOG.purge;
Exec pts001 ;
set linesize 4000
select ltexte from tlog;
select lsection from tlog;
/*
SQL>
SQL> create or replace procedure pts009 is begin PLOG.ERROR ('009'); pts010 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts008 is begin PLOG.ERROR ('008'); pts009 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts007 is begin PLOG.ERROR ('007'); pts008 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts006 is begin PLOG.ERROR ('006'); pts007 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts005 is begin PLOG.ERROR ('005'); pts006 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts004 is begin PLOG.ERROR ('004'); pts005 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts003 is begin PLOG.ERROR ('003'); pts004 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts002 is begin PLOG.ERROR ('002'); pts003 ; end;
2 /
Proc�dure cr��e.
SQL> create or replace procedure pts001 is begin PLOG.ERROR ('001'); pts002 ; end;
2 /
Proc�dure cr��e.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> set linesize 2000
SQL> create or replace procedure pts010
2 is
3 ms varchar2(2000);
4 pCTX PLOG.LOG_CTX := PLOG.INIT ;
5 begin
6 PLOG.full_call_stack;
7 end;
8 /
Proc�dure cr��e.
SQL>
SQL>
SQL> Exec PLOG.purge;
Proc�dure PL/SQL termin�e avec succ�s.
SQL> Exec pts001 ;
Proc�dure PL/SQL termin�e avec succ�s.
SQL>
SQL> set linesize 4000
SQL> select ltexte from tlog;
LTEXTE
Purge By SCOTT
001
002
003
004
005
006
007
008
009
----- PL/SQL Call Stack -----
LTEXTE
object line object
handle number name
7AC91128 962 package body ULOG.PLOG
7AC91128 952 package body ULOG.PLOG
7AB8EAB4 6 procedure SCOTT.PTS010
7AA1F490 1 procedure SCOTT.PTS009
7ABE59F8 1 procedure SCOTT.PTS008
7A7F4AD8 1 procedure SCOTT.PTS007
7AA1D214 1 procedure SCOTT.PTS006
7AA37674 1 procedure SCOTT.PTS005
7A7F1858 1 procedure SCOTT.PTS004
LTEXTE
7A7D5570 1 procedure SCOTT.PTS003
7A7D3918 1 procedure SCOTT.PTS002
7A7CEB80 1 procedure SCOTT.PTS001
7A7C4AE0 1 anonymous block
11 ligne(s) s�lectionn�e(s).
SQL> select lsection from tlog;
LSECTION
plog.purge
block.SCOTT.PTS001
block.SCOTT.PTS001.SCOTT.PTS002
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004.SCOTT.PTS005
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004.SCOTT.PTS005.SCOTT.PTS006
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004.SCOTT.PTS005.SCOTT.PTS006.SCOTT.PTS007
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004.SCOTT.PTS005.SCOTT.PTS006.SCOTT.PTS007.SCOTT.PTS008
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004.SCOTT.PTS005.SCOTT.PTS006.SCOTT.PTS007.SCOTT.PTS008.SCOTT.PTS009
block.SCOTT.PTS001.SCOTT.PTS002.SCOTT.PTS003.SCOTT.PTS004.SCOTT.PTS005.SCOTT.PTS006.SCOTT.PTS007.SCOTT.PTS008.SCOTT.PTS009.SCOTT.PTS010
11 ligne(s) s�lectionn�e(s).
SQL>
SQL>
*/
Create or replace package insert_error as
procedure insert_error;
end insert_error;
/
Create or replace package outer_insert_error as
procedure outer_insert_error;
end outer_insert_error;
/
create or replace package body insert_error as
procedure insert_error is
begin
plog.fatal(dbms_utility.format_call_stack);
end insert_error;
end insert_error;
/
create or replace package body outer_insert_error as
procedure outer_insert_error is
begin
insert_error.insert_error();
end outer_insert_error;
end outer_insert_error;
/
Exec PLOG.purge
/
SET SERVEROUTPUT ON
exec outer_insert_error.outer_insert_error();
Select * from vlog
/
set linesize 2000
select LSECTION from tlog;
/*
SQL> Create or replace package insert_error as
2 procedure insert_error;
3 end insert_error;
4 /
Package cr��.
SQL>
SQL> Create or replace package outer_insert_error as
2 procedure outer_insert_error;
3 end outer_insert_error;
4 /
Package cr��.
SQL>
SQL> create or replace package body insert_error as
2 procedure insert_error is
3 begin
4 plog.fatal(dbms_utility.format_call_stack);
5 end insert_error;
6 end insert_error;
7 /
Corps de package cr��.
SQL>
SQL> create or replace package body outer_insert_error as
2 procedure outer_insert_error is
3 begin
4 insert_error.insert_error();
5 end outer_insert_error;
6 end outer_insert_error;
7 /
Corps de package cr��.
SQL>
SQL> Exec PLOG.purge
Proc�dure PL/SQL termin�e avec succ�s.
SQL> /
Corps de package cr��.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> exec outer_insert_error.outer_insert_error();
Proc�dure PL/SQL termin�e avec succ�s.
SQL>
SQL>
SQL>
SQL> Select * from vlog
2 /
LOG
--------------------------------------------------------------------------------
[Fev 27, 20:35:09:87][INFO][SCOTT][plog.purge][Purge by user:SCOTT]
[Fev 27, 20:35:09:21][FATAL][SCOTT][block.SCOTT.OUTER_INSERT_ERROR.SCOTT.INSERT_
ERROR][----- PL/SQL Call Stack -----
object line object
handle number name
668896D4 4 package body SCOTT.INSERT_ERROR
66882068 4 package body SCOTT.OUTER_INSERT_ERROR
6688580C 1 anonymous block
]
SQL>
SQL>
SQL> set linesize 2000
SQL> select LSECTION from tlog;
LSECTION
plog.purge
block.SCOTT.OUTER_INSERT_ERROR.SCOTT.INSERT_ERROR
SQL>
*/
