ORA-06512: What It Means, Why It Appears, and How to Fix It

-- ============================================================
-- DEMO: Producing and reading a real ORA-06512 stack trace
-- Run each block in sequence in SQL*Plus or SQL Developer
-- ============================================================

-- Step 1: Create the innermost procedure — this is where the
--         real error will actually be raised.
CREATE OR REPLACE PROCEDURE validate_employee_salary(
    p_employee_id  IN  employees.employee_id%TYPE,
    p_new_salary   IN  employees.salary%TYPE
) AS
    v_current_salary  employees.salary%TYPE;
BEGIN
    -- Fetch the existing salary; raises NO_DATA_FOUND if employee missing
    SELECT salary
    INTO   v_current_salary          -- Line 13 relative to procedure start
    FROM   employees
    WHERE  employee_id = p_employee_id;

    -- Business rule: new salary cannot be less than current salary
    IF p_new_salary < v_current_salary THEN
        -- Raise a custom application error — this becomes the ROOT error
        RAISE_APPLICATION_ERROR(
            -20001,                  -- Line 20: THIS is where the bomb drops
            'New salary ' || p_new_salary ||
            ' is below current salary ' || v_current_salary ||
            ' for employee ' || p_employee_id
        );
    END IF;
END validate_employee_salary;
/

-- Step 2: Create a middle-layer procedure that calls the inner one
--         but does NOT handle exceptions — so errors bubble upward
CREATE OR REPLACE PROCEDURE process_salary_update(
    p_employee_id  IN  employees.employee_id%TYPE,
    p_new_salary   IN  employees.salary%TYPE
) AS
BEGIN
    -- Calls inner procedure — no exception handler here, so any
    -- error from validate_employee_salary will propagate upward
    validate_employee_salary(         -- Line 9 of this procedure
        p_employee_id => p_employee_id,
        p_new_salary  => p_new_salary
    );

    -- If we get here, the salary was valid — apply the update
    UPDATE employees
    SET    salary = p_new_salary
    WHERE  employee_id = p_employee_id;

    COMMIT;
END process_salary_update;
/

-- Step 3: Anonymous block that calls the middle layer
--         Deliberately triggers the salary-drop scenario
BEGIN
    process_salary_update(
        p_employee_id => 100,   -- Assume employee 100 earns 24000
        p_new_salary  => 5000   -- This is lower — will trigger ORA-20001
    );
END;
/

-- ============================================================
-- Pattern 1: Catch, enrich with context, and re-raise
-- This gives the caller a MORE useful error than the raw one
-- ============================================================
CREATE OR REPLACE PROCEDURE apply_annual_bonus(
    p_department_id  IN  departments.department_id%TYPE,
    p_bonus_percent  IN  NUMBER
) AS
    v_employee_id   employees.employee_id%TYPE;
    v_current_salary employees.salary%TYPE;

    -- Cursor to process every employee in the department
    CURSOR c_dept_employees IS
        SELECT employee_id, salary
        FROM   employees
        WHERE  department_id = p_department_id;
BEGIN
    FOR r_employee IN c_dept_employees LOOP
        v_employee_id   := r_employee.employee_id;
        v_current_salary := r_employee.salary;

        UPDATE employees
        SET    salary = salary * (1 + p_bonus_percent / 100)
        WHERE  employee_id = v_employee_id;
    END LOOP;

    COMMIT;
EXCEPTION
    -- Pattern 1: Catch anything, add rich context, re-raise
    -- SQLERRM gives the original error message
    -- SQLCODE gives the original error number
    WHEN OTHERS THEN
        -- Roll back any partial changes from this call
        ROLLBACK;

        -- Re-raise with a new message that includes what we were doing
        -- RAISE_APPLICATION_ERROR with the original SQLERRM embedded
        RAISE_APPLICATION_ERROR(
            -20002,
            'apply_annual_bonus failed for department ' || p_department_id ||
            ' while processing employee ' || NVL(TO_CHAR(v_employee_id), 'UNKNOWN') ||
            '. Original error: ' || SQLERRM   -- Embeds the root error text
        );
