Exception Section

---

1. What is Exception Handling

---

PL/SQL provides a feature to handle the exceptions which occur in a PL/SQL Block known as Exception Handling.
Using this Exception Handling we can test the code and avoid it from exiting abruptly.
When an exception occurs a message which explains its cause is received.

There are three types of Exceptions :

1. Named System Defined Exceptions
2. Unnamed System Exceptions
3. User – Defined Exceptions

---

---

2. Named System Defined Exceptions

---

System Exceptions are automatically raised by Oracle.
For Ex:

1. NO_DATA_FOUND — When a SELECT…INTO clause does not return any row from a Table — ORA-01403
2. TOO_MANY_ROWS — When you SELECT or fetch more than one row into a variable — ORA-01422

There are totally 20 Pre-defined Exceptions.

/ Predefined exceptions /
CURSOR_ALREADY_OPEN exception;
pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, ‘-6511’);

DUP_VAL_ON_INDEX exception;
pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, ‘-0001’);

TIMEOUT_ON_RESOURCE exception;
pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, ‘-0051’);

INVALID_CURSOR exception;
pragma EXCEPTION_INIT(INVALID_CURSOR, ‘-1001’);

NOT_LOGGED_ON exception;
pragma EXCEPTION_INIT(NOT_LOGGED_ON, ‘-1012’);

LOGIN_DENIED exception;
pragma EXCEPTION_INIT(LOGIN_DENIED, ‘-1017’);

NO_DATA_FOUND exception;
pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);

ZERO_DIVIDE exception;
pragma EXCEPTION_INIT(ZERO_DIVIDE, ‘-1476’);

INVALID_NUMBER exception;
pragma EXCEPTION_INIT(INVALID_NUMBER, ‘-1722’);

TOO_MANY_ROWS exception;
pragma EXCEPTION_INIT(TOO_MANY_ROWS, ‘-1422’);

STORAGE_ERROR exception;
pragma EXCEPTION_INIT(STORAGE_ERROR, ‘-6500’);

PROGRAM_ERROR exception;
pragma EXCEPTION_INIT(PROGRAM_ERROR, ‘-6501’);

VALUE_ERROR exception;
pragma EXCEPTION_INIT(VALUE_ERROR, ‘-6502’);

ACCESS_INTO_NULL exception;
pragma EXCEPTION_INIT(ACCESS_INTO_NULL, ‘-6530’);

COLLECTION_IS_NULL exception;
pragma EXCEPTION_INIT(COLLECTION_IS_NULL , ‘-6531’);

SUBSCRIPT_OUTSIDE_LIMIT exception;
pragma EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,’-6532′);

SUBSCRIPT_BEYOND_COUNT exception;
pragma EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,’-6533′);

— exception for ref cursors
ROWTYPE_MISMATCH exception;
pragma EXCEPTION_INIT(ROWTYPE_MISMATCH, ‘-6504’);

SYS_INVALID_ROWID EXCEPTION;
PRAGMA EXCEPTION_INIT(SYS_INVALID_ROWID, ‘-1410’);

— The object instance i.e. SELF is null
SELF_IS_NULL exception;
pragma EXCEPTION_INIT(SELF_IS_NULL, ‘-30625’);

CASE_NOT_FOUND exception;
pragma EXCEPTION_INIT(CASE_NOT_FOUND, ‘-6592’);

— Added for USERENV enhancement, bug 1622213.
USERENV_COMMITSCN_ERROR exception;
pragma EXCEPTION_INIT(USERENV_COMMITSCN_ERROR, ‘-1725’);

— Parallel and pipelined support
NO_DATA_NEEDED exception;
pragma EXCEPTION_INIT(NO_DATA_NEEDED, ‘-6548’);
— End of 8.2 parallel and pipelined support

— PL/SQL Block to display the Employee Details without Exceptions —
DECLARE
l_name VARCHAR2(50);
BEGIN
SELECT ename INTO l_name FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is : ‘||l_name);
END;
/

When this PL/SQL Block is executed it returns with the Error saying Exact Fetch returns more than requested no.f of rows.
This error can be handled explicity using Exceptions.

— PL/SQL Block to display the Employee Details and handle Exceptions appropriately —
DECLARE
l_name VARCHAR2(50);
BEGIN
SELECT ename INTO l_name FROM emp
WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is : ‘||l_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Your SELECT statement retrieved Multiple Rows. Consider using a Cursor’);
END;
/

