Exceptions In Oracle

Exception Handling In Oracle

                      Exception means "Handling the Errors".

Types of Exceptions::

        1.Pre Defined Exceptions
        2.User Defined Exceptions

1.Pre Defined Exceptions

        1.zero_divide
        2.no_data_found
        3.dup_val_on_index
        4.value_error
        5.too_many_rows
        6.others


2.User Defined Error::

        1.Age limit Check(Age_limit)
        2.Male or Female(Gender_Ex)
        3.Min or Max Salary Check

These kind of case we can declare Exceptions and Raise the Exception.

Application Error::

-00000 to -19999 : Oracle Error Range
-20000 to -20999 : User error range
PRAGMA Exception_init(name, number)
SQLERM
SELCODE

Examples

DECLARE
   v_a   NUMBER;
BEGIN
   SELECT 5 / 5
     INTO v_a
     FROM DUAL;

   DBMS_OUTPUT.put_line (v_a);
END;
/

1
PL/SQL procedure successfully completed.

ZERO_DIVIDE_ERROR::

DECLARE
   v_a   NUMBER;
BEGIN
   DBMS_OUTPUT.put_line ('----> 1 <----');

   SELECT 5 / 0
     INTO v_a
     FROM DUAL;

   DBMS_OUTPUT.put_line (v_a);
   DBMS_OUTPUT.put_line ('----> 2 <----');
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter Some other Number');
END;
/
----> 1 <----
Enter Some other Number
PL/SQL procedure successfully completed.

Example

DECLARE
   v_first_name   employees.first_name%TYPE;
   v_id           NUMBER := &id;
BEGIN
   SELECT First_name
     INTO v_first_name
     FROM employees
    WHERE employee_id = v_id;

   DBMS_OUTPUT.put_line ('Name : ' || v_first_name);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter Some other Number');
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (v_id || ' is not a valid employee id');
END;
/

Enter value for id: 101
Name : Neena
PL/SQL procedure successfully completed.

Example

DECLARE
   v_first_name   employees.first_name%TYPE;
   v_id           NUMBER := &id;
BEGIN
   SELECT First_name
     INTO v_first_name
     FROM employees
    WHERE employee_id = v_id;

   DBMS_OUTPUT.put_line ('Name : ' || v_first_name);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter Some other Number');
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line (v_id || ' is not a valid employee id');
END;
/

Enter value for id: 1212
1212 is not a valid employee id
PL/SQL procedure successfully completed.

VALUE_ERROR::

DECLARE
   v_a   NUMBER (2);
BEGIN
   v_a := 23;
   DBMS_OUTPUT.put_line (v_a);
   v_a := 134;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line ('Check the size');
END;
/
23
Check the size
PL/SQL procedure successfully completed.

Too_Many_Rows::

DECLARE
   v_empid   employees.employee_id%TYPE;
BEGIN
   SELECT employee_id
     INTO v_empid
     FROM employees
    WHERE first_name = 'Steven';

   DBMS_OUTPUT.put_line ('Employee Id : ' || v_empid);
EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      DBMS_OUTPUT.put_line ('Declare Cursor');
END;
/
Declare Cursor
PL/SQL procedure successfully completed.

SQLERRM::

DECLARE
   v_empid   employees.employee_id%TYPE;
BEGIN
   DBMS_OUTPUT.put_line (SQLERRM (-1422));

   SELECT employee_id
     INTO v_empid
     FROM employees
    WHERE first_name = 'Steven';

   DBMS_OUTPUT.put_line ('Employee Id : ' || v_empid);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.put_line ('Enter some other number');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'Some other error ->' || SQLERRM || '~' || SQLCODE);
END;
/

ORA-01422: exact fetch returns more than requested number of rows
Some other error -> ORA-01422: exact fetch returns more than
requested number of rows ~ -1422
PL/SQL procedure successfully completed.


USER DEFINED EXCEPTIONS::

DECLARE
   v_empid    NUMBER := 1004;
   v_name     VARCHAR2 (4000) := 'Karthic';
   v_salary   NUMBER := 8000;
   abort_ex   EXCEPTION;
BEGIN
   IF v_salary <= 10000
   THEN
      RAISE abort_ex;
   END IF;

   INSERT INTO emp_tb
        VALUES (v_empid, v_name, v_salary);
EXCEPTION
   WHEN abort_ex
   THEN
      DBMS_OUTPUT.put_line ('Salary Should be greater than 10000');
END;
/

Salary Should be greater than 10000
PL/SQL procedure successfully completed.

APPLICATION ERROR::

DECLARE
   v_empid    NUMBER := 1004;
   v_name     VARCHAR2 (4000) := 'Karthic';
   v_salary   NUMBER := 8000;
BEGIN
   IF v_salary <= 10000
   THEN
      RAISE_APPLICATION_ERROR (-20001, 'Salary must be greater than 10000');
   END IF;

   INSERT INTO emp_tb
        VALUES (v_empid, v_name, v_salary);
END;
/

DECLARE
*
ERROR at line 1:
ORA-20001: Salary must be greater than 10000
ORA-06512: at line 7

PRAGMA exception_init(exception_name, code)

Use this name to any unnamed ORA- error that might be raised
by your program.

SQL> create table test_exinit(id NUMBER(2) NOT NULL);
Table created.

SQL> INSERT INTO test_exinit VALUES(NULL);
INSERT INTO test_exinit VALUES(NULL)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."TEST_EXINIT"."ID")

DECLARE
   not_null_ex   EXCEPTION;
   PRAGMA EXCEPTION_INIT (not_null_ex, -01400);
BEGIN
   INSERT INTO test_exinit
        VALUES (NULL);
EXCEPTION
   WHEN not_null_ex
   THEN
      DBMS_OUTPUT.PUT_LINE ('NULL value not allowed');
END;
/

NULL value not allowed
PL/SQL procedure successfully completed.

Example => Curosr + Loop + Exception:

DECLARE
   CURSOR sal_ck_c
   IS
      SELECT First_name, salary, department_id
        FROM employees
       WHERE department_id < 40;

   sal_ex   EXCEPTION;
BEGIN
   FOR i IN sal_ck_c
   LOOP
      BEGIN
         IF i.salary < 10000
         THEN
            RAISE sal_ex;
         END IF;

         DBMS_OUTPUT.put_line (i.first_name || ' salary is ' || i.salary);
      EXCEPTION
         WHEN sal_ex
         THEN
            DBMS_OUTPUT.put_line (i.first_name || 'salary is less than 10k');
      END;
   END LOOP;
END;
/

Jennifer salary is less than 10k
Michael salary is 13000
Pat salary is less than 10k
Den salary is 11000
Alexander salary is less than 10k
Shelli salary is less than 10k
Sigal salary is less than 10k
Guy salary is less than 10k
Karen salary is less than 10k

PL/SQL procedure successfully completed.

--Exception In Oracle End--