END apply_annual_bonus;
/

-- ============================================================
-- Pattern 2: Catch, log to error table, continue (batch-safe)
-- ============================================================
CREATE OR REPLACE PROCEDURE batch_validate_all_salaries AS
    v_error_count  NUMBER := 0;
BEGIN
    FOR r_emp IN (SELECT employee_id, salary FROM employees) LOOP
        BEGIN
            -- Inner BEGIN..END creates a sub-block with its own handler
            -- so one failure doesn't abort the entire loop
            validate_employee_salary(
                p_employee_id => r_emp.employee_id,
                p_new_salary  => r_emp.salary
            );
        EXCEPTION
            WHEN OTHERS THEN
                v_error_count := v_error_count + 1;

                -- Log to an error tracking table instead of raising
                INSERT INTO salary_validation_errors (
                    employee_id,
                    error_code,
                    error_message,
                    error_timestamp
                ) VALUES (
                    r_emp.employee_id,
                    SQLCODE,          -- Numeric error code, e.g. -20001
                    SQLERRM,          -- Full error message text
                    SYSTIMESTAMP
                );
        END;  -- End of sub-block — loop continues to next employee
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE(
        'Batch complete. Errors encountered: ' || v_error_count
    );
END batch_validate_all_salaries;
/

-- ============================================================
-- Production-grade error logging using FORMAT_ERROR_BACKTRACE
-- This is the pattern used in real enterprise Oracle systems
-- ============================================================

-- First, a table to hold our structured error logs
CREATE TABLE plsql_error_log (
    log_id           NUMBER GENERATED ALWAYS AS IDENTITY,
    procedure_name   VARCHAR2(128),
    error_code       NUMBER,
    error_message    VARCHAR2(4000),
    error_backtrace  VARCHAR2(4000),   -- The ORA-06512 trail
    error_stack      VARCHAR2(4000),   -- Full ORA- error stack
    call_stack       VARCHAR2(4000),   -- Who called whom
    logged_at        TIMESTAMP DEFAULT SYSTIMESTAMP,
    session_user     VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER')
);
/

