Table of contents
1.
Introduction
2.
Raising Exceptions
3.
User-defined Exceptions
4.
Example of exception handling
5.
Pre-defined Exceptions
6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL - Exception Handling

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

An exception in PL/SQL is an error that occurs during the program's execution.

The exception block in PL/SQL allows programmers to capture such occurrences, taking appropriate action against the error condition.

There are two types of exceptions: 

  • System-defined Exceptions
  • User-defined Exceptions

Raising Exceptions

Exceptions are raised automatically by the database server in the event of any internal database fault. However, the programmer can explicitly raise it by using the RAISE command.

The following is the syntax for raising an exception:

WHEN exception THEN
         statement;
DECLARE  
   exception_name EXCEPTION;  
BEGIN  
   IF condition THEN  
      RAISE exception_name;  
   END IF;  

EXCEPTION  
   WHEN exception_name THEN  
   statement;  
END;  
/

User-defined Exceptions

PL/SQL allows users to define their exceptions based on the program's requirements. A RAISE statement or the function DBMS_STANDARD.RAISE_APPLICATION_ERROR can be used to raise a user-defined exception explicitly.

User-defined exceptions have a specific syntax.

DECLARE  
my-exception EXCEPTION;

Example of exception handling

To demonstrate the concept of exception handling, let's look at a basic example. We will use the STUDENTS table, which has already been created.

SELECT* FROM STUDENTS;

ID NAME AGE ADDRESS MARKS
1 Dinesh  22 Ahmedabad 80
2 Ramesh 19 Pune 74
3 Mukesh 22 Goa 45
4 Suresh 21 Chennai 34
5 Amisha 20 Delhi 78
6 Anushka 20 Delhi 88
DECLARE  
   s_id students.id%type;  
   s_name  students.name%type;  
   s_addr students.address%type:= ‘Mumbai’;  

BEGIN  
   SELECT name, address INTO  s_name, s_addr  
   FROM students 
   WHERE address = s_addr;  

 DBMS_OUTPUT.PUT_LINE ('Name: '||  s_name);  
 DBMS_OUTPUT.PUT_LINE ('Address: ' || s_addr);  

EXCEPTION  
   WHEN no_data_found THEN  
      dbms_output.put_line('No such student!');  
   WHEN others THEN  
      dbms_output.put_line('Error!');  
END;  
/   

You should obtain the following result after running the above code at the SQL prompt:

No such student!
PL/SQL procedure completed.

The above programs should display the name and address whose address has been provided. The software throws the run-time exception NO DATA FOUND, which is captured in the EXCEPTION block because no student has the address value ‘Mumbai’ in our database.

We will obtain a specific result if we utilize the address defined in the above table. For example, we'll use the address ‘Ahmedabad’.

DECLARE  
   s_id students.id%type;  
   s_name  students.name%type;  
   s_addr students.address%type := ‘Ahmedabad’;  

BEGIN  
   SELECT name, address INTO  s_name, s_addr  
   FROM students 
   WHERE address = s_addr;  

 DBMS_OUTPUT.PUT_LINE ('Name: '||  s_name);  
 DBMS_OUTPUT.PUT_LINE ('Address: ' || s_addr);  

EXCEPTION  
   WHEN no_data_found THEN  
      dbms_output.put_line('No such student!');  
   WHEN others THEN  
      dbms_output.put_line('Error!');  
END;  
/   

You should obtain the following result after running the above code at the SQL prompt:

Name: Dinesh
Address: Ahmedabad
PL/SQL procedure completed.

Recommended topics, DCL Commands in SQL and Tcl Commands in SQL

Pre-defined Exceptions

Several pre-defined exceptions are triggered when the applications break a database rule.

For example, when a SELECT INTO command produces no rows, a pre-defined exception called NO_DATA_FOUND is thrown.

Here's a summary of several pre-defined exceptions:

Exception

Oracle Error

SQL Code

Description

ACCESS_INTO_NULL

06530

-6530

This exception is triggered when a NULL object is automatically allocated a value.

CASE_NOT_FOUND

06592

-6592

It is triggered when none of the options in a CASE statement's "WHEN" clauses are picked, and there is no else clause.

COLLECTION_IS_NULL

06531

-6531

It is raised when a program tries to apply collection methods to an uninitialized nested table or varray that does not exist.

DUP_VAL_ON_INDEX

00001

-1

This error is raised when duplicate values are stored in a column with a unique index.

INVALID_CURSOR

01001

-1001

It is raised when an unallowable cursor operation is attempted, such as closing an unopened cursor.

INVALID_NUMBER

01722

-1722

When converting a character string to a number fails due to the string not representing a valid number, this exception is raised.

LOGIN_DENIED

01017

-1017

It's triggered when a program tries to access the database with an incorrect username or password.

NO_DATA_FOUND

01403

+100

When a select into command returns no rows, this error is raised.

NOT_LOGGED_ON

01012

-1012

This exception is generated when a database call is made without connecting to the database.

PROGRAM_ERROR

06501

-6501

When PL/SQL has an internal fault, this exception is triggered.

ROWTYPE_MISMATCH

06504

-6504

This exception is raised when a cursor retrieves a value from a variable with an incompatible data type.

SELF_IS_NULL

30625

-30625

This error is thrown when a member method is called, but the object type instance isn't initialized.

STORAGE_ERROR

06500

-6500

This exception is thrown when PL/SQL runs out of memory or is damaged.

TOO_MANY_ROWS

01422

-1422

This exception is thrown when a SELECT INTO command returns more than one row.

VALUE_ERROR

06502

-6502

This exception is raised when the arithmetic, conversion, truncation, or size-constraint error occurs.

ZERO_DIVIDE

01476

1476

When attempting to divide an integer by zero, this exception is raised.

FAQs

  1. What is exception handling in PL/SQL?
    An exception is an error that causes the normal flow of instructions. The exception block in PL/SQL raises the exception, assisting the programmer in locating and resolving the problem.
     
  2. What are the different exceptions defined in PL/SQL?
    In PL/SQL, there are two types of exceptions:
    User-defined exception.
    System-defined exceptions.
     
  3. What is the general syntax to write an exception?
    The following is the syntax for raising an exception:
WHEN exception THEN 
    statement;

Key Takeaways

In this blog, we have learned that an Exception is an error that happens during the execution of a program. We have seen types of exception handling and learned how to raise an exception and its syntax with examples of how to use them.

Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass