Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational