how to log exceptions in a pl/sql procedure

Logging errors in plsql procedures is a very important debugging factor especially if the procedure is used in night jobs. If a night job fails, in the morning it's best that you know why the procedure hang, and what has it done until it hang.

First of all create a log table:

CREATE TABLE AM_ERROR_LOG
(
  ERROR_SQL     VARCHAR2(4000 BYTE),
  DATE_RUN       DATE    DEFAULT SYSDATE,
  NAMEPROCEDURE  VARCHAR2(200 BYTE),
  ERROR VARCHAR2(1000 BYTE)
)
;

It's best that this table is on another schema (a log schema), and especially on another tablespace (a log tablespace). Because this is a log table, and there are many inserts on it, and doesn't have to be that fast, it is best not to have indexes.

Then create a procedure like this one:

CREATE OR REPLACE PROCEDURE p_am_log_err (
   sqlstr      VARCHAR2,
   datee       DATE,
   proc_name   VARCHAR2,
   err         VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO AM_ERROR_LOG
               (ERROR_SQL, date_run, nameprocedure, ERROR
               )
        VALUES (sqlstr, datee, proc_name, err
               );
   COMMIT;
END;
/

The important thig here is that this procedure has  PRAGMA AUTONOMOUS_TRANSACTION  this means that the commit here doesn't affect other transactions.

Here is an example on how to use the above procedure:

CREATE OR REPLACE PROCEDURE P_night_job_proc()
IS

errMsg VARCHAR2(4000);

BEGIN
---------------------------- procedure body
begin
 -----------------------internal plsql block
COMMIT;

EXCEPTION
                  WHEN OTHERS THEN
                            errMsg := SQLERRM;
                           p_am_log_err('current running sql', sysdate, 'CALCUL_CHEI', errMsg);
end;
  COMMIT;
-------------------- end of procedure body
EXCEPTION
                  WHEN OTHERS THEN
                            errMsg := SQLERRM;
                           p_am_log_err('another running sql', sysdate, 'CALCUL_CHEI', errMsg);
 
END P_night_job_proc;
/


Of course WHEN OTHERS THEN is a very general and easy way to capture an exception. Still it's best practice to capture exceptions and insert them in a log table especially for key procedures.