Oracle Exception Handling
Version 12.1.0.2

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
NOTE: How Oracle Does Implicit Rollbacks

Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.

If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters. Also, PL/SQL does not roll back database work done by the subprogram.

At the level of the SQL*Plus prompt, every update/insert/delete has one implicit savepoint, and also the invocation of any unnamed block. Below that, the unnamed block itself has 'sub' savepoints - one for each insert/update/delete statement in it, and one for each subprogram unit. And so on down the line.

If an error occurs, and that error is handled at any level by the time we're back at the SQL*Plus prompt, we only rollback to the immediate savepoint at the start of the update/insert/delete that errors. Otherwise we rollback to the top-level 'virtual' savepoint currently in existence, which is my offending unnamed block. That is, a handled error is handled and so can be dealt with without rolling back all the way to the top. It is handled and the transaction proceeds.

Commits define the end of a transaction (and start of a new one) - rollbacks only define the end of a transaction if they rollback to the last commit, rather than savepoint (whether explicit or implicit).

I came to my 'version' from the following by no means exhaustive tests:

CASE 1:
I created a table a with one column, a1 number, and at the sqlplus prompt inserted a row with a1 = 1.
I then ran that unnamed block I referred in an earlier post that, without an exception handler, does the following:

INSERT INTO a VALUES (2);
INSERT INTO a VALUES (3);
INSERT INTO a VALUES ('a');


As expected I get an unhandled error on the last line. When I do a select for everything in the table a,
I get the first row I inserted 'manually', the one with a1 = 1.

So there seems to have been an invisible savepoint set just before the unnamed block ran.

CASE 2:
Then I modified the unnamed block so it did two good inserts and then called a stored procedure that did two good inserts and ended with one 'bad' - inserting a character into a number column. The stored
procedure also had no error trap.

When I run this one, as expected, error message. When I select everything from the table, it gets that single row with a1 = 1.

Again, the unnamed block seems to set an invisible savepoint. And everything in the stored procedure got rolled back.

CASE 3:
Then I reran everything, except the unnamed block had a generic when others then null; error trap, and the stored procedure had a generic when others the null; error trap.

In this case as expected, no error message was generated, and when I selected * from the table, it had inserted all the rows that were valid and only failed to insert the 'bad' rows.

CASE 4:
Then I deleted everything from the table a except the a1 = 1 and did a commit.

Then I reran everything just as in case3, except that: the stored procedure had NO error trap but the unnamed block that calls it DOES. The result was exactly the same as in case3 - everything was stored except 'bad' rows.

CASE 5:
Then I deleted everything from the table 1 except the a1 = 1 and did  a commit.

Then I reran everything just as in case4, except that the stored procedure was the one with the error trap and unnamed block the one without an error trap. The results were that everything was stored in the table except the 'bad' lines.

CASE 6:
Finally ran case where my unnamed block did some ok inserts, I called  a proc that did some more ok updates, then I called a proc that did some ok inserts and a bad insert; and there were no error traps in any proc or block. Everything got rolled back.

Usenet source: Ken Quirici (c.d.o.server - 29-Oct-2004)
Data Dictionary Objects
DBA_ERRORS ERROR$ V$DIAG_CRITICAL_ERROR
DBA_ERRORS_AE DBMS_SYS_ERROR  
 
Basic Exception Handling
With Error Basic Block Structure Handling CREATE OR REPLACE PROCEDURE <procedure_name> [<in_out_parameters_list>] AUTHID <DEFINER | CURRENT_USER> IS
BEGIN
  NULL;
EXCEPTION
  WHEN <named_exception> THEN
    -- handle identified exception
  WHEN <named_exception> THEN
    -- handle identified exception
  WHEN OTHERS THEN
    -- handle any exceptions not previously handled
END;
/
CREATE OR REPLACE PROCEDURE myproc AUTHID DEFINER IS
BEGIN
  NULL;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  WHEN ZERO_DIVIDE THEN
    NULL;
  WHEN OTHERS THEN
  
  NULL;
END;
/
 
WHEN OTHERS THEN with SQLCODE and SQLERRM
Note: If not the only exception handler ... must be the last exception handler
No Error Condition SQLCODE RETURN PLS_INTEGER;
SQLERRM RETURN VARCHAR2;
SQLERRM (code IN PLS_INTEGER) RETURN VARCHAR2;
conn / as sysdba

SELECT standard.sqlerrm(-60) FROM dual;
SELECT standard.sqlerrm(-600) FROM dual;

