Error and Exception Handling In Oracle PL/SQL

In this article, I’ll demonstrate how your Oracle PL/SQL programs can use errors and exception handlers in Oracle and how to write exception handlers to handle exceptions. PL/SQL exception and how to write exception handler to handle exceptions.

What Is an Exception?

In PL/SQL, an Exception is a warning or error condition called an exception. Exceptions can be internally defined (by the run-time system) or user-defined. Errors can be classified as described in the below table. Exceptions are designed for run-time error handling, rather than compile-time error handling. Errors that occur during the compilation phase are detected by the PL/SQL engine and reported back to the user. The program cannot handle these, since the program has not yet run. There are two types of exceptions: user-defined and predefined.

User-Defined Exceptions: A user-defined exception is an error that the programmer defines. User-defined exceptions are declared in the declarative section of a PL/SQL block. Just like variables, exceptions have a type (EXCEPTION) and a scope. For example,

DECLARE
e_duplicatePerson EXCEPTION;

Here, e_duplicatePerson is an identifier that will be visible until the end of this block and the scope of an exception is the same as the scope of any other variable or cursor in the same declarative section.

Predefined Exceptions: Oracle has predefined several exceptions that correspond to the most common Oracle errors. It is not necessary to declare them in the declarative section like a user-defined exception. Example – INVALID_CUSROR, ZERO_DIVIDE, INVALID_NUMBER, VALUE_ERROR.

Raising Exceptions

When the error associated with an exception occurs, the exception is raised. User-defined exceptions are raised explicitly via the RAISE statement, while predefined exceptions are raised implicitly when their associated Oracle error occurs.

DECLARE
e_duplicatePerson EXCEPTION;
-- IDs for three authors
v_person1 books.author1%TYPE;
v_person2 books.author2%TYPE;
BEGIN
SELECT person1, person2
INTO v_person1, v_person2
FROM per_person_f
WHERE title = 'Manager';
/* Ensure that there are no duplicates */
IF (v_person1 = v_person2) THEN
RAISE e_duplicatePerson;
END IF;
END;

Handling Exceptions

When an exception is raised, control passes to the exception section of the block. An exception handler contains the code that is executed when the error associated with the exception occurs, and the exception is raised. Here is the basic syntax of the exception-handling section:

BEGIN
    -- executable section
    ...
    -- exception-handling section
    EXCEPTION 
        WHEN exception_name1 THEN 
            -- exception_handler1
        WHEN exception_name2 THEN 
            -- exception_handler1
        WHEN OTHERS THEN
            -- other_exception_handler
END;

In this example exception_name1 and exception_name2 are exceptions. If the exception exception_name1 occurred, the exception_handler1 runs. If the exception exception_name2 occurred, the exception_handler2 executes. In case any other exception raises, then the other_exception_handler runs.

Examining the Error Stack: Although only one exception can be raised at a time, the actual error message text SQLCODE returns the current error code, and SQLERRM returns the current error message text. For a user-defined exception, SQLCODE returns 1, and SQLERRM returns “User-defined Exception”.

PL/SQL TOO_MANY_ROWS exception example

Consider the persons table has the following data:

DECLARE
v_fname VARCHAR2 (15);
BEGIN
SELECT last_name INTO v_fname
FROM persons
WHERE last_name = 'User';
 DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_fname);
END;
/

We know there are 3 persons with a last name as ‘User’ so the above code fails. Below is the error code:

Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example:

set serveroutput on;
DECLARE
v_fname VARCHAR2 (15);
BEGIN
SELECT last_name INTO v_fname
FROM persons
WHERE last_name = 'User';
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple rows. Consider using a cursor.');
END;
/

This is how you can handle exceptions very easily in PL/SQL.

That’s all about this article.

Request you to subscribe to my YouTube channel for regular updates.

Further readings:

What Is an Autonomous Database?

How to create a free tier ATP connection

Different types of Loops in Oracle PL/SQL

Creating schema and adding roles in ATP