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;