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.');