— PL/SQL Block to display the Employee Details and handle Exceptions appropriately —
SET VERIFY OFF
DECLARE
l_name VARCHAR2(50);
BEGIN
SELECT ename INTO l_name FROM emp
WHERE deptno = &d;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is : ‘||l_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Deptno entered is invalid.Please enter a existing Department’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Your SELECT statement retrieved Multiple Rows. Consider using a Cursor’);
END;
/

---

---

3. Unnamed System Exceptions

---

Those System Exceptions for which Oracle does not provide a name is known as Unnamed System Exception.
There are two ways to handle them :
1. By using WHEN OTHERS Exception Handler
2. By associating the Exception Code to a name using it as named exception.

We can assign a name to unnamed system exceptions using PRAGMA called EXCEPTION_INIT.

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile-time, not at run time.

Steps to be followed to use unnamed system exceptions

1. They are raised implicitly
2. If they are not handled in WHEN OTHERS they must be handled explicitly
3. To handle the exception explicitly they must be declared using PRAGMA EXCEPTION_INIT

PRAGMA EXCEPTION_INIT :
exception_name and error_number
where exception_name is character string up to 2048 bytes and

— PL/SQL Block to insert Employee Details and while Inserting NULL values to EMPNO column handle that using PRAGMA EXCEPTION INIT and handle Exceptions appropriately —
BEGIN
INSERT INTO emp(ename,sal)VALUES(‘Rahul’,2000);
END;
/

— PL/SQL Block to handle the validation of NULL Value in Primary Key —
DECLARE
insert_exep EXCEPTION;
— Handles Cannot Insert NULL into Primary Key Constraint —
PRAGMA EXCEPTION_INIT(insert_exep,-01400);
BEGIN
INSERT INTO emp(ename,sal)VALUES(‘Rahul’,2000);
EXCEPTION
WHEN insert_exep THEN
DBMS_OUTPUT.PUT_LINE(‘Insert Operation Failed’);
END;
/

— PL/SQL Block to handle the validation of NULL Value in Primary Key —
— This PL/SQL Block will error out —
— This will not be handled by PRAGMA since the No. is not a specified System Error Number —
DECLARE
insert_exep EXCEPTION;
— Handles Cannot Insert NULL into Primary Key Constraint —
PRAGMA EXCEPTION_INIT(insert_exep,-01403); — Giving a Different Error Number —
BEGIN
INSERT INTO emp(ename,sal)VALUES(‘Rahul’,2000);
EXCEPTION
WHEN insert_exep THEN
DBMS_OUTPUT.PUT_LINE(‘Insert Operation Failed’);
END;
/

— Sample PL/SQL Block to retrieve the Oracle Error Number for handling the Exception —
— Handle Invalid Foreign Key Entry into a Foreign Key Table —
BEGIN
INSERT INTO emp(empno,deptno)VALUES(8001,50);
END;
/

— PL/SQL Block to demonstrate the Invalid Foreign Key Value entered for deptno in EMP Table —
— This doess not have an Exception Block so system will handle this exception —
DECLARE
insert_exep EXCEPTION;
PRAGMA EXCEPTION_INIT(insert_exep,-02291);
BEGIN
INSERT INTO emp(empno,deptno)VALUES(8001,50);
END;
/

— PL/SQL Block to handle the Invalid Department No. as the Foreign Key —
DECLARE
insert_exep EXCEPTION;
PRAGMA EXCEPTION_INIT(insert_exep,-02291); — Handles Integrity Constraint —
BEGIN
INSERT INTO emp(empno,deptno)VALUES(8001,50);
EXCEPTION
WHEN insert_exep THEN
DBMS_OUTPUT.PUT_LINE(‘Deptno No.50 does not exist as a valid Department in Department Table’);
END;
/

Example to demonstrate handling Exception using PRAGMA EXCEPTION INIT
while trying to delete the Master Records if the Child Records exists in a different table.

---

1.
— Create the Master Table —
CREATE TABLE t1(id NUMBER PRIMARY KEY)
/

2.
— Insert a Record into the t1 Table —
INSERT INTO t1 VALUES(100)
/

INSERT INTO t1 VALUES(200)
/

3.
— Create the Detail Table —
CREATE TABLE t2(id NUMBER REFERENCES t1(id))
/

4. 
   — Insert a Record into the t1 Table —
   INSERT INTO t2 VALUES(100)
   /

