WHEN OTHERS CLAUSE in PLSQL Exceptions:-
WHEN OTHERS CLAUSE in PLSQL Exceptions is used to capture an exception that’s not handled by our Named System Exception or Named Programmer Defined Exception.
The plsql syntax for using WHEN OTHERS Clause in an 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 using WHEN OTHERS Clause in an Exception within 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 WHEN OTHERS Clause 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 DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'Duplicate Employee_Id.');
WHEN no_salary THEN
raise_application_error (-20001,'Salary can’t be 0.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error occurred.');
END;
In the example of plsql exception handling above, if an exception is encountered that is not DUP_VAL_ON_INDEX or a no_salary exception, then it will be trapped by the PLSQL WHEN OTHERS Clause.