Exceptions in DBMS

Introduction

Suppose we have written a procedure to perform an arithmetic division. The procedure will input two numbers and they are divided and the result will be displayed. There is no check on the numbers that the user enters. He can enter any positive or negative numbers. This procedure will work as expected until user enters positive or negative numbers. But what will happen when he enters second number as zero, which is a divisor?

CREATE OR REPLACE SP_DIVIDE (num1 NUMBER, num2 NUMBER)

IS
	n_result NUMBER;

BEGIN
	n_result:= num1 / num2;
	DBMS_OUT.PUTLINE (num1||’/’ || num2 || ‘=’ || n_result);
END;

Above procedure will fail to when we input num2 = 0. But this is not expected by the user. User do not like programs failing for the errors in the code. If we had done error handling, even if there was a failure, user will see the proper error message or he would be asked to input correct value. Here in this case, we know that it will fail only when we input num2 as zero. But there would be some programs where we will not know for what values/conditions the program will fail. What could be done in such case? Proper error handling should be introduced in the programs and such method is called exceptions.

Exceptions

Exceptions are the method of handling the errors that occur during the execution of the programs. These errors are the results of data values that occur as a result of program execution. The developer will not know in prior where and when the error can occur. But he will have an idea that where error might occur. In such cases, he adds exceptions to handle the program so that it does not fail, but it should display proper message or alternative method to execute the program without failing it. Exception may contain a message to the user or block of code which will be executed in case of failure.

All the error handling methods are catched when normal execution of the code and handled in a separate block called exception. A normal program/ procedure/ function will have following structure

DECLARE
----- Declaration Section -----
BEGIN
-----Executable Code -----
EXCEPTION
----- Error handling Section -----
End;

We can define as many exceptions as we need inside the exception block above. Let us see the types of exception and to specify them in the next sessions below.

Above example procedure for division can be re-written as below with exception.

CREATE OR REPLACE SP_DIVIDE (num1 NUMBER, num2 NUMBER)
IS
	n_result NUMBER;
BEGIN
	n_result:= num1 / num2;

	DBMS_OUT.PUTLINE (num1||’/’ || num2 || ‘=’ || n_result);
EXCEPTION
	WHEN ZERO_DIVIDE THEN
		DBMS_OUT.PUTLINE (‘Cannot divide by Zero!’);
END;

Here we can see that code for dividing is written in a normal way. There is no error handling methods are specified. But if there is any error while dividing, say num2 = 0, then it will stop the normal execution and jump to Exception block. There it checks for the name ‘ZERO_DIVIDE’ which is system defined exception meant for handling division by zero. We have given a message when such error occurs. This is how the exception works.

Types of Exception in DBMS

There are two types of exception

1. System-defined exceptions

2. User-defined exceptions

System-defined exceptions

There are two types of System defined exceptions – Named System exceptions and Un-named System exceptions.

• Named System exceptions – These are the predefined exceptions created by the SQL to handle the known types of errors in the code. They are also known as named exceptions. They are defined by the SQL and need not be redefined by the user. These exceptions need to be handled in the exception block. But they will be raised automatically by the SQL when such error occurs in the code. For example, ‘ZERO_DIVIDE’ is the predefined exception to handle division by zero.

Few of the pre-defined named exceptions are given below.

• Un-named System exceptions: – These exceptions are similar to named exceptions but do not have predefined names. They have predefined error numbers and error code/message. These errors do not occur as frequently as named exceptions.

There are two ways to call these exceptions in the program

1. Use WHEN OTHERS THEN: – we have seen how a named exception are called in the program. Similarly, we use OTHERS to point to all other exceptions other than named exceptions. It handles all the exceptions which are not handled in the exception block. Hence it always written at the end of the exception block.

CREATE OR REPLACE SP_DIVIDE (num1 NUMBER, num2 NUMBER)
IS
	n_result NUMBER;
BEGIN
	n_result:= num1 / num2;

	DBMS_OUT.PUTLINE (num1||’/’ || num2 || ‘=’ || n_result);
EXCEPTION
	WHEN ZERO_DIVIDE THEN
		DBMS_OUT.PUTLINE (‘Cannot divide by Zero!’);
	WHEN OTHERS THEN
		DBMS_OUT.PUTLINE (‘Error has occurred while executing the code’);
END;

