PL/SQL Exceptions

An error that occurs during execution of the program is called exception, Like other programming languages, PL/SQL offers a way to catch these exceptions and handle them.

There are two types of exceptions in PL/SQL

1. System-Defined Exceptions 
2. User-Defined Exceptions 

There are some pre-defined exceptions in PL/SQL that are raised when the program violates any database rule, As per oracle docs 

Exception	Oracle Code	SQL Code	Description
ACCESS_INTO_NULL	ORA-06530	-6530	Program attempted to assign values to the attributes of an uninitialized object.
CASE_NOT_FOUND	ORA-06592	-6592	None of the choices in the WHEN clauses of a CASE statement were selected and there is no ELSE clause.
COLLECTION_IS_NULL	ORA-06531	-6531	Program attempted to apply collection methods other than EXISTS to an uninitialized nested table or varray, or program attempted to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPENED	ORA-06511	-6511	Program attempted to open an already opened cursor.
DUP_VAL_ON_INDEX	ORA-00001	-1	Program attempted to insert duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR	ORA-01001	-1001	There is an illegal cursor operation.
INVALID_NUMBER	ORA-01722	-1722	Conversion of character string to number failed.
NO_DATA_FOUND	ORA-01403	+100	Single row SELECT returned no rows or your program referenced a deleted element in a nested table or an uninitialized element in an associative array (index-by table).
PROGRAM_ERROR	ORA-06501	-6501	PL/SQL has an internal problem.
ROWTYPE_MISMATCH	ORA-06504	-6504	Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types.
STORAGE_ERROR	ORA-06500	-6500	PL/SQL ran out of memory or memory was corrupted.
SUBSCRIPT_BEYOND_COUNT	ORA-06533	-6533	A program referenced a nested table or varray using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	-6532	A program referenced a nested table or varray element using an index number that is outside the legal range (for example, -1).
SYS_INVALID_ROWID	ORA-01410	-1410	The conversion of a character string into a universal rowid failed because the character string does not represent a ROWID value.
TOO_MANY_ROWS	ORA-01422	-1422	Single row SELECT returned multiple rows.
VALUE_ERROR	ORA-06502	-6502	An arithmetic, conversion, truncation, or size constraint error occurred.
ZERO_DIVIDE	ORA-01476	-1476	A program attempted to divide a number by zero.

PL/SQL Exception Handling

The general syntax for handling the exception in PL/SQL is like this

DECLARE
declare variable here

BEGIN
statements to execute

EXCEPTION

WHEN exception 1 THEN
statements to handle the exception 1

WHEN exception 2 THEN
statements to handle the exception 2
.
.
.
.
WHEN exception n THEN
statements to handle the exception n

.

.

WHEN others THEN
statements to handle the exception

END;

See this example of handling exception

DECLARE 
--Variable Declaration
 a number :=100;
 c number;
BEGIN
-- statements to execute
c:=a/0;
--handle exceptions
EXCEPTION
WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Exception Occured: Divide by zero');
END;

Raising Exceptions

Exceptions are raised by the system if there is an error in program execution and it can be raised explicitly  by the user and syntax is like this

DECLARE 

exceptionname EXCEPTION;

BEGIN 

IF condition THEN 
RAISE exceptionname;
END IF; 

EXCEPTION 

WHEN exceptionname THEN 

statements to handle exception; 

END;

We can use this syntax for the system and user-defined exceptions

User-Defined Exceptions in PL/SQL

PL/SQL allows us to define our own exceptions as per the need of the program, In this example, we'll see how to define and raise an exception using RAISE statement

DECLARE 
--Variable Declaration
 a number :=100;
 b number:=20;
 c number;
 my_exception EXCEPTION;
BEGIN
-- statements to execute
IF b = 0 THEN 
RAISE my_exception;
ELSE
c:=a/b;
dbms_output.put_line('Value of C is- '||c);
END IF;
--handle exceptions
EXCEPTION
WHEN my_exception THEN
dbms_output.put_line('Exception Occured: my_exception- b is zero');
END;

Here the value of b is 20 so the output is

And when the value of b is 0, It raises the exception

Cheers :) Happy Learning