Exception Handling In Oracle
Exception means "Handling the Errors".
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--