2. In the second method, a name is associated to the error code and then calls it as a named exception. A name to the error code is associated by using PRAGMA called EXCEPTION_INIT. A developer can choose any name to such exceptions. These exceptions are raised implicitly and should be handled in WHEN OTHERS. If not, then they have to be initiated by using EXCEPTION_INIT and can be handled by using the names that is defined while initializing.

The general syntax for this type of exception is as below.

DECLARE 
   exception_name EXCEPTION; -- declare a exception variable
   PRAGMA 
   EXCEPTION_INIT (exception_name, ERR_CODE); -- Associate error code to exception name
BEGIN 
-----Executable Code -----
EXCEPTION
  WHEN exception_name THEN
      -----Executable Code -----
END;

Suppose we have to delete a department 10, for which employees exists in the Employee table. The code should break because there are still child records for the department. Oracle defines error code ORA-02292 for the integrity constraint violation.

DECLARE 
   exp_child_rec EXCEPTION; -- declare a exception variable
   PRAGMA 
   EXCEPTION_INIT (exp_child_rec, -02292);--Associate error code to exp_child_rec
BEGIN 
DELETE FROM DEPARTMENT WHERE DEPT_ID = 10;
EXCEPTION
  WHEN exp_child_rec THEN
	DBMS_OUT.PUTLINE (‘Child records for the DEPARTMENT still exists’);
	DBMS_OUT.PUTLINE (ERR_CODE || ‘– ‘|| ERR_MSG);
END;

User-defined exceptions

These exceptions are defined by the developers based on the business rules. These exceptions are raised and handled similar to the unnamed exceptions, but they have to be raised explicitly. They have to be declared and assigned error code and names, and then it will be handled in the exception block by calling the names as we do it in other exceptions. The error codes from -20000 and -20999 are used to define user defined error codes and are used to handle the user defined exceptions.

Suppose, we have to check for number of students in a class and if it is more than 30, then we have to raise a message.

DECLARE 
   exp_std_limit EXCEPTION; 
   l_std_count NUMBER;
BEGIN 
SELECT COUNT (1) 
	INTO l_std_count 
FROM STUDENT
WHERE CLASS_ID = ‘DESIGN_12’
GROUP BY CLASS_ID;
IF l_std_count > 30 THEN
	RAISE exp_std_count;
ELSE
	DBMS_OUT.PUTLINE (‘Number of Students in class DESIGN_12 is: ’|| l_std_count);
END IF;
EXCEPTION
  WHEN exp_std_count THEN
	DBMS_OUT.PUTLINE (‘Number of students in the class DESIGN_12 is more than limit’);
END;

In the above code we can see RAISE command is used to explicitly call the exception. It does not associate any error code or error message to the exception. It simply handles the error.

In case we have to have error code and message with this user defined exception, then we will use RAISE_APPLICATION_ERROR procedure. This is a built in procedure used to associate user defined error message to error code. Developer can use error codes from -20000 and -20999. This procedure raises the exception, but it does not handle the exception. This procedure automatically rollbacks all the transaction which are not committed in the execution block.

Above example can be rewritten using RAISE_APPLICATION_ERROR and associate a error code and user defined error message as below:

DECLARE 
   exp_std_limit EXCEPTION; 
   l_std_count NUMBER;
BEGIN 
SELECT COUNT (1) 
	INTO l_std_count 
FROM STUDENT
WHERE CLASS_ID = ‘DESIGN_12’
GROUP BY CLASS_ID;
IF l_std_count > 30 THEN
	RAISE exp_std_count;
ELSE
	DBMS_OUT.PUTLINE (‘Number of Students in class DESIGN_12 is: ’|| l_std_count);
END IF;
EXCEPTION
  WHEN exp_std_count THEN
	raise_application_error (-2010, ‘Number of students in the class DESIGN_12 is more than limit’);
END;

Advantages of Exceptions

• It executes the program without interrupting the normal execution.
• If there are no exceptions, in each step of normal execution error handling method has to be added, making program lengthier and confusing to any new developer. For example, in above procedure of division, we have had to add a block of code to check if num2 is zero or not. If zero, then do not divide, else divide. This distracts the developer when he is rechecking the code. (this code may be simple to understand, but imagine a lengthy code where in each step such checks are involved. It makes difficult to understand the intension of code).