conn uwclass/uwclass@pdbdev

set serveroutput on

DECLARE
 ecode NUMBER;
 emesg VARCHAR2(200);
BEGIN
  NULL;
  ecode := SQLCODE;
  emesg := SQLERRM;
  dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
END;
/
A Procedure That Does Nothing CREATE OR REPLACE PROCEDURE no_error AUTHID CURRENT_USER IS
BEGIN
  NULL;
END no_error;
/

exec no_error
Modified To Force An Error CREATE OR REPLACE PROCEDURE force_error AUTHID DEFINER IS
BEGIN
  NULL;
  RAISE too_many_rows;
END force_error;
/

exec force_error
Trap And Hide The Error CREATE OR REPLACE PROCEDURE trap_error AUTHID DEFINER IS
BEGIN
  NULL;
  RAISE too_many_rows;
EXCEPTION
  WHEN OTHERS THEN
    NULL;

END trap_error;
/

exec trap_error
Display Error With SQLCODE CREATE OR REPLACE PROCEDURE trap_errcode AUTHID DEFINER IS
 ecode    NUMBER(38);
 thisproc CONSTANT VARCHAR2(50) := 'trap_errmesg';
BEGIN
  NULL;
  RAISE too_many_rows;
EXCEPTION
  WHEN OTHERS THEN
    ecode := SQLCODE;
    dbms_output.put_line(thisproc || ' - ' || ecode);
END trap_errcode;
/

set serveroutput on

exec trap_errcode
Display Error With SQLERRM CREATE OR REPLACE PROCEDURE trap_errmesg AUTHID DEFINER IS
 emesg VARCHAR2(250);
BEGIN
  NULL;
  RAISE too_many_rows;
EXCEPTION
  WHEN OTHERS THEN
    emesg := SQLERRM;
    dbms_output.put_line(emesg);
END trap_errmesg;
/

set serveroutput on

exec trap_errmesg
 
WHEN <name exception> THEN & Named Exceptions
Note: A table of the named exceptions is at the bottom of this web page.
When Invalid Cursor Exception Demo CREATE OR REPLACE PROCEDURE invcur_exception AUTHID DEFINER IS
 CURSOR x_cur is
 SELECT *
 FROM all_tables;

 x_rec x_cur%rowtype;
BEGIN
  LOOP
    -- note the cursor was not opened before the FETCH
    FETCH x_cur INTO x_rec;
    EXIT WHEN x_cur%notfound;

    NULL;
  END LOOP;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    dbms_output.put_line('Whoops!');
  WHEN OTHERS THEN
    dbms_output.put_line('Some Other Problem');
END invcur_exception;
/

set serveroutput on

exec invcur_exception
Two Many Rows Exception Demo CREATE OR REPLACE PROCEDURE tmr_exception AUTHID DEFINER IS
 x all_tables.table_name%TYPE;
BEGIN
  -- note the statement will try to fetch many values
  SELECT table_name -- try to SELECT many things into 1 var
  INTO x
  FROM all_tables;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('Too Many Rows');
  WHEN OTHERS THEN
    dbms_output.put_line('Some Other Problem');
END tmr_exception;
/

set serveroutput on

exec tmr_exception
Division By Zero Error Trapping Demo CREATE OR REPLACE PROCEDURE dbz_exception (numin IN NUMBER) AUTHID DEFINER IS
 z  NUMBER := 0;
 x  NUMBER;
BEGIN
  x := numin / z;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    dbms_output.put_line('Division By Zero');
  WHEN OTHERS THEN
    dbms_output.put_line('Some Other Problem');
END dbz_exception;
/

set serveroutput on

exec dbz_exception(6)
Divide By Zero Error Pass In The Zero CREATE OR REPLACE PROCEDURE zero_div (numin IN NUMBER) AUTHID CURRENT_USER IS
 z NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'));
 x NUMBER;
BEGIN
  x := z / numin;

  dbms_output.put_line('Division By ' || TO_CHAR(numin));
EXCEPTION
  WHEN ZERO_DIVIDE THEN
     dbms_output.put_line('Division By Zero');
   WHEN OTHERS THEN
      dbms_output.put_line('Some Other Problem');
END zero_div;
/

set serveroutput on

exec zero_div(2)
exec zero_div(0)
exec zero_div(7)
 