INSERT INTO t2 VALUES(200)
/

5.
— Sample PL/SQL Block to retrieve the Oracle Error Number for handling the Exception —
BEGIN
DELETE FROM t1 WHERE id = 100;
END;
/

— PL/SQL Block to delete the Master Records directly if the Child Records exists and handle that exception —
DECLARE
insert_exep EXCEPTION;
PRAGMA EXCEPTION_INIT(insert_exep,-02292); — Handles Integrity Constraint —
BEGIN
DELETE FROM t1 WHERE id = 100;
EXCEPTION
WHEN insert_exep THEN
DBMS_OUTPUT.PUT_LINE(‘Cannot Delete Master Records since Child Records Exists.’);
END;
/

6.
— Truncate and Drop the Tables created —

DROP TABLE t2 PURGE
/

DROP TABLE t1 PURGE
/

---

When we really dont know what type of Exception gets raised during execution of PL/SQL Block we use the WHEN OTHERS.

— PL/SQL Block to handle multiple Exceptions inside each Inner Blocks —
— In the Main Block handle other Exceptions —
DECLARE
l_name VARCHAR2(60);
BEGIN
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 8000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee No. is invalid.Please provide an existing Employee No.’);
END;
BEGIN
SELECT ename INTO l_name FROM emp WHERE deptno = 10;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Your SELECT statement retrieved Multiple Rows.Consider using a Cursor’);
END;
INSERT INTO emp(ename)VALUES(‘Rahul’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘SQL Error Message : ‘||sqlerrm);
END;
/

— PL/SQL Block to handle multiple Exceptions inside each Inner Blocks —
— In the Main Block handle other Exceptions —
DECLARE
l_name VARCHAR2(60);
BEGIN
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 7369;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee No. is invalid.Please provide an existing Employee No.’);
END;
BEGIN
SELECT ename INTO l_name FROM emp WHERE deptno = 10;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Your SELECT statement retrieved Multiple Rows.Consider using a Cursor’);
END;
INSERT INTO emp(ename)VALUES(‘Rahul’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘SQL Error Message : ‘||sqlerrm);
END;
/

— PL/SQL Block to handle multiple Exceptions inside each Inner Blocks —
— In the Main Block handle other Exceptions —
DECLARE
l_name VARCHAR2(60);
BEGIN
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is : ‘||l_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee No. is invalid.Please enter a existing Employee No.’);
END;
BEGIN
SELECT ename INTO l_name FROM emp WHERE sal = 800;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is : ‘||l_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Your SELECT statement retrieved Multiple Rows.Consider using a Cursor’);
END;
INSERT INTO emp(ename)VALUES(‘Rahul’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘SQL Error Message : ‘||sqlerrm);
END;
/

— PL/SQL Block to handle WHEN OTHERS Exception which can be otherwise handled by No Data Found —
— Using SQLCODE and SQLERRM –
DECLARE
l_employee_id NUMBER;
BEGIN
SELECT empno INTO l_employee_id
FROM emp WHERE 1 = 2;
DBMS_OUTPUT.PUT_LINE(‘Result is : ‘||l_employee_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error encountered is : ‘||SQLCODE||’ with Error Message ‘||SQLERRM);
/*
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No Data Found Error’);*/
END;
/

— PL/SQL Block to handle WHEN OTHERS Exception which can be otherwise handled by No Data Found —
— Without using SQLCODE and SQLERRM –
DECLARE
l_employee_id NUMBER;
BEGIN
SELECT empno INTO l_employee_id
FROM emp WHERE 1 = 2;
DBMS_OUTPUT.PUT_LINE(‘Result is : ‘||l_employee_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error’);
/* NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No Data Found Error’);*/
END;
/

— PL/SQL Block to handle the CURSOR OPEN Exception if a Cursor is not closed —
DECLARE
v_empno emp.empno%TYPE;
CURSOR c_emp IS
SELECT empno
FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_empno;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Employee No. : ‘||v_empno);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records retrieved by the Cursor :’|| c_emp%ROWCOUNT);
— CLOSE c_emp;
OPEN c_emp;
EXCEPTION
WHEN OTHERS THEN
IF c_emp%ISOPEN
THEN
DBMS_OUTPUT.PUT_LINE(‘Cursor is not closed properly’);
CLOSE c_emp;
END IF;
END;
/

