Oracle ORA-06506 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06506: PL/SQL Unhandled User-Defined Exception – Causes and Fixes

ORA-06506 occurs when a PL/SQL block raises a user-defined exception but no corresponding EXCEPTION handler exists to catch it. This causes the exception to propagate up the call stack uncaught, ultimately returning the error to the caller. It is one of the most common PL/SQL errors in production environments involving complex package hierarchies or nested blocks.

Top 3 Causes and Fixes

Cause 1: Missing EXCEPTION Handler After RAISE

The most straightforward cause is declaring and raising a custom exception without providing a matching WHEN clause.

-- Problematic Code
DECLARE
    e_invalid_age EXCEPTION;
    v_age NUMBER := -1;
BEGIN
    IF v_age < 0 THEN
        RAISE e_invalid_age;  -- No handler -> ORA-06506
    END IF;
END;
/

-- Fixed Code
DECLARE
    e_invalid_age EXCEPTION;
    v_age NUMBER := -1;
BEGIN
    IF v_age < 0 THEN
        RAISE e_invalid_age;
    END IF;
EXCEPTION
    WHEN e_invalid_age THEN
        DBMS_OUTPUT.PUT_LINE('Error: Age cannot be negative.');
END;
/

Cause 2: Package-Level Exception Not Referenced Correctly

When a package declares a global exception, callers must reference it as package_name.exception_name. Using just the exception name in the handler will cause a mismatch, leaving the exception unhandled.

-- Package Spec
CREATE OR REPLACE PACKAGE pkg_orders AS
    e_order_not_found EXCEPTION;
    PROCEDURE fetch_order(p_order_id IN NUMBER);
END pkg_orders;
/

-- Caller - WRONG (exception not matched)
BEGIN
    pkg_orders.fetch_order(p_order_id => 99999);
EXCEPTION
    WHEN e_order_not_found THEN  -- Won't match! Missing package prefix
        DBMS_OUTPUT.PUT_LINE('Order not found.');
END;
/

-- Caller - CORRECT
BEGIN
    pkg_orders.fetch_order(p_order_id => 99999);
EXCEPTION
    WHEN pkg_orders.e_order_not_found THEN  -- Correct reference
        DBMS_OUTPUT.PUT_LINE('Order not found.');
END;
/

Cause 3: Unhandled Re-raised Exception in Nested Blocks

In nested PL/SQL blocks, an exception caught in an inner block and re-raised with RAISE must also be handled in the outer block. If the outer block has no matching handler, ORA-06506 is thrown.

-- Fixed: Handle at both inner and outer levels
DECLARE
    e_process_error EXCEPTION;
BEGIN
    -- Outer Block
    BEGIN
        -- Inner Block
        RAISE e_process_error;
    EXCEPTION
        WHEN e_process_error THEN
            DBMS_OUTPUT.PUT_LINE('Inner block caught the error. Re-raising...');
            RAISE;  -- Re-raises to outer block
    END;
EXCEPTION
    WHEN e_process_error THEN
        -- Outer block must also handle it
        DBMS_OUTPUT.PUT_LINE('Outer block: final handling of process error.');
END;
/

Quick Fix Solutions

Always add a WHEN OTHERS handler at the top-level of every procedure or function.
Use PRAGMA EXCEPTION_INIT to bind custom exceptions to specific error codes for easier tracking.

DECLARE
    e_data_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_data_error, -20100);
BEGIN
    RAISE_APPLICATION_ERROR(-20100, 'Invalid data encountered.');
EXCEPTION
    WHEN e_data_error THEN
        DBMS_OUTPUT.PUT_LINE('Caught: ' || SQLERRM);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM);
        RAISE;
END;
/

Prevention Tips

Centralize exception declarations in a shared utility package so all developers reference the same exception objects consistently, eliminating handler mismatches.
Enforce a coding standard that requires every PL/SQL unit to include a WHEN OTHERS handler that logs to an error table and re-raises, ensuring no exception ever goes silently unhandled.

-- Standard error logging pattern
WHEN OTHERS THEN
    INSERT INTO error_log (log_date, proc_name, err_code, err_msg)
    VALUES (SYSDATE, 'YOUR_PROC_NAME', SQLCODE, SUBSTR(SQLERRM, 1, 500));
    COMMIT;
    RAISE;

Related Errors

ORA-06500 – PL/SQL storage error
ORA-06501 – PL/SQL program error
ORA-01403 – NO_DATA_FOUND, often converted to user-defined exceptions
ORA-20000 to ORA-20999 – User-defined application error range used with RAISE_APPLICATION_ERROR

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community