Oracle Scheduling error_logs table Oracle Tips by Burleson Consulting

Another alternative is to log an error message in the exception handler.  First, the error_logs table to hold the error messages needs to be created.

CREATE TABLE error_logs (
  id                 NUMBER(10)      NOT NULL,
  prefix             VARCHAR2(100),
  data               VARCHAR2(4000)  NOT NULL,
  error_level        NUMBER(2)       NOT NULL,
  created_timestamp  TIMESTAMP       NOT NULL,
  created_by         VARCHAR2(50)    NOT NULL);

ALTER TABLE error_logs ADD (CONSTRAINT error_logs_pk PRIMARY KEY (id));

CREATE SEQUENCE error_logs_seq;

The usages of the table columns are listed below:

* ID - A system generated sequence number used as the primary key.

* PREFIX - An optional string to identify the source of the error.  This may be used to identify the job or procedure that the job is running. 

* DATA - A string containing information about the error. This could be just the error text or some additional information like the position in the process where the error occurred and any relevant parameters.

* ERROR_LEVEL - A number that can be used as an indicator of the severity of the error.  For example, normal errors may be level five, major errors may be level one and minor warnings may be level 10.  This has a default value of five.

* CREATED_TIMESTAMP - A timestamp indicating the time the error was logged.  This defaults to the current system time.

* CREATED_BY - A reference to the user who created the error.  This defaults to the database user.

With the table in place, the logging code starting with the package specification in the err.pks script can be created; then the package body in the err.pkb script can also be created.

* err.pks

-- Requirements :
/*
CREATE TABLE error_logs (
  id                 NUMBER(10)      NOT NULL,
  prefix             VARCHAR2(100),
  data               VARCHAR2(4000)  NOT NULL,
  error_level        NUMBER(2)       NOT NULL,
  created_timestamp  TIMESTAMP       NOT NULL,
  created_by         VARCHAR2(50)    NOT NULL);

ALTER TABLE error_logs ADD (CONSTRAINT error_logs_pk PRIMARY KEY (id));

CREATE SEQUENCE error_logs_seq;
*/
-- *****************************************************************

CREATE OR REPLACE PACKAGE err AS

PROCEDURE reset_defaults;

PROCEDURE logs_on;
PROCEDURE logs_off;

PROCEDURE line (p_prefix       IN  error_logs.prefix%TYPE,
                p_data         IN  error_logs.data%TYPE,
                p_error_level  IN  error_logs.error_level%TYPE DEFAULT 5,
                p_error_user   IN  error_logs.created_by%TYPE DEFAULT USER);

PROCEDURE line (p_data         IN  error_logs.data%TYPE,
                p_error_level  IN  error_logs.error_level%TYPE DEFAULT 5,
                p_error_user   IN  error_logs.created_by%TYPE DEFAULT USER);

END err;
/
SHOW ERRORS

* err.pkb

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

CREATE OR REPLACE PACKAGE BODY err AS

-- Package Variables
g_logs_on  BOOLEAN := TRUE;

-- Exposed Methods

-- -----------------------------------------------------------------
PROCEDURE reset_defaults IS
-- -----------------------------------------------------------------

BEGIN
  g_logs_on := TRUE;
END;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE logs_on IS
-- -----------------------------------------------------------------
BEGIN
  g_logs_on := TRUE;
END;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE logs_off IS
-- -----------------------------------------------------------------
BEGIN
  g_logs_on := FALSE;
END;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE line (p_prefix       IN  error_logs.prefix%TYPE,
                p_data         IN  error_logs.data%TYPE,
                p_error_level  IN  error_logs.error_level%TYPE DEFAULT 5,
                p_error_user   IN  error_logs.created_by%TYPE DEFAULT USER) IS
-- -----------------------------------------------------------------
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF g_logs_on THEN
    INSERT INTO error_logs
    (id,
     prefix,
     data,
     error_level,
     created_timestamp,
     created_by)
    VALUES
    (error_logs_seq.NEXTVAL,
     p_prefix,
     p_data,
     p_error_level,
     SYSTIMESTAMP,
     p_error_user);

    COMMIT;
  END IF;
END;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE line (p_data         IN  error_logs.data%TYPE,
                p_error_level  IN  error_logs.error_level%TYPE DEFAULT 5,
                p_error_user   IN  error_logs.created_by%TYPE DEFAULT USER) IS
-- -----------------------------------------------------------------
BEGIN
  line (p_prefix       => NULL,
        p_data         => p_data,
        p_error_level  => p_error_level,
        p_error_user   => p_error_user);
END;
-- -----------------------------------------------------------------

END err;
/
SHOW ERRORS

The line procedure is overloaded allowing it to be used with or without a prefix.  The main procedure is defined as an autonomous transaction, allowing it to commit the logging data without affecting the transactions within the job. In its simplest form, error logging can be achieved by issuing the following command:

SQL> execute err.line(?This is an error?);

PL/SQL procedure successfully completed.

The contents of the error_logs table can be queried using the list_error_logs.sql script listed below.

* list_error_logs.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
-- Parameters:
--    1) Specific prefix or "all".
-- *****************************************************************

set feedback off
alter session set nls_timestamp_format='DD-MON-YYYY HH24:MI:SS';
set feedback on

set linesize 150
set verify off

column id format 99999
column prefix format a20
column data format a30
column created_timestamp format a20
column created_by format a10

select
   id,
   prefix,
   data,
   error_level,
   created_timestamp,
   created_by
from
   error_logs
where
   nvl(prefix, '~') = decode(upper('&1'), 'ALL', nvl(prefix, '~'), '&1')
order by
   id
;

The output from this query is displayed below.

SQL> @list_error_logs.sql all

    ID PREFIX  DATA              ERROR_LEVEL CREATED_TIMESTAMP     CREATED_BY
------ ------- ----------------- ----------- --------------------- ----------
     1         This is an error            5 14-AUG-2004 12:58:08  JOB_USER

1 row selected.

Armed with this error logging procedure, the exception_job_proc_1 procedure can be amended to create the exception_job_proc_2 procedure.

* exception_job_proc_2.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************

CREATE OR REPLACE PROCEDURE exception_job_proc_2 AS
BEGIN
  -- Force an error.
  RAISE_APPLICATION_ERROR(-20000, ?Forced error in exception_job_proc_2?);
EXCEPTION
  WHEN OTHERS THEN
    ERR.line(p_prefix => ?exception_job_proc_2?,
             P_data   => SQLERRM);
END exception_job_proc_2;
/

Running the following procedure results in the generation of the appropriate error log:

SQL> exec exception_job_proc_2;

PL/SQL procedure successfully completed.

SQL> @list_error_logs.sql exception_job_proc_2

ID PREFIX     DATA                   ERROR_LEVEL CREATED_TIMESTAMP    CREATED_BY
------ -------------------- ------------------------------ ----------- --------------------
 2 exception_job_proc_2 ORA-20000: Forced error in exc   5 14-AUG-2004 13:35:02 JOB_USER
                            eption_job_proc_2

1 row selected.

With this mechanism in place, PL/SQL exceptions can be monitored, and their presence will not cause the jobs to fail.

This section has introduced several methods by which error checking can be implemented.  Chapter 6 contains additional information on the job run history available in Oracle10g, which has some relevance to monitoring job failures.  Next, the following section will focus on mechanisms for sending email notifications of job failures.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.