-- A reusable logging procedure — call this from any exception handler
CREATE OR REPLACE PROCEDURE log_plsql_error(
    p_procedure_name  IN  VARCHAR2,
    p_backtrace       IN  VARCHAR2,   -- Pass in pre-captured backtrace
    p_error_stack     IN  VARCHAR2    -- Pass in pre-captured error stack
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;    -- Log even if caller rolls back
BEGIN
    INSERT INTO plsql_error_log (
        procedure_name,
        error_code,
        error_message,
        error_backtrace,
        error_stack
    ) VALUES (
        p_procedure_name,
        SQLCODE,
        SQLERRM,
        p_backtrace,
        p_error_stack
    );
    COMMIT;  -- Autonomous transaction commits independently
END log_plsql_error;
/

-- The main procedure showing the capture pattern
CREATE OR REPLACE PROCEDURE recalculate_department_budget(
    p_department_id  IN  departments.department_id%TYPE
) AS
    v_total_salary    NUMBER;
    v_headcount       NUMBER;
    v_backtrace       VARCHAR2(4000);  -- Will hold our breadcrumb trail
    v_error_stack     VARCHAR2(4000);  -- Will hold the full ORA- stack
BEGIN
    SELECT SUM(salary), COUNT(*)
    INTO   v_total_salary, v_headcount
    FROM   employees
    WHERE  department_id = p_department_id;

    -- Deliberately cause a divide-by-zero if dept has no employees
    -- This raises ORA-01476: divisor is equal to zero
    UPDATE departments
    SET    budget_per_head = v_total_salary / v_headcount  -- Line 44
    WHERE  department_id   = p_department_id;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- CAPTURE THE BACKTRACE FIRST — before any other statement
        -- This preserves the exact line 44 reference in the backtrace
        v_backtrace   := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        v_error_stack := DBMS_UTILITY.FORMAT_ERROR_STACK;

        -- Now safe to do other work — backtrace is saved in variables
        ROLLBACK;

        -- Log using autonomous transaction so the rollback above
        -- does not wipe out our error log entry
        log_plsql_error(
            p_procedure_name => 'RECALCULATE_DEPARTMENT_BUDGET',
            p_backtrace      => v_backtrace,
            p_error_stack    => v_error_stack
        );

        -- Re-raise so the caller knows something went wrong
        RAISE;
END recalculate_department_budget;
/

-- Test it:
BEGIN
    recalculate_department_budget(p_department_id => 999); -- No employees
END;
/

-- Check what was logged:
SELECT procedure_name,
       error_code,
       error_message,
       error_backtrace
FROM   plsql_error_log
ORDER  BY logged_at DESC
FETCH FIRST 1 ROWS ONLY;

-- ============================================================
-- Pattern: Trigger-originated ORA-06512
-- Triggers are a common source of confusing stack traces because
-- they fire invisibly during DML — the developer doing the INSERT
-- doesn't always know a trigger exists
-- ============================================================

-- A BEFORE INSERT trigger that enforces a business rule
CREATE OR REPLACE TRIGGER enforce_salary_band
    BEFORE INSERT OR UPDATE OF salary
    ON employees
    FOR EACH ROW
DECLARE
    v_min_salary  job_grades.min_salary%TYPE;
    v_max_salary  job_grades.max_salary%TYPE;
BEGIN
    -- Look up the allowed salary range for this job
    SELECT min_salary, max_salary
    INTO   v_min_salary, v_max_salary
    FROM   job_grades
    WHERE  job_id = :NEW.job_id;   -- :NEW is the incoming row

    -- If the new salary falls outside the band, reject the DML
    IF :NEW.salary NOT BETWEEN v_min_salary AND v_max_salary THEN
        RAISE_APPLICATION_ERROR(    -- Line 21 of trigger
            -20010,
            'Salary ' || :NEW.salary ||
            ' is outside the band [' || v_min_salary ||
            ' - ' || v_max_salary || '] for job ' || :NEW.job_id
        );
    END IF;
END enforce_salary_band;
/

-- Now try to insert an out-of-band salary
INSERT INTO employees (
    employee_id, first_name, last_name,
    email, job_id, salary, department_id
) VALUES (
    207, 'Alex', 'Chen',
    'ACHEN', 'IT_PROG', 999999,  -- Way above any IT_PROG band
    60
);

-- ============================================================
-- Pattern: Package body stack trace
-- Shows how dot-notation reveals package membership
-- ============================================================
CREATE OR REPLACE PACKAGE BODY employee_pkg AS

    PROCEDURE hire_employee(
        p_first_name    IN  VARCHAR2,
        p_last_name     IN  VARCHAR2,
        p_job_id        IN  VARCHAR2,
        p_salary        IN  NUMBER,
        p_dept_id       IN  NUMBER
    ) AS
        v_new_emp_id  NUMBER;
    BEGIN
        SELECT employee_seq.NEXTVAL INTO v_new_emp_id FROM dual;

        INSERT INTO employees (
            employee_id, first_name, last_name,
            email, job_id, salary, department_id
        ) VALUES (
            v_new_emp_id,
            p_first_name, p_last_name,
            UPPER(SUBSTR(p_first_name,1,1) || p_last_name),
            p_job_id, p_salary, p_dept_id
        );                            -- The trigger fires HERE
        COMMIT;
    END hire_employee;

END employee_pkg;
/

-- Calling the package procedure — trigger fires mid-INSERT
BEGIN
    employee_pkg.hire_employee(
        p_first_name => 'Jordan',
        p_last_name  => 'Rivera',
        p_job_id     => 'IT_PROG',
        p_salary     => 999999,       -- Still out of band
        p_dept_id    => 60
    );
END;
/