Basic logging Procedure in PL/SQL

As an Oracle Applications Technical Consultant, I spent a lot of time analyzing code to understand why something has happened or something did not happen inside big PL/SQL procedures written by some other developers. I get queries from functional consultants to explain them why that program did not update a particular record or why was the error message displayed on the screen. The answer will be either you did it in the wrong way or there is a bug in the code.

To arrive at this conclusion, I need to trace the flow of the code by adding logging messages at all possible points and executing the program again. Log messages can be written to concurrent program log file, a log file in the server or a log table in the database. I prefer to use table because it can be used in all the cases and it is easy to retrieve the messages using SQL.

Here is a simple but effective script that every PL/SQL developer should have in his kitty. Feel free to use it if you have not written this yet.

First we need to create a database table to store the log
messages. Here is the script to create the table

CREATE TABLE XX_LOG
(CREATION_DATE DATE,
MESSAGE VARCHAR2(4000),
SEQ NUMBER(10))
/

Now create the sequence to generate value for the column SEQ_NUMBER

CREATE SEQUENCE XX_LOG_SEQ START WITH 1 INCREMENT BY 1

Finally, create a procedure which will insert into the log table. This procedure is made an AUTONOMOUS_TRANSACTION so that it is COMMITed independent of the COMMIT or ROLLBACK of the calling function.

CREATE OR REPLACE PROCEDURE xxinsert_log(p_message in varchar2) AS
pragma autonomous_transaction;
BEGIN
INSERT INTO xx_log
VALUES (sysdate,SUBSTR(p_message,1,4000),xx_log_seq.nextval);
COMMIT;
END xxinsert_log;

Now calls to this procedure can be simply inserted to the programs you want to debug.

CREATE OR REPLACE PROCEDURE my_buggy_procedure (parameter1 in number) IS
BEGIN
xxinsert_log('Start of procedure. PArameter1 = 'parameter1);
..................................
..................................
xxinsert_log('Middle of procedure');
..................................
..................................
xxinsert_log('End of Procedure');
EXCEPTION
WHEN others THEN
…………………….
xxinsert_log('Exception of procedure 'sqlerrm);
END my_buggy_procedure;

After executing the procedure the messages can be retrieved and analyzed using simple SQL.