Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Exception

PL/SQL Exception

Summary: in this tutorial, you will learn about PL/SQL exception and how to write exception handler to handle exceptions.

Introduction to PL/SQL Exceptions

PL/SQL treats all errors that occur in an anonymous block, procedure, or function as exceptions. The exceptions can have different causes such as coding mistakes, bugs, even hardware failures.

It is not possible to anticipate all potential exceptions, however, you can write code to handle exceptions to enable the program to continue running as normal.

The code that you write to handle exceptions is called an exception handler.

A PL/SQL block can have an exception-handling section, which can have one or more exception handlers.

Here is the basic syntax of the exception-handling section:

BEGIN -- executable section ... -- exception-handling section EXCEPTION WHEN e1 THEN -- exception_handler1 WHEN e2 THEN -- exception_handler1 WHEN OTHERS THEN -- other_exception_handler END;

In this syntax, e1, e2 are exceptions.

When an exception occurs in the executable section, the execution of the current block stops and control transfers to the exception-handling section.

If the exception e1 occurred, the exception_handler1 runs. If the exception e2 occurred, the exception_handler2 executes. In case any other exception raises, then the other_exception_handler runs.

After an exception handler executes, control transfers to the next statement of the enclosing block. If there is no enclosing block, then the control returns to the invoker if the exception handler is in a subprogram or host environment (SQL Developer or SQL*Plus) if the exception handler is in an anonymous block.

If an exception occurs but there is no exception handler, then the exception propagates, which we will discuss in the unhandled exception propagation tutorial.

PL/SQL exception examples

Let’s take some examples of handling exceptions.

PL/SQL NO_DATA_FOUND exception example

The following block accepts a customer id as an input and returns the customer name :

DECLARE l_name customers.NAME%TYPE; l_customer_id customers.customer_id%TYPE := &customer_id; BEGIN -- get the customer name by id SELECT name INTO l_name FROM customers WHERE customer_id = l_customer_id; -- show the customer name dbms_output.put_line('Customer name is ' || l_name); END; /

If you execute the block and enter the customer id as zero, Oracle will issue the following error:

ORA-01403: no data found

The ORA-01403 is a predefined exception.

Note that the following line does not execute at all because control transferred to the exception handling section.

dbms_output.put_line('Customer name is ' || l_name);

To issue a more meaningful message, you can add an exception-handling section as follows:

DECLARE l_name customers.NAME%TYPE; l_customer_id customers.customer_id%TYPE := &customer_id; BEGIN -- get the customer SELECT NAME INTO l_name FROM customers WHERE customer_id = l_customer_id; -- show the customer name dbms_output.put_line('customer name is ' || l_name); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Customer ' || l_customer_id || ' does not exist'); END; /

If you execute this code block and enter the customer id 0, you will get the following message:

Customer 0 does not exist

PL/SQL TOO_MANY_ROWS exception example

First, modify the code block in the above example as follows and execute it:

DECLARE l_name customers.name%TYPE; l_customer_id customers.customer_id%TYPE := &customer_id; BEGIN -- get the customer SELECT name INTO l_name FROM customers WHERE customer_id <= l_customer_id; -- show the customer name dbms_output.put_line('Customer name is ' || l_name); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Customer ' || l_customer_id || ' does not exist'); END; /

Second, enter the customer id 10 and you’ll get the following error:

ORA-01422: exact fetch returns more than requested number of rows

This is another exception called TOO_MANY_ROWS which was not handled by the code.

Third, add the exception handler for the TOO_MANY_ROWS exception:

DECLARE l_name customers.NAME%TYPE; l_customer_id customers.customer_id%TYPE := &customer_id; BEGIN -- get the customer SELECT NAME INTO l_name FROM customers WHERE customer_id > l_customer_id; -- show the customer name dbms_output.put_line('Customer name is ' || l_name); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Customer ' || l_customer_id || ' does not exist'); WHEN TOO_MANY_ROWS THEN dbms_output.put_line('The database returns more than one customer'); END; /

Finally, if you execute the code, enter 10 as the customer id. You will see that the code will not raise any exception and issue the following message:

The database returns more than one customer

PL/SQL exception categories

PL/SQL has three exception categories:

  • Internally defined exceptions are errors which arise from the Oracle Database environment. The runtime system raises the internally defined exceptions automatically. ORA-27102 (out of memory) is one example of Internally defined exceptions. Note that Internally defined exceptions do not have names, but an error code.
  • Predefined exceptions are errors which occur during the execution of the program. The predefined exceptions are internally defined exceptions that PL/SQL has given names e.g., NO_DATA_FOUND, TOO_MANY_ROWS.
  • User-defined exceptions are custom exception defined by users like you. User-defined exceptions must be raised explicitly.

The following table illustrates the differences between exception categories.

CategoryDefinerHas Error CodeHas NameRaised ImplicitlyRaised Explicitly
Internally definedRuntime systemAlwaysOnly if you assign oneYesOptionally
PredefinedRuntime systemAlwaysAlwaysYesOptionally
User-definedUserOnly if you assign oneAlwaysNoAlways

In this tutorial, you have learned about the PL/SQL exceptions and how to write exception handlers to handle the possible exceptions in a block.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL Record
Next PL/SQL Raise Exceptions

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.