Exception Handling

22.1 Predefined Exceptions

LOGIN_DENIED, NOT_LOGGED_ON, TIMEOUT_ON_RESOURCE, STORAGE_ERROR, PROGRAM_ERROR

22.2 Handling Exceptions

DECLARE
    e_insufficient_stock EXCEPTION;
    e_fk_violation       EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_fk_violation, -2291);   -- map an ORA error to a name
    v_stock NUMBER;
BEGIN
    SELECT stock INTO v_stock FROM products WHERE product_id = 1;
    IF v_stock < 5 THEN
        RAISE e_insufficient_stock;
    END IF;
    INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (999999, 1, 5, 10);
EXCEPTION
    WHEN e_insufficient_stock THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient stock for product 1');
    WHEN e_fk_violation THEN
        DBMS_OUTPUT.PUT_LINE('Order does not exist');
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Product not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);   -- line number of the error
        log_error(SQLERRM);
        RAISE;   -- re-raise. NEVER silently swallow WHEN OTHERS.
END;
/

22.3 Key Concepts

RAISE_APPLICATION_ERROR(-20000 .. -20999, msg [, keep_stack]).

Exception propagation: an unhandled exception moves to the enclosing block, then to the caller.

Exceptions raised in the declaration section propagate immediately to the outer block.

SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_STACK, FORMAT_ERROR_BACKTRACE, FORMAT_CALL_STACK, UTL_CALL_STACK (12c).

Statement-level rollback: a failed statement is rolled back, but the transaction isn't.

Compiler warning PLW-06009: a WHEN OTHERS handler that doesn't raise.