2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Named Programmer-Defined Exceptions

WHAT is a named PROGRAMMER-defined EXCEPTION in ORACLE?
userimage

Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.

Syntax:

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];

 

Example:

 

CREATE OR REPLACE PROCEDURE Order_sales

   (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;

Answer is