Using Named Programmer-Defined Exceptions in Oracle

This Oracle tutorial explains how to use Named Programmer-Defined Exceptions in Oracle/PLSQL with syntax and examples.

What is a named programmer-defined exception in Oracle?

Sometimes, it is vital for programmers to identify and lure their personal exceptions – ones that are not defined already by means of PL/SQL. These are referred to as Named Programmer-Defined Exceptions.

Syntax

We will take a look at the syntax for Named Programmer-Defined Exceptions in both methods and functions.

Syntax for Procedures

The syntax for the Named Programmer-Defined Exception in a system is:

CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter [,parameter]) ]
IS
   [declaration_section]

   exception_name EXCEPTION;

BEGIN
   executable_section
   RAISE exception_name;

EXCEPTION
   WHEN exception_name THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];

Syntax for Functions

The syntax for the Named Programmer-Defined Exception in a feature is:

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype

IS | AS
   [declaration_section]

   exception_name EXCEPTION;

BEGIN
   executable_section

   RAISE exception_name;

EXCEPTION
   WHEN exception_name THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [function_name];

Example

Here is an instance of a system that makes use of a Named Programmer-Defined Exception:

CREATE OR REPLACE PROCEDURE add_new_order
   (order_id_in IN NUMBER, sales_in IN NUMBER)
IS
   no_sales EXCEPTION;

BEGIN
   IF sales_in = 0 THEN
      RAISE no_sales;

   ELSE
      INSERT INTO orders (order_id, total_sales )
      VALUES ( order_id_in, sales_in );
   END IF;

EXCEPTION
   WHEN no_sales THEN
      raise_application_error (-20001,'You must have sales in order to submit the order.');

   WHEN OTHERS THEN
      raise_application_error (-20002,'An error has occurred inserting an order.');

END;

In this example, we have declared a Named Programmer-Defined Exception called no_sales in our assertion assertion with the following code:

no_sales EXCEPTION;

We’ve then raised the exception in the executable area of the code:

IF sales_in = 0 THEN
   RAISE no_sales;

Now if the sales_in variable consists of a zero, our code will jump immediately to the Named Programmer-Defined Exception called no_sales.

Finally, we inform our technique what to do when the no_sales exception is encountered via consisting of code in the WHEN clause:

WHEN no_sales THEN
   raise_application_error (-20001,'You must have sales in order to submit the order.');

We are also the use of the WHEN OTHERS clause to lure all ultimate exceptions:

WHEN OTHERS THEN
   raise_application_error (-20002,'An error has occurred inserting an order.');