PL/Jumpstart Standalone Log
===========================
This logger Uses (mostly) the same code as the main PL/Jumpstart logger.
Please see this [PL/Jumpstart blog entry](http://blog.pljumpstart.com/logging) for a quick overview.
and [PL/Jumpstart](http://www.pljumpstart.com) for information about the whole framework.
I hope that this utility improves the quality of your delivered PL/SQL code.
I welcome all feedback. (pjoyce[at]whatsthis.ie)
To install, simply unzip the files to a folder, open a SQL*Plus window from that location and type:
SQL> @install
SQL> @testInstall
(Sourceforge - I thought this was supposed to be markdown!)
Note on usage
-------------
Please see the documentation. However the most important concept is to realise that the logger
'remembers' where it is (package, procedure, function etc) by maintaining a call stack. The
procedures PUSH and POP maintain this stack. Thus, it you wish to use PLJ_LG in PL/SQL code
ALL entry point must contain a call to PUSH and ALL exit points a call to POP.
PUSH takes two arguments - the Module NAME (eg Package Name), and the Unit Name (eg Procedure or Function).
Thus:
PLJ_LG.PUSH('CUSTOMER','ADD);
POP takes none, thus simply,
PLJ_LG.POP;
Once you use it a little, this will become clear.
I have included an example at the bottom of this file.
Finally, if you wish to refactor code, and insert calls to the logger, I have
created utilities to do this. Please see [PL/Jumpstart](http://www.pljumpstart.com)
Thanks and good luck!
Paul
An example of instrumented code:
CREATE OR REPLACE PACKAGE BODY PLJ_NOTE AS
-- {{{ GET_VERSION
FUNCTION GET_VERSION
RETURN varchar2 IS
BEGIN
return C_VERSION;
END GET_VERSION;
-- }}}
/* {{{ ADD */
PROCEDURE ADD (p_note_of_code IN PLJ_NOTE_ASSGTS.NOTE_OF_CODE%TYPE,
p_note_of_id IN PLJ_NOTE_ASSGTS.NOTE_OF_ID%TYPE,
p_note_type IN PLJ_NOTES.NOTE_TYPE_CODE%TYPE,
p_note_text IN PLJ_NOTES.NOTE_TEXT%TYPE,
p_note_id OUT PLJ_NOTES.NOTE_ID%TYPE) IS
l_note_id PLJ_NOTES.NOTE_ID%TYPE;
l_note_text PLJ_NOTES.NOTE_TEXT%TYPE;
BEGIN
PLJ_LG.PUSH (PLJ_CONST.C_PLJ_MOD_NOTE,'ADD');
PLJ_CODE.VALIDATE (p_note_type, 'NOTES', 'NOTE_TYPE_CODE');
PLJ_CODE.VALIDATE (p_note_of_code, 'NOTE_ASSGTS', 'NOTE_OF_CODE');
PLJ_LG.D('Creating Note of Type: '||p_note_type);
PLJ_LG.D('Creating Note of Code: '||p_note_of_code);
if p_note_text IS NULL then
PLJ_ERR.RAISE(PLJ_ERR_MSG.C_NOTE_EMPTY);
end if;
if length ( p_note_text) > C_NOTE_MAX_LENGTH then
PLJ_LG.W('Note greater than C_NOTE_MAX_LENGTH characters. Truncating!');
l_note_text := substr(p_note_text,1,C_NOTE_MAX_LENGTH); --To avoid compiler warning PLW-07202
else
l_note_text := p_note_text;
end if;
-- if p_note_of_code = PLJ_CONST.C_NOTE_OF_PERS then
-- if NOT PERSON.IS_VALID (p_note_of_id) then
-- PLJ_ERR.RAISE(PLJ_ERR_MSG.C_NOTE_PERSON_WRONG_ASSIGN);
-- end if;
-- end if;
PLJ_LG.D('Insert into NOTES');
insert into PLJ_NOTES (NOTE_TYPE_CODE,
NOTE_TEXT,
CREATED_BY,
CREATED_DATE)
values (p_note_type,
l_note_text,
PLJ_AUTH.GET_CURRENT_USER,
sysdate) returning NOTE_ID into l_note_id;
p_note_id := l_note_id;
PLJ_LG.D('Creating Note Assignment using Note ID: '||l_note_id);
PLJ_LG.D('Insert into NOTE_ASSGTS');
insert into PLJ_NOTE_ASSGTS (NOTE_ID,
NOTE_OF_CODE,
NOTE_OF_ID,
CREATED_BY,
CREATED_DATE)
values (l_note_id,
p_note_of_code,
p_note_of_id,
PLJ_AUTH.GET_CURRENT_USER,
SYSDATE) returning NOTE_ID into l_note_id;
PLJ_LG.POP;
EXCEPTION
WHEN OTHERS THEN
PLJ_ERR.HANDLE();
END ADD;
/* }}} */
/* {{{ UPD */
PROCEDURE UPD (p_note_id IN PLJ_NOTES.NOTE_ID%TYPE,
p_note_text IN PLJ_NOTES.NOTE_TEXT%TYPE) IS
l_note_text PLJ_NOTES.NOTE_TEXT%TYPE;
BEGIN
PLJ_LG.PUSH (PLJ_CONST.C_PLJ_MOD_NOTE,'UPD');
l_note_text := p_note_text;
if length ( p_note_text) > C_NOTE_MAX_LENGTH then
PLJ_LG.W('Note greater than C_NOTE_MAX_LENGTH characters. Truncating!');
l_note_text := substr(l_note_text,1,C_NOTE_MAX_LENGTH); --To avoid compiler warning PLW-07202
end if;
PLJ_LG.PUSH(PLJ_CONST.C_PLJ_MOD_NOTE, 'UPD');
UPDATE PLJ_NOTES
SET NOTE_TEXT = l_note_text,
last_updated_by = PLJ_AUTH.GET_CURRENT_USER,
last_updated_date=SYSDATE
WHERE NOTE_ID = p_note_id;
PLJ_LG.D('Note ID: '||p_note_id||'. '||SQL%ROWCOUNT||' rows updated.');
PLJ_LG.POP;
EXCEPTION
WHEN OTHERS THEN
PLJ_ERR.HANDLE(PLJ_ERR_MSG.C_NOTE_UPD);
END UPD;
/* }}} */
/* {{{ DEL */
PROCEDURE DEL (p_note_id IN PLJ_NOTES.NOTE_ID%TYPE) IS
BEGIN
PLJ_LG.PUSH(PLJ_CONST.C_PLJ_MOD_NOTE, 'PURGE');
PLJ_LG.D('Delete note ID: '||p_note_id);
DELETE FROM PLJ_NOTE_ASSGTS
WHERE NOTE_ID = p_note_id;
PLJ_LG.D(SQL%ROWCOUNT||' rows deleted from PLJ_NOTE_ASSGTS table.');
DELETE FROM PLJ_NOTES
WHERE NOTE_ID = p_note_id;
PLJ_LG.D(SQL%ROWCOUNT||' rows deleted NOTES table.');
PLJ_LG.POP;
EXCEPTION
WHEN OTHERS THEN
PLJ_ERR.HANDLE(PLJ_ERR_MSG.C_NOTE_PURGE);
END DEL;
/* }}} */
/* {{{ GET_NOTE_COUNT */
FUNCTION GET_NOTE_COUNT ( p_note_of_code IN PLJ_NOTE_ASSGTS.NOTE_OF_CODE%TYPE,
p_note_of_id IN PLJ_NOTE_ASSGTS.NOTE_OF_ID%TYPE)
RETURN pls_integer IS
l_note_count pls_integer :=0;
BEGIN
PLJ_LG.PUSH (PLJ_CONST.C_PLJ_MOD_NOTE, 'GET_NOTE_COUNT');
PLJ_LG.D('Note of Code '||l_note_iof_code);
select count(*)
into l_note_count
from PLJ_NOTE_ASSGTS
where NOTE_OF_CODE = p_note_of_code
and NOTE_OF_ID = p_note_of_id;
PLJ_LG.D('Returning: '||l_note_count);
PLJ_LG.POP;
return l_note_count;
EXCEPTION
WHEN OTHERS THEN
PLJ_ERR.HANDLE();
return l_note_count;
END GET_NOTE_COUNT;
/* }}} */
/* {{{ GET_NOTE_TEXT */
FUNCTION GET_NOTE_TEXT ( p_note_id IN PLJ_NOTES.NOTE_ID%TYPE)
RETURN PLJ_NOTES.NOTE_TEXT%TYPE is
l_note_text PLJ_NOTES.NOTE_TEXT%TYPE;
BEGIN
PLJ_LG.PUSH (PLJ_CONST.C_PLJ_MOD_NOTE, 'GET_NOTE_TEXT');
PLJ_LG.D('Get Note for Note ID: '||p_note_id);
BEGIN
select note_text
into l_note_TEXT
from PLJ_NOTES
where NOTE_ID = p_note_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
PLJ_LG.W('No note found');
l_note_text := NULL;
END;
PLJ_LG.D('Returning: '||l_note_text);
PLJ_LG.POP;
return l_note_text;
EXCEPTION
WHEN OTHERS THEN
PLJ_ERR.HANDLE();
return l_note_text;
END GET_NOTE_TEXT;
/* }}} */
END PLJ_NOTE;
/