User Defined Exceptions
Named Exception In a Function Demo CREATE OR REPLACE FUNCTION is_ssn (string_in IN VARCHAR2) RETURN VARCHAR2 AUTHID DEFINER IS
 -- validating ###-##-#### format
 delim VARCHAR2(1);
 part1 NUMBER(3,0);
 part2 NUMBER(2,0);
 part3 NUMBER(4,0);

 too_long  EXCEPTION;
 too_short EXCEPTION;
 delimiter EXCEPTION;
BEGIN
  IF LENGTH(string_in) > 11 THEN
    RAISE too_long;
  ELSIF LENGTH(string_in) < 11 THEN
    RAISE too_short;
  END IF;

  part1 := TO_NUMBER(SUBSTR(string_in,1,3),'999');

  delim := SUBSTR(string_in,4,1);
  IF delim <> '-' THEN
    RAISE delimiter;
  END IF;

  part2 := TO_NUMBER(SUBSTR(string_in,5,2),'99');

  delim := SUBSTR(string_in,7,1);
  IF delim <> '-' THEN
    RAISE delimiter;
  END IF;

  part3 := TO_NUMBER(SUBSTR(string_in,8,4),'9999');

  RETURN 'TRUE';
EXCEPTION
  WHEN too_long THEN
    dbms_output.put_line('More Than 11 Characters');
    RETURN 'FALSE';
  WHEN too_short THEN
    dbms_output.put_line('Less Than 11 Characters');
    RETURN 'FALSE';
  WHEN delimiter THEN
    dbms_output.put_line('Incorrect Delimiter');
    RETURN 'FALSE';
  WHEN OTHERS THEN
    dbms_output.put_line('Some Other Issue');
    RETURN 'FALSE';
END is_ssn;
/

set serveroutput on

SELECT is_ssn('123-45-6789') FROM dual;

SELECT is_ssn('123-45-67890') FROM dual;

SELECT is_ssn('123-45-678') FROM dual;

SELECT is_ssn('123-45=67890') FROM dual;
 
PRAGMA EXCEPTION_INIT
PRAGMA EXCEPTION_INIT Demo CREATE TABLE results (
sourceno     NUMBER(10) NOT NULL,
testno       NUMBER(3) NOT NULL,
locationid   NUMBER(10) NOT NULL);

-- the basic procedure
CREATE OR REPLACE PROCEDURE PragmaExcInit AUTHID CURRENT_USER IS
BEGIN
  INSERT INTO results
  (sourceno)
  VALUES
  ('1');
  COMMIT;
END PragmaExcInit;
/

exec pragmaexcinit

-- the same procedure with exception trapping
CREATE OR REPLACE PROCEDURE PragmaExcInit AUTHID CURRENT_USER IS
 FieldsLeftNull EXCEPTION;
 PRAGMA EXCEPTION_INIT(FieldsLeftNull, -01400);

BEGIN
  INSERT INTO results
  (sourceno)
  VALUES
  ('1');
  COMMIT;
EXCEPTION
  WHEN FieldsLeftNull THEN
    dbms_output.put_line('ERROR: Trapped Fields Left Null');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END PragmaExcInit;
/

set serveroutput on

exec pragmaexcinit
 
RAISE
Demo Procedure With User Defined Exceptions And RAISE CREATE OR REPLACE PROCEDURE raise_demo (inval IN NUMBER) AUTHID DEFINER IS
 evenno EXCEPTION;
 oddno  EXCEPTION;
BEGIN
  IF MOD(inval, 2) = 1 THEN
    RAISE oddno;
  ELSE
    RAISE evenno;
  END IF;
EXCEPTION
  WHEN evenno THEN
    dbms_output.put_line(TO_CHAR(inval) || ' is even');
  WHEN oddno THEN
    dbms_output.put_line(TO_CHAR(inval) || ' is odd');
END raise_demo;
/

set serveroutput on

exec raise_demo
 
RAISE_APPLICATION_ERROR
Returning a User Defined Exception to the application RAISE_APPLICATION_ERROR(<error_number>, <error_message>, <TRUE | FALSE>);

error_number -20000 to -20999
error_message VARCHAR2(2048)
TRUE          add to error stack
FALSE         replace error stack (the default)
CREATE OR REPLACE PROCEDURE raise_app_error (inval IN NUMBER) AUTHID DEFINER IS
 evenno EXCEPTION;
 oddno  EXCEPTION;
BEGIN
  IF MOD(inval, 2) = 1 THEN
    RAISE oddno;
  ELSE
    RAISE evenno;
  END IF;
