PL/SQL Built-in Exceptions

There are two types of exceptions in PL/SQL Built-in Exceptions and User-Defined Exceptions. Built-in exceptions are predefined in Oracle with a exception (error code) and its details. User Defined exceptions are the ones that a programmer can declare in the Declare section of a PL/SQL block just like a variable. In this post we will understand the PL/SQL Built-in Exceptions available in Oracle.

PL/SQL Built-in Exceptions

NO_DATA_FOUND

This exception is raised when a SELECT INTO statement does not return any rows. When group functions, like SUM or COUNT is used NO_DATA_FOUND is not raised. SUM, COUNT or other aggregate functions always return a non-null value. So even when SUM or COUNT doesn’t get any records to sum of count the output will be 0. So these functions in SELECT INTO will not raise NO_DATA_FOUND Exception under any situation.

DECLARE
v_sal employees.salary%TYPE;
BEGIN
	SELECT salary INTO v_sal from employees WHERE employee_id=1000;
EXCEPTION
	WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No records available');
END;

TOO_MANY_ROWS

This exception is raised when a SELECT INTO statement returns more than one row since a SELECT INTO can return only a single row. The SELECT INTO statement is used in the PL/SQL block to fetch column values into variables or record. So the SELECT INTO statement must return one row. If this statement fails to do so, this Exception is raised.

DECLARE
v_sal employees.salary%TYPE;
BEGIN
	SELECT salary INTO v_sal from employees WHERE department_id=30;
EXCEPTION
	WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('You have multiple 	employees in the department.Cant pick one salary into variable');
END;

ZERO_DIVIDE

This exception is raised when a division operation is performed with divisor 0.

DECLARE
N1 INTEGER := 10;
N2 INTEGER := 0;
ANS NUMBER;
BEGIN
ANS := N1 / N2;
DBMS_OUTPUT.PUT_LINE (‘Answer is : '||ANS);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE (‘Division by zero not allowed');
END;

VALUE_ERROR

This exception is raised when a data conversion is not possible between the variables and values being assigned. If there is a mismatch of data-type size the exception is raised.

DECLARE
v_small number(2);
BEGIN
	SELECT salary INTO v_small FROM employees WHERE employee_id=102;  
EXCEPTION
	WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Data conversion 	problem');
END;

DUP_VAL_ON_INDEX

This exception is raised when a program tries to store a duplicate value in primary or unique key column or columns. This exception is raised by an INSERT or UPDATE Statements.

BEGIN
INSERT INTO employees VALUES (104, 'Bruce', 'Ernst', 'BERNST',  '590.423.4568','21-	MAY-1991','IT_PROG',6000,NULL,103,60);
EXCEPTION	
	WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('This employee ID 	already exists');
END;

CASE_NOT_FOUND

This exception is raised by a CASE statement due to a missing condition in WHEN or ELSE. If the choices given in the WHEN or ELSE statements does not match with one being checked, the exception is raised.

DECLARE 
dayNum number:=& dayNum;
BEGIN
CASE 
WHEN dayNum=0 THEN DBMS_OUTPUT.PUT_LINE('Sunday');
WHEN dayNum=1 THEN DBMS_OUTPUT.PUT_LINE('Monday');
WHEN dayNum=2 THEN DBMS_OUTPUT.PUT_LINE('Tuesday');
WHEN dayNum=3 THEN DBMS_OUTPUT.PUT_LINE('Wednesday');
WHEN dayNum=4 THEN DBMS_OUTPUT.PUT_LINE('Thursday');
WHEN dayNum=5 THEN DBMS_OUTPUT.PUT_LINE('Friday');
WHEN dayNum=6 THEN DBMS_OUTPUT.PUT_LINE('Saturday');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN DBMS_OUTPUT.PUT_LINE('Day number invalid');
END;

CURSOR_ALREADY_OPEN

After Opening and fetching rows from an explicit cursor, you must close it. If an explicit cursor is open and you attempt to open such a cursor the CURSOR_ALREADY_OPEN will be raised. A cursor FOR loop opens an explicit cursor, fetches the records till end and closes it automatically. If your code tries to open the cursor in the Cursor FOR loop you will get this error raised.

DECLARE
CURSOR c_empl IS SELECT first_name, last_name FROM employees WHERE department_id=30;
BEGIN
Open c_empl;
open c_empl;
EXCEPTION 
WHEN CURSOR_ALREADY_OPEN THEN DBMS_OUTPUT.PUT_LINE('This cursor is already open');
END;

INVALID_CURSOR

When you try to open a wrongly defined explicit cursor or close an unopened cursor, this Exception is raised.

DECLARE 
CURSOR c_empl IS SELECT first_name, last_name FROM employees WHERE department_id=30;
BEGIN
Open c_empl;
CLOSE c_empl;
CLOSE c_empl;
EXCEPTION 
WHEN INVALID_CURSOR THEN DBMS_OUTPUT.PUT_LINE('invalid cursor. Cursor may be closed or non existant ');
END;

These are the most commonly used built-in exceptions. You can check more PL/SQL built-in exceptions here.

admin

PL/SQL Built-in Exceptions