Oracle ORA-06502 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06502: PL/SQL Numeric or Value Error — A Practical Guide

ORA-06502 is one of the most common runtime errors in Oracle PL/SQL development. It occurs when a value assignment fails due to type mismatch, size overflow, or precision violation. Understanding its root causes and applying the right fix can save you hours of debugging time.

Top 3 Causes and Fixes

1. String Buffer Too Small

This is the most frequent cause. It happens when you try to assign a string longer than the declared variable size.

-- Problem: variable too small
DECLARE
  v_name VARCHAR2(10);
BEGIN
  v_name := 'This string is way too long'; -- ORA-06502
END;
/

-- Fix 1: Use %TYPE to match the column definition automatically
DECLARE
  v_name employees.employee_name%TYPE; -- inherits column size
BEGIN
  SELECT employee_name INTO v_name
  FROM employees WHERE employee_id = 101;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

-- Fix 2: Declare with a safely large buffer
DECLARE
  v_name VARCHAR2(500);
BEGIN
  v_name := 'This string is now handled safely';
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

2. Invalid Type Conversion

Assigning a non-numeric string to a NUMBER variable or using TO_NUMBER() on an invalid string triggers this error.

-- Problem: non-numeric string assigned to NUMBER
DECLARE
  v_val NUMBER;
BEGIN
  v_val := TO_NUMBER('ABC'); -- ORA-06502
END;
/

-- Fix 1: Validate before conversion using VALIDATE_CONVERSION (Oracle 12c+)
DECLARE
  v_input VARCHAR2(50) := 'ABC';
  v_val   NUMBER;
BEGIN
  IF VALIDATE_CONVERSION(v_input AS NUMBER) = 1 THEN
    v_val := TO_NUMBER(v_input);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Invalid number input: ' || v_input);
    v_val := 0;
  END IF;
END;
/

-- Fix 2: Use exception handling as a safety net
DECLARE
  v_input VARCHAR2(50) := '12X4';
  v_val   NUMBER;
BEGIN
  v_val := TO_NUMBER(v_input);
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Caught ORA-06502: Cannot convert [' || v_input || '] to number.');
    v_val := -1;
END;
/

3. NUMBER Precision or Scale Overflow

When a NUMBER variable is defined with limited precision (e.g., NUMBER(5,2)) and a value exceeds that range, ORA-06502 is raised.

-- Problem: value exceeds precision
DECLARE
  v_amount NUMBER(5, 2); -- allows up to 999.99
BEGIN
  v_amount := 12345.67; -- ORA-06502: integer part overflows
END;
/

-- Fix 1: Increase precision in declaration
DECLARE
  v_amount NUMBER(12, 2); -- safely accommodates large values
BEGIN
  v_amount := 12345.67;
  DBMS_OUTPUT.PUT_LINE('Amount: ' || v_amount);
END;
/

-- Fix 2: Pre-validate the value range before assignment
DECLARE
  v_amount NUMBER(5, 2);
  v_raw    NUMBER := 12345.67;
BEGIN
  IF v_raw BETWEEN -999.99 AND 999.99 THEN
    v_amount := ROUND(v_raw, 2);
    DBMS_OUTPUT.PUT_LINE('Amount: ' || v_amount);
  ELSE
    RAISE_APPLICATION_ERROR(-20001, 'Value out of range: ' || v_raw);
  END IF;
END;
/

Quick Fix Checklist

✅ Always use %TYPE and %ROWTYPE instead of hard-coded sizes
✅ Use VALIDATE_CONVERSION (12c+) or REGEXP_LIKE before TO_NUMBER()
✅ Wrap risky assignments in BEGIN...EXCEPTION...END blocks
✅ Use SUBSTR() to safely truncate strings before assignment
✅ Review NUMBER column precision when storing calculated results

Prevention Tips

Use %TYPE everywhere you can. This ensures your PL/SQL variables automatically inherit the column's data type and size, eliminating buffer mismatch errors entirely.

Build a validation utility package. Centralize input validation logic — number checks, string length guards, and range validations — into a shared package. This reduces duplicated code and makes your PL/SQL applications significantly more robust against ORA-06502.

Related Errors

Error Code	Description
ORA-01722	Invalid number — SQL-layer equivalent of ORA-06502
ORA-01426	Numeric overflow — exceeds NUMBER max range
ORA-06500	PL/SQL storage error — memory allocation failure

📖 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