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:
1 | CREATE OR REPLACE PROCEDURE add_employee_salary |
2 | (employee_id_in IN NUMBER, salary_in IN NUMBER) |
3 | IS |
4 | no_salary EXCEPTION; |
5 |
6 | BEGIN |
7 | IF salary_in = 0 THEN |
8 | RAISE no_salary; |
9 |
10 | ELSE |
11 | INSERT INTO employee (employee_id, salary ) |
12 | VALUES ( employee_id_in, salary_in ); |
13 | END IF; |
14 |
15 | EXCEPTION |
16 | WHEN DUP_VAL_ON_INDEX THEN |
17 | raise_application_error (-20001,'Duplicate Employee_Id.'); |
18 |
19 | WHEN no_salary THEN |
20 | raise_application_error (-20001,'Salary can’t be 0.'); |
21 |
22 | WHEN OTHERS THEN |
23 | raise_application_error (-20002,'An error occurred.'); |
24 |
25 | 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.