EXCEPTION
  WHEN evenno THEN
    RAISE_APPLICATION_ERROR(-20001, 'Even Number Entered');
  WHEN oddno THEN
    RAISE_APPLICATION_ERROR(-20999, 'Odd Number Entered');
END raise_app_error;
/

exec raise_app_error;
 
Locator Variables
Locating Errors With Locator Variables. The use of variables to identify the location with a code block where the error was raised set serveroutput on

DECLARE
 step  VARCHAR2(2);
 i     NUMBER(1) := 5;
 n     NUMBER(2) := 10;
BEGIN
  step := 'A';
  n := n/i;
  i := i-1;

  step := 'B';
  n := n/i;
  i := i-2;

  step := 'C';
  n := n/i;
  i := i-2;

  step := 'D';
  n := n/i;
  i := i-2;

  step := 'E';
  n := n/i;
  i := i-1;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    dbms_output.put_line('Failure at: ' || step);
END;
/
 
Declaration Exceptions
Declaration exceptions can not be trapped with an error handler DECLARE
 i NUMBER(3) := 1000;
BEGIN
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

CREATE OR REPLACE PROCEDURE demo(someval IN NUMBER) AUTHID CURRENT_USER IS
 i NUMBER(3) := someval;
BEGIN
  i := i+0;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

exec demo(999);
exec demo(1000);
Trapping untrappable declaration exceptions by declaration nesting. DECLARE
 i NUMBER(3) := 1000;
BEGIN
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

BEGIN
  DECLARE
   i NUMBER(3) := 1000;
  BEGIN
    NULL;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

EXCEPTION
  WHEN value_error THEN
    dbms_output.put_line('Trapped The Declaration Exception');
END;
/
 
Incomplete Exception Handling
Exception handling that does not completely address the error condition CREATE TABLE test (
col INT);

ALTER TABLE test
ADD CONSTRAINT pk_test
PRIMARY KEY (col)
USING INDEX;

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
BEGIN
  INSERT INTO test VALUES (1);
END p;
/

BEGIN
  p;
  p;
END;
/

-- no records inserted as expected
SELECT * FROM test;


BEGIN
  p;
  p;
EXCEPTION
  WHEN OTHERS THEN
    NULL;

END;
/

-- one record inserted
SELECT * FROM test;
 
System-Defined Event Trapping
Error Stack Trapping with System Events set serveroutput on

CREATE OR REPLACE TRIGGER e_trigger
BEFORE delete
ON t
DECLARE
  l_text ora_name_list_t;
  l_n NUMBER;
BEGIN
  dbms_output.put_line( '--------------------' );
  dbms_output.put_line('statment causing error: ' );

  l_n := ora_sql_txt( l_text );

  FOR i in 1 .. nvl(l_text.count,0)
  LOOP
    dbms_output.put_line(l_text(i) );
  END LOOP;

  dbms_output.put_line( 'error text: ' );

  FOR i IN 1 .. ora_server_error_depth
  LOOP
    dbms_output.put_line(ora_server_error_msg(i) );
  END LOOP;

  dbms_output.put_line( '--------------------' );
END e_trigger;
/
 
Simple Error Handling Procedure
Function To Identify The User Logged Onto Oracle CREATE OR REPLACE FUNCTION getosuser RETURN VARCHAR2 AUTHID DEFINER IS
 vOSUser user_users.username%TYPE;
BEGIN
  SELECT osuser
  INTO vOSUser
  FROM sys.v_$session
  WHERE sid = (
    SELECT sid
    FROM sys.v_$mystat
    WHERE rownum = 1);

  RETURN vOSUser;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'UNK';
END getosuser;
/

SELECT getosuser FROM dual;
The Table Holding The Output Of The Error Logging Procedure CREATE TABLE errorlog (
procname     VARCHAR2(61),
loadfilename VARCHAR2(40),
runtime      DATE DEFAULT SYSDATE,
osuser       VARCHAR2(30),
mesgtext     VARCHAR2(250));
The Error Logging Procedure CREATE OR REPLACE PROCEDURE log_error AUTHID CURRENT_USER (
pProcName VARCHAR2,
pLoadFile VARCHAR2,
pMesgText VARCHAR2)
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO errorlog
  (procname, loadfilename, osuser, mesgtext)
  VALUES
  (pProcName, pLoadFile, getOSUser, pMesgText);
  COMMIT;
  -- No exception handler intentionally. Do you understand why?
END log_error;
/
To Test The Error Logging Procedure exec log_error('Test', 'None', 'Did it work?');

