To handle errors that are specific to your application or business logic, you can define your own exceptions in PL/SQL. Here's how you can create and handle user-defined exceptions:
- Define the Exception: You define custom exceptions using the
DECLAREsection of a PL/SQL block or package. This is typically done before theBEGINblock.
DECLARE
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(my_exception, -20001); -- Assign a unique error code
- Raise the Exception: When a condition occurs that warrants raising the exception, you use the
RAISEstatement. You can raise your custom exception along with a custom error message.
IF some_condition THEN
RAISE my_exception;
END IF;
- Handle the Exception: In the
EXCEPTIONsection of your PL/SQL block, you specify how to handle the custom exception.
EXCEPTION
WHEN my_exception THEN
DBMS_OUTPUT.PUT_LINE('Custom exception occurred.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
Here's a complete example:
DECLARE
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(my_exception, -20001); -- Assign a unique error code
BEGIN
-- Your PL/SQL code here
IF some_condition THEN
RAISE my_exception;
END IF;
EXCEPTION
WHEN my_exception THEN
DBMS_OUTPUT.PUT_LINE('Custom exception occurred.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
In this example, if the condition some_condition evaluates to true, the custom exception my_exception is raised. Then, in the EXCEPTION block, the custom exception is caught and handled accordingly. All other exceptions are caught by the WHEN OTHERS clause, which provides a generic error message.
Remember to use meaningful names for your custom exceptions and to document them appropriately for clarity in your code.
Tags:
Oracle