September 13, 2007

Easy logging and debugging in Forms

Each forms-application needs a simple way to log errors and find them. This technique can be used also to debug Forms, Reports and pure PL/SQL.

First create the table, sequence and view to store the logging-information:


CREATE TABLE Logging (
ID NUMBER(8,0) NOT NULL,
SESSION_ID NUMBER(8,0),
INSERT_DATE DATE NOT NULL,
TEXT VARCHAR2(2000) NOT NULL);

CREATE SEQUENCE Logging_SEQ;

CREATE OR REPLACE VIEW V_Logging_desc
(ID, SESSION_ID, INSERT_DATE, TEXT)
AS SELECT ID, SESSION_ID, INSERT_DATE, TEXT
FROM Logging
ORDER BY SESSION_ID DESC, ID DESC;

You need also a package with some functions to start the logging-process

CREATE OR REPLACE PACKAGE PK_DEBUG IS
FUNCTION Debug_allowed RETURN BOOLEAN;
FUNCTION Next_ID RETURN NUMBER;

PROCEDURE Disable;
PROCEDURE Enable;
PROCEDURE Destroy;
PROCEDURE Init (P_Debug_allowed IN BOOLEAN DEFAULT TRUE);
PROCEDURE Write (P_Text IN VARCHAR2,
P_Session_ID IN NUMBER DEFAULT NULL);

G_Debug_allowed BOOLEAN := TRUE;
G_Session_ID NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY PK_DEBUG IS
FUNCTION Debug_allowed RETURN BOOLEAN IS
BEGIN
RETURN (G_Debug_allowed);
END;

FUNCTION Next_ID RETURN NUMBER IS
V_ID NUMBER;
BEGIN
SELECT Logging_SEQ.nextval
INTO V_ID
FROM DUAL;
RETURN (V_ID);
END;

PROCEDURE Disable IS
BEGIN
G_Debug_allowed := FALSE;
END;

PROCEDURE Enable IS
BEGIN
G_Debug_allowed := TRUE;
END;

PROCEDURE Destroy IS
BEGIN
Write ('----------------------stopp '
|| to_char (G_Session_ID) || '--');
G_Session_ID := NULL;
END;

PROCEDURE Init (
P_Debug_allowed IN BOOLEAN DEFAULT TRUE) IS
BEGIN
G_Debug_allowed := P_Debug_allowed;
G_Session_ID := Next_ID;
Write ('--start ' || to_char (G_Session_ID)
|| '----------------------');
END;

PROCEDURE Write (
P_Text IN VARCHAR2,
P_Session_ID IN NUMBER DEFAULT NULL) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF Debug_allowed THEN
IF G_Session_ID IS NULL THEN
Init;
END IF;
INSERT INTO Logging (ID,
Session_ID, Insert_Date, Text)
VALUES (Next_ID,
NVL (P_Session_ID, G_Session_ID),
Sysdate, P_Text);
COMMIT;
END IF;
END;
END;
/

You start the debug with INIT and end it with DESTROY. Error-Messages are logged through WRITE. For example:

pk_Debug.Write ('Hello World - ' || V_Test);

Parts of your debugging can be deactivated with DISABLE and from this point on nothing will be written into the logging-table till ENABLE is called.

The view V_Logging_desc shows you the logging-data, grouped by the newest session-id.

ID Session Insert-Date Text
============================================
24 21 10.09.-12:38:48 -------stopp 21--
23 21 10.09.-12:38:48 Hello World - 42
22 21 10.09.-12:38:48 --start 21-------


Try it and have fun
Gerd

1 comment:

jwh said...

I use a similar method for debugging and logging. I found recently that having a message level (like in Forms) can be useful, so I can choose to have some log messages shown only in the production environment, and a lot more shown in the development environment. But the live application can be made to write all log messages for a specific session if required.

On the assumption that it's better for a system to run with no logging information than to fail because a log cannot be written, I recommend that you remove the Not Null constraints on the logging table and use "exception when others then null" (or write to a text file). Better safe than sorry ;-)