SELECT * FROM errorlog;
 
Database-Wide Exception Handling
Using AFTER SERVERERROR CREATE TABLE error_log (
error_timestamp TIMESTAMP(9),
database_name   VARCHAR2(50),
instance_number NUMBER,
error_number    NUMBER,
error_message   VARCHAR2(255),
logged_on_as    VARCHAR2(30),
client_host     VARCHAR2(50),
service_name    VARCHAR2(30));

CREATE OR REPLACE PROCEDURE error_trap AUTHID CURRENT_USER IS
 odbname VARCHAR2(50); -- Oracle database name
 oinst NUMBER; -- Oracle instance number
 enum NUMBER; -- Error Message number
 emsg VARCHAR2(250); -- Error text
 curschema VARCHAR2(30);
 clihost VARCHAR2(50);
 serv_name VARCHAR2(30);
-- PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  enum := sqlcode;
  emsg := sqlerrm;

  odbname := ora_database_name;
  oinst := ora_instance_num;

  SELECT sys_context('USERENV', 'CURRENT_SCHEMA')
  INTO curschema
  FROM dual;

  SELECT sys_context('USERENV', 'HOST')
  INTO clihost
  FROM dual;

  SELECT sys_context('USERENV', 'SERVICE_NAME')
  INTO serv_name
  FROM dual;

  INSERT INTO error_log
  (error_timestamp, database_name, instance_number,
   error_number, error_message, logged_on_as,
   client_host, service_name)
  VALUES
  (SYSTIMESTAMP, odbname, oinst, enum, emsg,
   curschema, clihost, serv_name);
  COMMIT;
END error_trap;
/

CREATE OR REPLACE TRIGGER error_trig
AFTER SERVERERROR ON DATABASE
CALL error_trap
/

BEGIN
  RAISE zero_divide;
END;
/

set linesize 141
col error_timestamp format a31
col database_name format a40
col error_message format a40
col logged_on_as format a20
col client_host format a20
col service_name format a20

SELECT error_timestamp, database_name, instance_number
FROM error_log;

SELECT error_timestamp, error_number, error_message
FROM error_log;

SELECT logged_on_as, client_host, service_name
FROM error_log;
 
Robust Error Handling Procedure 11gR2
Formatting Error Stack Tables And Sequence CREATE TABLE errors (
module       VARCHAR2(50),
seq_number   NUMBER,
error_number NUMBER,
error_mesg   VARCHAR2(100),
error_stack  VARCHAR2(2000),
call_stack   VARCHAR2(2000),
timestamp    DATE);

ALTER TABLE errors
ADD CONSTRAINT pk_errors
PRIMARY KEY (module, seq_number)
USING INDEX
TABLESPACE indx_sml;

CREATE TABLE call_stacks (
module VARCHAR2(50),
seq_number NUMBER,
call_order NUMBER,
object_handle VARCHAR2(10),
line_num NUMBER,
object_name VARCHAR2(80));

ALTER TABLE call_stacks
ADD CONSTRAINT pk_call_stacks
PRIMARY KEY (module, seq_number, call_order)
USING INDEX
TABLESPACE indx_sml;

ALTER TABLE call_stacks
ADD CONSTRAINT fk_cs_errors
FOREIGN KEY (module, seq_number)
REFERENCES errors (module, seq_number)
ON DELETE CASCADE;

CREATE TABLE error_stacks (
module VARCHAR2(50),
seq_number NUMBER,
error_order NUMBER,
facility CHAR(3),
error_number NUMBER(5),
error_mesg VARCHAR2(100));

ALTER TABLE error_stacks
ADD CONSTRAINT pk_error_stacks
PRIMARY KEY (module, seq_number, error_order)
USING INDEX
TABLESPACE indx_sml;

ALTER TABLE error_stacks
ADD CONSTRAINT fk_es_errors
FOREIGN KEY (module, seq_number)
REFERENCES errors (module, seq_number)
ON DELETE CASCADE;

CREATE SEQUENCE error_seq
START WITH 1
INCREMENT BY 1;
Error Handling Package Header CREATE OR REPLACE PACKAGE ErrorPkg AUTHID DEFINER AS

/* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_CALL_STACK. This package stores general error information in the errors table, with detailed call stack and error stack information in the call_stacks and error_stacks tables, respectively.

Entry point for handling errors. HandleAll should be called from all exception handlers where you want the error to be logged. p_Top should be TRUE only at the topmost level of procedure nesting. It should be FALSE at other levels. */