— PL/SQL Block to demonstrate a Cursor Opened and not closed,but trying to open a different Cursor —
DECLARE
v_empno emp.empno%TYPE;
CURSOR c_emp IS
SELECT empno
FROM emp;
CURSOR c_dept IS
SELECT deptno
FROM emp;
BEGIN
OPEN c_emp;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records retrieved by the Cursor :’|| c_emp%ROWCOUNT);
LOOP
FETCH c_emp INTO v_empno;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Employee No. : ‘||v_empno);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total No.of Records retrieved by the Cursor :’|| c_emp%ROWCOUNT);
OPEN c_dept;
EXCEPTION
WHEN OTHERS THEN
IF c_emp%ISOPEN
THEN
DBMS_OUTPUT.PUT_LINE(‘Cursor is not closed properly’);
CLOSE c_emp;
END IF;
END;
/

---

---

4. RAISE_APPLICATION_ERROR

---

The Procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA-error messages from stored subprograms.
The error_number is a negative integer in the range -20000 to -20999. The Maximum character string allowed is upto 2,048 bytes.

— PL/SQL Block to Select Employee Name belonging to an non-existent Deptno —
DECLARE
l_name VARCHAR2(60);
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 8000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20999,’Employee No. is invalid.Please enter a existing Employee No.’);
END;
/

DECLARE
l_name VARCHAR2(60);
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 8000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘Employee No. is invalid.Please enter a existing Employee No.’);
dbms_output.put_line(‘Employee No.’);
END;
/

DECLARE
l_name VARCHAR2(60);
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 8000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,’Employee No. is invalid.Please enter a existing Employee No.’);
dbms_output.put_line(‘Employee No. is invalid.Please enter a existing Employee No.’);
END;
/

DECLARE
l_name VARCHAR2(60);
BEGIN
SELECT ename INTO l_name FROM emp WHERE empno = 8000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘Employee No. is invalid.Please enter a existing Employee No.’);
raise_application_error(-20101,’Employee No. is invalid.Please enter a existing Employee No.’);
dbms_output.put_line(‘Employee No. is invalid.Please enter a existing Employee No.’);
END;
/

---

---

5. User Defined Exceptions

---

— PL/SQL Block to explain the User Defined Exceptions in PL/SQL —
BEGIN

DECLARE
v_count NUMBER;
v_err   EXCEPTION;
v_empno NUMBER:=&e;
BEGIN
    SELECT COUNT(EMPNO) INTO v_count FROM EMP WHERE empno = v_empno;
    IF v_count > 0 THEN
        RAISE v_err;
    ELSIF v_count = 0 THEN
        INSERT INTO EMP(EMPNO,ENAME)VALUES(v_empno,'K1');
    END IF;
EXCEPTION

WHEN v_err THEN
DBMS_OUTPUT.PUT_LINE(‘Employee already Exists.Please enter a new Employee into Employee Master Table’);
END;
DBMS_OUTPUT.PUT_LINE(‘New Employee Inserted’);
END;
/

BEGIN

DECLARE
v_count NUMBER;
v_err   EXCEPTION;
v_empno NUMBER:=&e;
BEGIN
    SELECT COUNT(EMPNO) INTO v_count FROM EMP WHERE empno = v_empno;
    IF v_count > 0 THEN
        RAISE v_err;
    ELSIF v_count = 0 THEN
        INSERT INTO EMP(EMPNO,ENAME)VALUES(v_empno,'K1');
    END IF;
EXCEPTION
WHEN v_err THEN
RAISE_APPLICATION_ERROR(-20000,'Employee already Exists.');
END;

DBMS_OUTPUT.PUT_LINE(‘Employee Record’);
END;
/

The above PL/SQL Block will insert Employee Records into EMP Table after the block validates for a valid EMPNO Value from EMP Table else it abruptly stops the PL/SQL Block execution.

— PL/SQL Block to explain the User Defined Exceptions in PL/SQL —
DECLARE

v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_count NUMBER;

BEGIN
v_empno:=&eno;
SELECT COUNT(empno) INTO v_count FROM emp WHERE empno = v_empno;
IF v_count > 0 THEN
RAISE_APPLICATION_ERROR(-20000,’Employee already Exists.’);
ELSIF v_count = 0 THEN
v_ename:=’&enme’;
INSERT INTO EMP(EMPNO,ENAME)VALUES(v_empno,v_ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Record Inserted’);
END IF;
END;
/

---

---