Oracle/PLSQL: WHEN OTHERS Clause
What is a WHEN OTHERS clause?
The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by your Named System Exceptions and Named Programmer-Defined Exceptions.The syntax for the WHEN OTHERS clause in a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]
END [procedure_name];
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]
END [procedure_name];
The syntax for the WHEN OTHERS clause in a function is:
CREATE [OR REPLACE] FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]
END [function_name];
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
[declaration_section]
BEGIN
executable_section
EXCEPTION
WHEN exception_name1 THEN
[statements]
WHEN exception_name2 THEN
[statements]
WHEN exception_name_n THEN
[statements]
WHEN OTHERS THEN
[statements]
END [function_name];
Here is an example of a procedure that uses a WHEN OTHERS clause:
CREATE OR REPLACE PROCEDURE add_new_orderIn this example, if an exception is encountered that is not a DUP_VAL_ON_INDEX or a no_sales, it will be trapped by the WHEN OTHERS clause.
(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 DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');
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;
Frequently Asked Questions
Question: Is there any way to get the ORA error number (and/or description) for the errors that will fall into OTHERS?
Something like:
WHEN OTHERS THENAnswer: Yes, you can use SQLCODE function to retrieve the error number and SQLERRM function to retrieve the error message.
'Error number ' & Err.Number& ' has happen.'
For example, you could raise the error as follows:
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Or you could log the error to a table as follows:
EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);
INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;
No comments:
Post a Comment