PROCEDURE HandleAll(p_Top BOOLEAN);

/* Prints the error and call stacks (using DBMS_OUTPUT) for the given module and sequence number. */

PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE);

/* Unwinds the call and error stacks, and stores them in the errors and call stacks tables. Returns the sequence number under which the error is stored. If p_CommitFlag is TRUE,
then the inserts are committed. In order to use StoreStacks, an error must have been handled. Thus HandleAll should have been called with p_Top = TRUE. */


PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
 p_SeqNum OUT errors.seq_number%TYPE,
 p_CommitFlag BOOLEAN DEFAULT FALSE);
END ErrorPkg;
/
Error Handling Package Body CREATE OR REPLACE PACKAGE BODY ErrorPkg AUTHID DEFINER IS
 v_NewLine CONSTANT CHAR(1) := CHR(10);
 v_Handled BOOLEAN := FALSE;
 v_ErrorStack VARCHAR2(2000);
 v_CallStack VARCHAR2(2000);

 PROCEDURE HandleAll(p_Top BOOLEAN) IS
 BEGIN
   IF p_Top THEN
     v_Handled := FALSE;
   ELSIF NOT v_Handled THEN
     v_Handled := TRUE;
     v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
     v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
   END IF;
 END HandleAll;
 --===================================================
 PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
                       p_SeqNum IN errors.seq_number%TYPE)
 IS
  v_TimeStamp errors.timestamp%TYPE;
  v_ErrorMsg errors.error_mesg%TYPE;

  CURSOR c_CallCur IS
  SELECT object_handle, line_num, object_name
  FROM call_stacks
  WHERE module = p_Module
  AND seq_number = p_SeqNum
  ORDER BY call_order;

  CURSOR c_ErrorCur IS
  SELECT facility, error_number, error_mesg
  FROM error_stacks
  WHERE module = p_Module
  AND seq_number = p_SeqNum
  ORDER BY error_order;
 BEGIN
   SELECT timestamp, error_mesg
   INTO v_TimeStamp, v_ErrorMsg
   FROM errors
   WHERE module = p_Module
   AND seq_number = p_SeqNum;

   -- output general error information
   dbms_output.put_line(TO_CHAR(v_TimeStamp, 'DD-MON-YY HH24:MI:SS'));
   dbms_output.put(' Module: ' || p_Module);
   dbms_output.put(' Error #' || p_SeqNum || ': ');
   dbms_output.put_line(v_ErrorMsg);

   -- output the call stack
   dbms_output.put('Complete Call Stack:');
   dbms_output.put(' Object Handle Line Number Object Name');
   dbms_output.put_line(' ------------- ----------- -----------');

   FOR v_CallRec in c_CallCur LOOP
     dbms_output.put(RPAD(' ' || v_CallRec.object_handle, 15));
     dbms_output.put(RPAD(' ' || TO_CHAR(v_CallRec.line_num), 13));
     dbms_output.put_line(' ' || v_CallRec.object_name);
   END LOOP;

   -- output the error stack
   dbms_output.put_line('Complete Error Stack:');

   FOR v_ErrorRec in c_ErrorCur LOOP
     dbms_output.put(' ' || v_ErrorRec.facility || '-');
     dbms_output.put(TO_CHAR(v_ErrorRec.error_number) || ': ');
     dbms_output.put_line(v_ErrorRec.error_mesg);
   END LOOP;
 END PrintStacks;
 --===================================================
 PROCEDURE StoreStacks(p_Module     IN  errors.module%TYPE,
                       p_SeqNum     OUT errors.seq_number%TYPE,
                       p_CommitFlag IN  BOOLEAN DEFAULT FALSE)
 IS
  v_SeqNum     NUMBER;
  v_Index      NUMBER;
  v_Length     NUMBER;
  v_End        NUMBER;
  v_Call       VARCHAR2(100);
  v_CallOrder  NUMBER := 1;
  v_Error      VARCHAR2(120);
  v_ErrorOrder NUMBER := 1;

  v_Handle      call_stacks.object_handle%TYPE;
  v_LineNum     call_stacks.line_num%TYPE;
  v_ObjectName  call_stacks.object_name%TYPE;
  v_Facility    error_stacks.facility%TYPE;
  v_ErrNum      error_stacks.error_number%TYPE;
  v_ErrMsg      error_stacks.error_mesg%TYPE;
  v_FirstErrNum errors.error_number%TYPE;
  v_FirstErrMsg errors.error_mesg%TYPE;
 BEGIN
   -- get the error sequence number
   SELECT error_seq.nextval
   INTO v_SeqNum
   FROM dual;

   p_SeqNum := v_SeqNum;

   -- insert first part of header info. into the errors table
   INSERT INTO errors
   (module, seq_number, error_stack, call_stack, timestamp)
   VALUES
   (p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE);

   /* unwind the error stack to get each error out by scanning the error stack string. Start with the index at the beginning of the string */
   v_Index := 1;

   /* loop through the string, finding each newline. A newline ends each error on the stack */
   WHILE v_Index < LENGTH(v_ErrorStack) LOOP
     -- v_End is the position of the newline
     v_End := INSTR(v_ErrorStack, v_NewLine, v_Index);

     -- the error is between the current index and the newline
     v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index);

     -- skip over the current error, for the next iteration
     v_Index := v_Index + LENGTH(v_Error) + 1;

     /* an error looks like 'facility-number: mesg'. Get each piece out for insertion. The facility is the first 3 characters of the error. */

     v_Facility := SUBSTR(v_Error, 1, 3);

     -- remove the facility and the dash (always 4 characters)
     v_Error := SUBSTR(v_Error, 5);

     -- next get the error number
     v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error, ':') - 1));

     -- remove the error number, colon & space (always 7 chars)
     v_Error := SUBSTR(v_Error, 8);

     -- what's left is the error message
     v_ErrMsg := v_Error;

     /* insert the errors and grab the first error number and message */

     INSERT INTO error_stacks
     (module, seq_number, error_order, facility, error_number, error_mesg)
     VALUES
     (p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum, v_ErrMsg);

     IF v_ErrorOrder = 1 THEN
       v_FirstErrNum := v_ErrNum;
       v_FirstErrMsg := v_Facility || '-' || TO_NUMBER(v_ErrNum) || ': ' || v_ErrMsg;
     END IF;

     v_ErrorOrder := v_ErrorOrder + 1;
   END LOOP;

   -- update the errors table with the message and code
   UPDATE errors
   SET error_number = v_FirstErrNum,
       error_mesg = v_FirstErrMsg
   WHERE module = p_Module
   AND seq_number = v_SeqNum;

   /* unwind the call stack to get each call out by scanning the call stack string.
      Start with the index after the  first call on the stack. This will be after  the first occurrence of 'name' and the newline. */

   v_Index := INSTR(v_CallStack, 'name') + 5;

   /* loop through the string finding each newline. A newline ends a call on the stack */
   WHILE v_Index < LENGTH(v_CallStack) LOOP
     -- v_End is the position of the newline
     v_End := INSTR(v_CallStack, v_NewLine, v_Index);

     -- the call is between the current index and the newline
     v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index);

     -- skip over the current call, for the next iteration
     v_Index := v_Index + LENGTH(v_Call) + 1;

     /*  within a call, we have the object handle, then the line number, then the object name, separated by spaces. Separate them out for insertion.
        Trim white space from the call first. */

     v_Call := TRIM(v_Call);

     -- first get the object handle
     v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' '));

     -- remove the object handle,then the white space
     v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
     v_Call := TRIM(v_Call);

     -- get the line number
     v_LineNum := TO_NUMBER(SUBSTR(v_Call,1,INSTR(v_Call,' ')));

     -- remove the line number, and white space
     v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1);
     v_Call := TRIM(v_Call);

     -- what is left is the object name
     v_ObjectName := v_Call;

     -- insert all calls except the call for ErrorPkg
     IF v_CallOrder > 1 THEN
       INSERT INTO call_stacks
       (module, seq_number, call_order, object_handle, line_num, object_name)
       VALUES
       (p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum, v_ObjectName);
     END IF;

     v_Callorder := v_CallOrder + 1;
   END LOOP;

   IF p_CommitFlag THEN
     COMMIT;
   END IF;
 END StoreStacks;
