Oracle ORA-06512 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06512: Understanding Oracle's PL/SQL Stack Trace Error

ORA-06512 is not a standalone error — it's Oracle's way of telling you where an error occurred within a PL/SQL call stack, showing the program unit name and line number at each level of propagation. It always appears alongside a root-cause error (such as ORA-01403, ORA-00001, or ORA-20001) and serves as a traceback mechanism to help you pinpoint the exact source of the failure. Think of it as Oracle's built-in stack trace, similar to what you'd see in Java or Python exception output.

Top 3 Causes

1. Unhandled Runtime Exceptions Propagating Up the Call Stack

When a built-in exception like NO_DATA_FOUND or ZERO_DIVIDE occurs inside a nested procedure without a local handler, Oracle propagates it upward — printing an ORA-06512 line for each level it passes through.

-- Problem: No exception handler in inner procedure
CREATE OR REPLACE PROCEDURE inner_proc IS
  v_val NUMBER;
BEGIN
  SELECT salary INTO v_val       -- ORA-06512 points here if no data found
    FROM employees
   WHERE employee_id = 99999;    -- Non-existent ID
END;
/

CREATE OR REPLACE PROCEDURE outer_proc IS
BEGIN
  inner_proc;                    -- ORA-06512 also points here
END;
/

-- Fix: Add explicit exception handling
CREATE OR REPLACE PROCEDURE inner_proc_fixed IS
  v_val NUMBER;
BEGIN
  SELECT salary INTO v_val
    FROM employees
   WHERE employee_id = 99999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found. Handling gracefully.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    RAISE;
END;
/

2. RAISE_APPLICATION_ERROR or Explicit RAISE Calls

Developers frequently use RAISE_APPLICATION_ERROR for business rule validation. Each call generates an ORA-06512 entry in the stack trace, which can make the error output look intimidating even for simple logic.

-- This will generate ORA-20001 + ORA-06512
CREATE OR REPLACE PROCEDURE validate_age(p_age IN NUMBER) IS
BEGIN
  IF p_age < 0 OR p_age > 150 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid age value: ' || p_age);
  END IF;
  DBMS_OUTPUT.PUT_LINE('Age is valid: ' || p_age);
END;
/

-- Calling and catching it properly
BEGIN
  validate_age(-5);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Caught error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

3. DML Constraint Violations Inside PL/SQL

When an INSERT or UPDATE violates a constraint (UNIQUE, NOT NULL, CHECK), Oracle raises an error from within the SQL engine, and PL/SQL wraps it with ORA-06512 stack entries as it propagates.

-- Problem: Unhandled DUP_VAL_ON_INDEX
CREATE OR REPLACE PROCEDURE add_employee(
  p_id   IN NUMBER,
  p_name IN VARCHAR2
) IS
BEGIN
  INSERT INTO employees (employee_id, first_name)
  VALUES (p_id, p_name);
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- ORA-00001 + ORA-06512 prevented from propagating
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Duplicate employee ID: ' || p_id);
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Error ' || SQLCODE || ': ' || SQLERRM);
    RAISE;
END;
/

Quick Fix Solutions

Read the stack bottom-up: The last ORA-06512 line in the output is the actual origin of the error. Start your investigation there, not at the top.

Use FORMAT_ERROR_BACKTRACE: Unlike SQLERRM, this function preserves the full stack trace even after exception handlers are entered.

BEGIN
  outer_proc;
EXCEPTION
  WHEN OTHERS THEN
    -- Always log both for complete diagnosis
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

Prevention Tips

1. Implement a centralized error logging package using PRAGMA AUTONOMOUS_TRANSACTION so every unhandled exception gets recorded to a log table with full backtrace info — even if the main transaction is rolled back.

2. Enforce coding standards: Every stored procedure must have an EXCEPTION section. Never use WHEN OTHERS THEN NULL (silently swallowing errors). Always either handle the specific exception, log it, or re-raise it with RAISE. Integrate static analysis tools into your deployment pipeline to catch missing handlers before they reach production.

Related Oracle Errors

Error Code	Description
ORA-01403	No data found — most common root cause paired with ORA-06512
ORA-01422	Exact fetch returns too many rows
ORA-00001	Unique constraint violated
ORA-06510	Unhandled user-defined exception
ORA-20000~20999	User-defined errors via 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