PL/SQL Exceptions

Sharing is Caring

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;

 

PL/SQL Exceptions

 

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

Related Posts

An Oracle ACE, Blogger, Reviewer, Technical Lead working on Oracle ADF

Leave a Reply

Your email address will not be published. Required fields are marked *