END ErrorPkg;
/
Format Error Stack Demo Table And Trigger CREATE TABLE ttt (f1 NUMBEr);

CREATE OR REPLACE TRIGGER ttt_insert
BEFORE INSERT ON ttt
BEGIN
  RAISE ZERO_DIVIDE;
END ttt_insert;
/
Error Producing Procedures (A, B, And C) CREATE OR REPLACE PROCEDURE C AUTHID DEFINER AS
BEGIN
  INSERT INTO ttt VALUES (7);
EXCEPTION
  WHEN OTHERS THEN
    ErrorPkg.HandleAll(FALSE);
    RAISE;
END C;
/
--===================================================
CREATE OR REPLACE PROCEDURE B AUTHID DEFINER AS
BEGIN
  C;
EXCEPTION
  WHEN OTHERS THEN
    ErrorPkg.HandleAll(FALSE);
RAISE;

END B;
/
--===================================================
CREATE OR REPLACE PROCEDURE A AUTHID DEFINER AS
 v_ErrorSeq NUMBER;
BEGIN
 B;
EXCEPTION
  WHEN OTHERS THEN
    ErrorPkg.HandleAll(TRUE);
    ErrorPkg.StoreStacks('Scott', v_ErrorSeq, TRUE);
    ErrorPkg.PrintStacks('Scott', v_ErrorSeq);
