Basic Exception Handling

Exception Handling
CREATE OR REPLACE PROCEDURE <procedure_name> 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 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
DECLARE
 ecode NUMBER;
 emesg VARCHAR2(200);
BEGIN
  NULL;
  ecode := SQLCODE;
  emesg := SQLERRM;
  dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
END;
/
--Modified To Force An Error

CREATE OR REPLACE PROCEDURE force_error IS

BEGIN
  NULL;
  RAISE too_many_rows;
END force_error;
/

exec force_error


--Trap And Hide The Error    
CREATE OR REPLACE PROCEDURE trap_error IS

BEGIN
  NULL;
  RAISE too_many_rows;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END trap_error;
/
Display Error With SQLCODE

CREATE OR REPLACE PROCEDURE trap_errcode 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


Display Error With SQLERRM     CREATE OR REPLACE PROCEDURE trap_errmesg 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 Invalid Cursor Exception Demo

CREATE OR REPLACE PROCEDURE invcur_exception 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


Two Many Rows Exception Demo     CREATE OR REPLACE PROCEDURE tmr_exception 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

Divide By Zero Error Pass In The Zero

CREATE OR REPLACE PROCEDURE zero_div (numin NUMBER) 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)

------------
Named Exception In a Function Demo

CREATE OR REPLACE FUNCTION is_ssn (string_in VARCHAR2)
RETURN VARCHAR2 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 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 IS

BEGIN
  INSERT INTO results
  (sourceno)
  VALUES
  ('1');
  COMMIT;
END PragmaExcInit;
/

exec pragmaexcinit

-- the same procedure with exception trapping
CREATE OR REPLACE PROCEDURE PragmaExcInit 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 NUMBER) 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
------------------
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 NUMBER) 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     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) IS
 i NUMBER(3) := someval;
BEGIN
  i := i+0;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/

exec demo(999);
exec demo(1000);
---------------------
Exception Handling Demo

Incomplete Handling     CREATE TABLE test (
col INT);

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

CREATE OR REPLACE PROCEDURE p 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     Declaration exceptions can not be trapped with an error handler
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;
/
----------

exec trap_error