Learn How To Handle Exceptions In PL/SQL

If you are a programmer, you might be familiar with the concept of exception handling is an integral part of any programming language. As errors are inevitable and even the smartest of us can make mistakes while writing code, we must be acquainted with how to handle them. In this article, we will be learning particularly about the exception handling in PL/SQL.

Below are the topics covered in this article :

• What is an Exception?
• Syntax of Exception Handling
• Types of Exceptions
  • System defined
    • Named system exceptions
    • Unnamed system exceptions
  • User-defined
    • Steps to declare User-defined functions
    • Examples of User-defined functions

What is an Exception?

Any abnormal condition or event that interrupts the normal flow of our program instructions at run time or in simple words an exception is an error.

Syntax of Exception Handling in PL/SQL

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2  THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........

WHEN others THEN
exception3-handling-statements
END;

Here, we can list down as many exceptions as we want to handle. The default exception will be handled using ‘WHEN others THEN’

Example of Exception Handling in PL/SQL

The below program displays the name and address of a student whose ID is given. Since there is no student with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

DECLARE 
   s_id studentS.id%type := 8; 
   s_name studentS.Name%type; 
   s_loc studentS.loc%type; 
BEGIN 
   SELECT  name, loation INTO  s_name, s_loc 
   FROM students 
   WHERE id = s_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  s_name); 
   DBMS_OUTPUT.PUT_LINE ('Location: ' || s_loc);
EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such student!'); 
   WHEN others THEN 
      dbms_output.put_line('Oops, Error!'); 
END; 

Output

No such student!
PL/SQL procedure successfully completed.  

Here, we can list down as many exceptions as we want to handle. The default exception will be handled using ‘WHEN others THEN’

Types of Exceptions in PL/SQL

• System defined
• User defied

Next in this article on exception handling in PL/SQL, let us discuss about both of these types in detail.

System defined

Defined and maintained implicitly by the Oracle server, these exceptions are mainly defined in the Oracle Standard Package. Whenever an exception occurs inside the program,  Oracle server matches and identifies the appropriate exceptions from the available set of exceptions available in the oracle standard package. Basically, these exceptions are predefined in PL/SQL which gets raised WHEN a particular database rule is violated.

The System-defined exceptions are further divided into two categories:

• Named system exceptions
• Unnamed system exceptions

Named system Exceptions

The named PL/SQL exceptions are named in the standard package of PL/SQL, hence the developer does not need to define the PL/SQL exceptions in their code. PL/SQL provides many pre-defined named exceptions, which are executed when any database rule is violated by a program. The following table lists a few of the important pre-defined exceptions −

Examples

CREATE OR REPLACE PROCEDURE add_new_student
      (student _id_in IN NUMBER, student _name_in IN VARCHAR2)
IS
BEGIN
       INSERT INTO student (student _id, student _name )
       VALUES ( student _id_in, student _name_in );
EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
 raise_application_error (-20001,'Duplicate student _id');
       WHEN OTHERS THEN
raise_application_error (-20002,'An error occurred.');
END;

Moving on in this article on exception handling in PL/SQL, let us understand what are unnamed system exceptions.

Unnamed System Exceptions

The system exceptions for which Oracle does not have a name are known as unnamed system exceptions. These exceptions do not occur frequently and are written with a code and an associated message.

There are basically two ways to handle unnamed system exceptions:

1. Using the WHEN OTHERS exception handler

2. Associating the exception code to a name and using it as a named exception.

Some steps followed for unnamed system exceptions are:

• Raise them implicitly.
• In case they are not handled in ‘WHEN Others’ then, they have to be handled explicitly.
• To handle the exception explicitly, they can be declared using Pragma EXCEPTION_INIT and handled by referencing the user-defined exception name in the exception section.

An example of handling unnamed exceptions using Pragma EXCEPTION_INIT is provided later in the article. Moving on in this article on exception handling in PL/SQL, let us understand the User-defined excetpions.

Discover the secrets of efficient data management through our SQL Course Online.

User-defined

Like all other programming languages, Oracle also allows you to declare ad implement your own exceptions. Unlike System defined exceptions, these exceptions are raised explicitly in the PL/SQL block.

Steps to declare User-defined exceptions in the Oracle database

We can define User-defined exceptions in Oracle database in the following 3 ways:

• Using Variable of EXCEPTION type

Here, we can declare a User-defined exception by declaring a variable of EXCEPTION datatype in our code and raise it explicitly in our program using RAISE statement.

• Using PRAGMA EXCEPTION_INIT function

We can define a non-predefined error number with the variable of EXCEPTION datatype

• Using RAISE_APPLICATION_ERROR method

Using this method, we can declare a User-defined exception with our own customized error number and message.

