• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / plsql / Named Programmer Defined Exceptions in Oracle PLSQL

Named Programmer Defined Exceptions in Oracle PLSQL

December 21, 2012 by techhoneyadmin

Oracle PLSQL exception is the condition that terminates the execution of plsql block.

Named Programmer Defined Exception:-

The named programmer defined exceptions are the plsql exceptions that are defined by a programmer to trap specific exceptions hence have been named.

The named programmer defined plsql exceptions are not named in the standard package in plsql and hence the developer needs to define these types of plsql exception.


The plsql syntax for the Named Programmer Defined Exception within a procedure is:

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

BEGIN
–executable_statements

EXCEPTION
WHEN exception_name1 THEN
[exception1_handling_statements]

WHEN exception_name2 THEN
[exception2_handling_statements]

WHEN exception_name_n THEN
[exceptionN_handling_statements]

WHEN OTHERS THEN
[generic_handling_statements]

END [procedure_name];


The plsql syntax for the Named Programmer Defined Exception in a function is:

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

[declaration_section]

BEGIN
–executable_statements

EXCEPTION
WHEN exception_name1 THEN

[exception1_handling_statements]

WHEN exception_name2 THEN
[exception2_handling_statements]

WHEN exception_name_n THEN
[exceptionN_handling_statements]

WHEN OTHERS THEN
[generic_handling_statements]

END [function_name];


Example of plsql exception (named programmer defined exception) handling in a procedure is:

CREATE OR REPLACE PROCEDURE add_employee_salary
   (employee_id_in IN NUMBER, salary_in IN NUMBER)
IS
   no_salary EXCEPTION;

BEGIN
   IF salary_in = 0 THEN
      RAISE no_salary;

   ELSE
      INSERT INTO employee (employee_id, salary )
      VALUES ( employee_id_in, salary_in );
   END IF;

EXCEPTION
  WHEN no_salary THEN
    raise_application_error (-20001,'Salary can’t be 0.');

  WHEN OTHERS THEN
    raise_application_error (-20002,'An error occurred.');
END;

Filed Under: plsql Tagged With: EXCEPTIONHandlingPLSQL, how to handle named programmer defined exception in oracle plsql, named programmer defined exception handling in oracle plsql, Oracle plsql named programmer defined exception handling, what is oracle plsql named programmer defined exception handling

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in