END A;
/
Run Format Error Stack Demo exec a;

-- examine the tables errors, call_stack, and error_stack
 
Robust Error Handling Procedure 12cR1
To be completed now that production release is final -- all_tables

backtrace depth
backtrace line
backtrace unit
current_edition
dynamic depth
error_depth
error_msg
error_number
lexical depth
owner
subprogram
unit_line
To be completed now that production release is final CREATE OR REPLACE PACKAGE utc_pkg AUTHID DEFINER IS
  PROCEDURE main;
  PROCEDURE
  FUNCTION (
END utc_pkg;
/
 
Predefined (Named) PL/SQL Exceptions
Exception Name Error Description
ACCESS_INTO_NULL ORA-06530 Attempted to assign values to the attributes of an uninitialized (NULL) object
CASE_NOT_FOUND ORA-06592 None of the choices in the WHEN clauses of a CASE statement is selected and there is no ELSE clause
COLLECTION_IS_NULL ORA-06531 Attempt to apply collection methods other than EXISTS to an uninitialized (NULL) PL/SQL table or varray
CURSOR_ALREADY_OPEN ORA-06511 Exactly what it seems to be. Tried to open a cursor that was already open
DUP_VAL_ON_INDEX ORA-00001 An attempt to insert or update a record in violation of a primary key or unique constraint
INVALID_CURSOR ORA-01001 The cursor is not open or not valid in the context in which it is being called
INVALID_NUMBER ORA-01722 It isn't a number even though you are treating it like one to trying to turn it into one
LOGIN_DENIED ORA-01017 Invalid name and/or password for the instance
NO_DATA_FOUND ORA-01403 The SELECT statement returned no rows or referencing a deleted element in a nested table or an initialized element in an Index-By table
NO_DATA_NEEDED ORA-06548 Now more rows needed: Parallel and pipelined table function support
NOT_LOGGED_ON ORA-01012 Database connection lost
PROGRAM_ERROR ORA-06501 Internal PL/SQL error
ROWTYPE_MISMATCH ORA-06504 The rowtype does not match the values being fetched, or assigned, to it
SELF_IS_NULL ORA-30625 Program attempted to call a MEMBER method, but the instance of the object type has not been intialized. The built-in parameter SELF points to the object, and is always the first parameterpassed to a MEMBER method
STORAGE_ERROR ORA-06500 A hardware problem: Either RAM or disk drive
SUBSCRIPT_BEYOND_COUNT ORA-06533 Reference to a nested table or varray index higher than the number of elements in the collection
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 Reference to a nested table or varray index outside the declared range (such as -1)
SYS_INVALID_ROWID ORA-01410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid
TIMEOUT_ON_RESOURCE ORA-00051 The activity took too long and timed out
TOO_MANY_ROWS ORA-01422 The SQL INTO statement brought back more than one value or row (only one is allowed)
USERENV_COMMITSCN_ERROR ORA-01725 Undocumented but added for USERENV enhancement, bug 1622213
VALUE_ERROR ORA-06502 An arithmetic, conversion, truncation, or size-constraint error sually raised by trying to put a 6 byte string into a 5 byte variable
ZERO_DIVIDE ORA-01476 Not only would your math teacher not let you do it. Computer's won't either. Who said you didn't learn anything useful in primary school

Related Topics
Anonymous Block
CrossEdition Triggers
DBMS_UTILITY
DDL Event Triggers
Errors
Function
Instead-Of Triggers
My Oracle Support
PL/SQL Warnings
Procedures
System Events
System Event Triggers
Table Triggers
UTL_CALL_STACK
UTL_LMS

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved