Oracle ORA-06502 Error: Causes and Solutions Complete Guide

-- 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;
/

-- 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;
/

-- 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;
/