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
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;
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;
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;
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;
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;
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;
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;
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.