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:

DECLARE
	-- Declaration statements;
BEGIN
	-- SQL statements;
	-- Procedural statements;
EXCEPTION
	-- Exception handling statements;
END;

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:

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:

Time for an Example!

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

set serveroutput on;

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

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.

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

set serveroutput on;

DECLARE
	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);
BEGIN
	sno:=&rollno;
	snm:='&sname';
	s_age:=&age;
	cr:='&course';
	INSERT into student values(sno, snm, s_age, cr);
	dbms_output.put_line('Record inserted');
	EXCEPTION
		WHEN already_exist THEN
			dbms_output.put_line('Record already exist');
END;

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,

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

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

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;

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

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.