Signup/Sign In

Exception Handling in PL/SQL

Exceptions are runtime errors or unexpected events that occur during the execution of a PL/SQL code block.

The oracle engine is the first one to identify such an exception and it immediately tries to resolve it by default exception handler.

The default exception handler is a block of code predefined in the memory to take the appropriate action against exceptions.

Exception handling can be done in the EXCEPTION part of PL/SQL program code block.

Following is the syntax for it:

	-- Declaration statements;
	-- SQL statements;
	-- Procedural statements;
	-- Exception handling statements;

There are two types of exceptions:

  1. System (pre-defined) Exceptions
  2. User-defined Exceptions

Let's cover both types of exceptions one by one.

System (pre-defined) Exceptions

In order to handle common exceptions that occur while running PL/SQL code, there are two types of exception handlers in oracle:

  • Named Exception Handler
  • Numbered Exception Handler

Named Exception Handling

Such exceptions are the predefined names given by oracle for those exceptions that occur most commonly.

Following is the syntax for handling named exception:

	WHEN <exception_name> THEN
		-- take action			

There are number of pre-defined named exceptions available by default. Few of them are shown in the table below, along with their meanings:

Named Exception Meaning
LOGIN_DENIED Occurs when invalid username or invalid password is given while connecting to Oracle.
TOO_MANY_ROWS Occurs when select statement returns more than one row.
VALUE_ERROR Occurs when invalid datatype or size is given by the user.
NO_DATA_FOUND Occurs when no records are found.
DUP_VAL_ON_INDEX Occurs when a unique constraint is applied on some column and execution of Insert or Update leads to creation of duplicate records for that column.
PROGRAM_ERROR Occurs when internal error arise in program.
ZERO_DIVIDE Occurs when the division of any variable value is done by zero.

Time for an Example!

Below we have a simple PL/SQL code block, to demonstrate the use of Named Exception Handler,

set serveroutput on;

	a int;
	b int;
	c int;
	a := &a;
	b := &b;
	c := a/b;
	dbms_output.put_line('RESULT=' || c);
	when ZERO_DIVIDE then
		dbms_output.put_line('Division by 0 is not possible');

Enter the value for a:10 Enter the value for b:0 Division by 0 is not possible PL/SQL procedure successfully completed.

Numbered Exception Handling

In oracle, some of the pre-defined exceptions are numbered in the form of four integers preceded by a hyphen symbol. To handle such exceptions we should assign a name to them before using them.

This can be done by using the Pragma exception technique in which a numbered exception handler is bound to a name. For this purpose, we use a keyword in PL/SQL program and write a statement that binds a name to a numbered exception using the following syntax and this statement is written in the DECLARE section of program:

pragma exception_init(exception_name, exception _number);

where, pragma exception_init(case doesn't matter) is a keyword indicating Pragma exception technique with two arguments:

  • exception_name, which is a user-defined name given to a predefined numbered exception if it occurs.
  • exception_number, is the number allotted to the exception by oracle.

Time for an Example!

Below we have a table with Student's data in it.

11 Anu 20 BSC
12 Asha 21 BCOM
13 Arpit 18 BCA
14 Chetan 20 BCA
15 Nihal 19 BBA

In the PL/SQL program below, we will be using the above table student to demonstrate the use of Numbered Exception,

set serveroutput on;

	sno student.rollno%type;
	snm student.sname%type;
	s_age student.age%type;
	cr student.course%type;
	-- Exception name declared below
	already_exist EXCEPTION;
	-- pragma statement to provide name to numbered exception
	pragma exception_init(already_exist, -1);
	INSERT into student values(sno, snm, s_age, cr);
	dbms_output.put_line('Record inserted');
		WHEN already_exist THEN
			dbms_output.put_line('Record already exist');

Enter the value for sno:11 Enter the value for snm:heena Enter the value for s_age:20 Enter the value for cr:bsc Record already exist PL/SQL procedure successfully completed.

In the above program, whenever a primary key concept(records should be unique and not null) is violated oracle generates a numbered exception by -1 and that is why when rollno entered by user during execution of above program was 11. The exception section of the program comes into action and message is displayed before the user Record already exist.

Using pragma keyword in the declare section of the program already_exist string is mapped to a numbered exception -1.

User-defined Exception

In any program, there is a possibility that a number of errors can occur that may not be considered as exceptions by oracle. In that case, an exception can be defined by the programmer while writing the code such type of exceptions are called User-defined exception.

User defined exceptions are in general defined to handle special cases where our code can generate exception due to our code logic.

Also, in your code logic, you can explicitly specify to genrate an exception using the RAISE keyword and then handle it using the EXCEPTION block.

Following is the syntax for it,

	<exception name> EXCEPTION
	<sql sentence>
	If <test_condition> THEN 
		RAISE <exception_name>;
		WHEN <exception_name> THEN
			-- some action

Let's take an example to understand how to use user-defined exception. Below we have a simple example,

ROLLNO SNAME Total_Courses
11 Anu 2
12 Asha 1
13 Arpit 3
14 Chetan 1

In the PL/SQL program below, we will be using the above table student to demonstrate the use of User-defined Exception,

set serveroutput on;

	sno student.rollno%type;
	snm student.sname%type;
	crno student.total_course%type;
	invalid_total EXCEPTION;
	sno := &rollno;
	snm := '&sname';
	IF (crno > 3) THEN 
		RAISE invalid_total;
	INSERT into student values(sno, snm, crno);
		WHEN invalid_total THEN
			dbms_output.put_line('Total number of courses cannot be more than 3');

Enter the value for sno:15 Enter the value for snm:Akash Enter the value for crno:5 Total number of courses cannot be more than 3 PL/SQL procedure successfully completed.

In the above program,

User-defined exception called invalid_total is used which is generated when total number of courses is greater than 3(when a student can be enrolled maximum in 3 courses)

And with this we are done with exceptions.