Till now you might have got a rough idea on the ways in which we can raise User-defined exceptions in PL/SQL. We will learn about each of the above-mentioned methods with examples further in this article on exception handling in PL/SQL.

Next in this article, let us proceed with the demonstrations of User-defined exception handling.

Demonstration of User-defined Exceptions

Moving on in this article on Exception Handling in PL/SQL, let us understand how to use the variable of EXCEPTION type.

Using Variable of EXCEPTION type

The process of declaring user-defined exception is divided into three parts and these 3 parts are:

• Declare a variable exception datatype
• Raise the Exception
• Handle the Exception

Let’s write a code to demonstrate the above steps in detail.

DECLARE
       var_dividend NUMBER :=10;
       var_divisor  NUMBER :=0
       var_result NUMBER;
       ex-DivZero EXCEPTION

In the above declaration block, we have four variables, among which the first three are normal number datatype variables and the fourth one which is ex_DivZero is the special exception datatype variable. The fourth one is our user-defined exception.

DECLARE
       var_dividend NUMBER :=10;
       var_divisor  NUMBER :=0
       var_result NUMBER;
       ex-DivZero EXCEPTION

The above execution part of this anonymous block, will come into action only when the divisor is 0. If the divisor is zero as it is in our case, the error will be raised and the control of the program will skip all the next steps and  will look for matching exception handler. In the case where it finds any other, it will perform the action accordingly, otherwise it will either terminate the program or prompt us with an unhandled system defined error.

EXCEPTION WHEN ex_DivZero THEN
DBMS_OUTPUT.PUT_LINE(‘ ERROR, The divisor can’t be zero’);

This the exception handler. As soon as the user enter divisor as 0, the above message string will be prompted.

Final Code:

DECLARE
       var_dividend NUMBER :=10;
       var_divisor  NUMBER :=0
       var_result NUMBER;
       ex-DivZero EXCEPTION
BEGIN
IF var_divisor =0 THEN
RAISE ex-DivZero;
END IF;
Var_result := var_dividend/var_divisor;
DBMS_OUTPUT.PUT_LINE (‘Result = ‘ || var_result);
BEGIN
IF var_divisor =0 THEN
RAISE ex-DivZero;
END IF;
Var_result := var_dividend/var_divisor;
DBMS_OUTPUT.PUT_LINE (‘Result = ‘ || var_result);
END;

Moving on in this article on exception handling in PL/SQL, let us understand how to use the PRAGMA_EXCEPTION_INIT method.

Using PRAGMA EXCEPTION_INIT function

In the PRAGMA EXCEPTION_INIT function,  an exception name is associated with an Oracle error number. This name can be used in designing the exception handler for the error. For huge projects with many user defined errors, PRAGMA EXCEPTION_INIT  is the most useful and suitable method.

Syntax:

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

Example

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
 NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      NULL; -- handle the error
END;

The PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number as mentioned earlier. It  lets you refer to any internal exception by name and write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that can be trapped and handled.

Moving on in this article on Exception Handling in PL/SQL, let us understand how to use the RAISE_APPLICATION_ERROR method.

Using RAISE_APPLICATION_ERROR method

It’s a procedure that comes inbuilt with the oracle software. Using this procedure we can associate an error number with a custom error message. Combining both the error number and the custom error message, an error string can be composed which looks similar to those default error strings which are displayed by oracle when an error is encountered. RAISE_APPLICATION_ERROR procedure is found inside DBMS_STANDARD package

Syntax

raise_application_error (error_number, message [, {TRUE | FALSE}]);

Example

/* A trigger trg_emp_detail_chk is created.*/
CREATE OR REPLACE TRIGGER trg_emp_detail_chk

/* The trigger timing is declared as BEFORE UPDATE on the EMPLOYEES table.*/
Before UPDATE ON employees

DECLARE
permission_denied EXCEPTION;
BEGIN

/*Start of the IF condition checking whether the day of the system time is either Saturday or Sunday or not.*/
IF trim(TO_CHAR(sysdate,'Day')) IN ('Saturday', 'Sunday') THEN
raise_application_error(-20000, 'You are not authorized to do any modification in the weekends!!');

/* The procedure raise_application_error is called with the first parameter value as -20000 and the second parameter
with a default text stating that the user is not authorized to do any modification in the weekends. */
END IF;
END;

With this we come to an end of this article on “Exception handling in PL/SQL”. I hope this topic is understood well and helped you. Try to write your own codes and incorporate the methods explained in this article.

If you want to get trained from professionals on this technology, you can opt for structured training from edureka! Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions etc. End of the training you will be able to create and administer your own MySQL Database and manage data.

Got a question for us? Please mention it in the comments section of this “Exception Handling in PL/SQL” article and we will get back to you as soon as possible.