ORA-06502 PL/SQL Numeric or Value Error - Complete Resolution Guide

ORA-06502: PL/SQL: Numeric or Value Error

Error Overview

Error Text: ORA-06502: PL/SQL: numeric or value error

The ORA-06502 error is one of the most common PL/SQL runtime errors. It occurs when a value assignment or conversion violates datatype constraints. This error has many variations indicating the specific cause, such as character string buffer too small, number precision too large, or character to number conversion error.

Common Error Variations

Variation	Description
character string buffer too small	Value exceeds VARCHAR2 variable length
number precision too large	Number exceeds defined precision
character to number conversion error	Invalid string-to-number conversion
null index table key value	NULL used as associative array index
bulk bind: truncated bind	Bulk operation data truncation

Common Causes

1. String Buffer Overflow

• Assigning value longer than variable definition
• Concatenation result exceeds buffer
• Substring operations returning unexpected lengths

2. Number Precision Issues

• Value exceeds NUMBER precision
• Scale overflow during calculations
• Division results with too many decimal places

3. Type Conversion Failures

• Non-numeric string to NUMBER conversion
• Invalid date string formats
• Implicit conversion failures

4. NULL Value Handling

• NULL in arithmetic operations
• NULL as collection index
• Uninitialized variable usage

Diagnostic Examples

Scenario 1: Character String Buffer Too Small

-- ERROR: Buffer overflow
DECLARE
  v_name VARCHAR2(10);
BEGIN
  v_name := 'This is a very long string';  -- 26 characters
END;
/
-- ORA-06502: PL/SQL: numeric or value error: character string buffer too small

-- SOLUTION 1: Increase buffer size
DECLARE
  v_name VARCHAR2(100);
BEGIN
  v_name := 'This is a very long string';
END;
/

-- SOLUTION 2: Use SUBSTR to truncate
DECLARE
  v_name VARCHAR2(10);
BEGIN
  v_name := SUBSTR('This is a very long string', 1, 10);
END;
/

-- SOLUTION 3: Use %TYPE for column-based sizing
DECLARE
  v_name employees.last_name%TYPE;  -- Inherits column size
BEGIN
  SELECT last_name INTO v_name FROM employees WHERE employee_id = 100;
END;
/

Scenario 2: Number Precision Too Large

-- ERROR: Precision overflow
DECLARE
  v_num NUMBER(5,2);  -- Max: 999.99
BEGIN
  v_num := 12345.67;  -- Too large
END;
/
-- ORA-06502: PL/SQL: numeric or value error: number precision too large

-- SOLUTION 1: Increase precision
DECLARE
  v_num NUMBER(10,2);
BEGIN
  v_num := 12345.67;
END;
/

-- SOLUTION 2: Use ROUND to fit precision
DECLARE
  v_num NUMBER(5,2);
BEGIN
  v_num := ROUND(123.456789, 2);  -- Results in 123.46
END;
/

-- SOLUTION 3: Use TRUNC for truncation without rounding
DECLARE
  v_num NUMBER(5,2);
BEGIN
  v_num := TRUNC(123.456789, 2);  -- Results in 123.45
END;
/

Scenario 3: Character to Number Conversion Error

-- ERROR: Invalid number conversion
DECLARE
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER('ABC123');
END;
/
-- ORA-06502: PL/SQL: numeric or value error: character to number conversion error

-- SOLUTION 1: Validate before conversion
DECLARE
  v_num NUMBER;
  v_str VARCHAR2(20) := 'ABC123';
BEGIN
  -- Check if string is numeric
  IF REGEXP_LIKE(v_str, '^\d+\.?\d*$') THEN
    v_num := TO_NUMBER(v_str);
  ELSE
    v_num := NULL;  -- Or handle error
    DBMS_OUTPUT.PUT_LINE('Invalid number format: ' || v_str);
  END IF;
END;
/

-- SOLUTION 2: Use exception handling
DECLARE
  v_num NUMBER;
BEGIN
  BEGIN
    v_num := TO_NUMBER('ABC123');
  EXCEPTION
    WHEN VALUE_ERROR THEN
      v_num := 0;  -- Default value
      DBMS_OUTPUT.PUT_LINE('Conversion failed, using default');
  END;
END;
/

-- SOLUTION 3: Extract numeric portion
DECLARE
  v_num NUMBER;
  v_str VARCHAR2(20) := 'ABC123';
BEGIN
  v_num := TO_NUMBER(REGEXP_REPLACE(v_str, '[^0-9.]', ''));
  DBMS_OUTPUT.PUT_LINE('Extracted number: ' || v_num);  -- 123
END;
/

Scenario 4: NULL Index in Associative Array

-- ERROR: NULL index
DECLARE
  TYPE t_lookup IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
  v_lookup t_lookup;
  v_key VARCHAR2(50);
BEGIN
  v_key := NULL;
  v_lookup(v_key) := 'test';  -- NULL key not allowed
END;
/
-- ORA-06502: PL/SQL: numeric or value error: NULL index table key value

-- SOLUTION: Validate key before use
DECLARE
  TYPE t_lookup IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(50);
  v_lookup t_lookup;
  v_key VARCHAR2(50);
BEGIN
  v_key := NULL;
  IF v_key IS NOT NULL THEN
    v_lookup(v_key) := 'test';
  ELSE
    DBMS_OUTPUT.PUT_LINE('Cannot use NULL as key');
  END IF;
END;
/

Scenario 5: Bulk Bind Truncation

-- ERROR: Bulk bind truncation
DECLARE
  TYPE t_names IS TABLE OF VARCHAR2(10);
  v_names t_names;
BEGIN
  SELECT last_name BULK COLLECT INTO v_names
  FROM employees;  -- Some names > 10 chars
END;
/
-- ORA-06502: PL/SQL: numeric or value error: bulk bind: truncated bind

-- SOLUTION: Use appropriate size or %TYPE
DECLARE
  TYPE t_names IS TABLE OF employees.last_name%TYPE;
  v_names t_names;
BEGIN
  SELECT last_name BULK COLLECT INTO v_names
  FROM employees;
END;
/

Diagnostic Queries

Find Column Sizes for %TYPE Usage

-- Check column definitions for proper variable sizing
SELECT
  column_name,
  data_type,
  data_length,
  data_precision,
  data_scale,
  nullable
FROM all_tab_columns
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES'
ORDER BY column_id;

Identify Potential Overflow Data

-- Find values that would cause overflow
-- Example: Find names longer than 10 characters
SELECT employee_id, last_name, LENGTH(last_name) as name_length
FROM employees
WHERE LENGTH(last_name) > 10;

-- Find numbers that exceed precision
SELECT *
FROM transactions
WHERE amount >= 1000000  -- Would overflow NUMBER(8,2)
   OR amount < -1000000;

Check PL/SQL Code for Issues

-- Find PL/SQL objects with potential issues
SELECT owner, name, type, line, text
FROM all_source
WHERE REGEXP_LIKE(text, 'VARCHAR2\s*\(\s*[1-9]\s*\)', 'i')  -- Small buffers
  AND owner = 'SCHEMA_NAME'
ORDER BY name, line;

-- Find hardcoded small number precision
SELECT owner, name, type, line, text
FROM all_source
WHERE REGEXP_LIKE(text, 'NUMBER\s*\(\s*[1-3]\s*(,|\))', 'i')
  AND owner = 'SCHEMA_NAME'
ORDER BY name, line;

Resolution Strategies

1. Defensive Variable Declaration

-- Use maximum practical sizes
DECLARE
  v_name VARCHAR2(4000);        -- Max VARCHAR2 in PL/SQL
  v_clob CLOB;                   -- For unlimited text
  v_num  NUMBER;                 -- Unrestricted precision

  -- Better: Use %TYPE for database columns
  v_emp_name employees.last_name%TYPE;
  v_salary   employees.salary%TYPE;

  -- Use %ROWTYPE for entire rows
  v_emp_rec  employees%ROWTYPE;
BEGIN
  -- Safe from buffer overflow
  SELECT last_name, salary INTO v_emp_name, v_salary
  FROM employees WHERE employee_id = 100;
END;
/

2. Safe Type Conversion Functions

-- Create safe conversion function
CREATE OR REPLACE FUNCTION safe_to_number(
  p_str IN VARCHAR2,
  p_default IN NUMBER DEFAULT NULL
) RETURN NUMBER IS
BEGIN
  RETURN TO_NUMBER(p_str);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN p_default;
END safe_to_number;
/

-- Usage
DECLARE
  v_num NUMBER;
BEGIN
  v_num := safe_to_number('ABC', 0);  -- Returns 0
  v_num := safe_to_number('123', 0);  -- Returns 123
END;
/

3. Validation Before Assignment

-- Validate string length before assignment
CREATE OR REPLACE FUNCTION safe_assign(
  p_value IN VARCHAR2,
  p_max_len IN NUMBER
) RETURN VARCHAR2 IS
BEGIN
  IF p_value IS NULL THEN
    RETURN NULL;
  ELSIF LENGTH(p_value) > p_max_len THEN
    RETURN SUBSTR(p_value, 1, p_max_len);
  ELSE
    RETURN p_value;
  END IF;
END safe_assign;
/

-- Usage
DECLARE
  v_short VARCHAR2(10);
BEGIN
  v_short := safe_assign('This is a very long string', 10);
  -- v_short = 'This is a '
END;
/

4. Exception Handling Pattern

DECLARE
  v_result VARCHAR2(100);
BEGIN
  -- Wrap risky operations
  BEGIN
    -- Potentially dangerous operation
    v_result := some_function_that_might_overflow();
  EXCEPTION
    WHEN VALUE_ERROR THEN
      -- Log the error
      INSERT INTO error_log (error_date, error_msg)
      VALUES (SYSDATE, 'Value error in some_function');
      COMMIT;
      -- Set safe default
      v_result := 'ERROR';
  END;

  -- Continue processing
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/

Prevention Best Practices

1. Variable Declaration Guidelines

-- GOOD: Use %TYPE for column-based variables
v_name employees.last_name%TYPE;

-- GOOD: Use generous sizes for computed values
v_full_name VARCHAR2(500);  -- first || ' ' || last || ' ' || suffix

-- GOOD: Use NUMBER without precision for calculations
v_result NUMBER;

-- BAD: Hardcoded small sizes
v_name VARCHAR2(10);  -- Too small!

-- BAD: Tight precision for calculations
v_result NUMBER(5,2);  -- May overflow

2. Safe String Concatenation

DECLARE
  v_result VARCHAR2(4000);
  v_first VARCHAR2(100) := 'John';
  v_last VARCHAR2(100) := 'Doe';
BEGIN
  -- Safe concatenation with NULL handling
  v_result := NVL(v_first, '') || ' ' || NVL(v_last, '');

  -- Or use CONCAT (auto-handles NULL)
  v_result := v_first || ' ' || v_last;
END;
/

3. Number Calculation Safety

DECLARE
  v_result NUMBER;
  v_divisor NUMBER := 0;
BEGIN
  -- Safe division
  IF v_divisor != 0 THEN
    v_result := 100 / v_divisor;
  ELSE
    v_result := NULL;  -- Or raise custom error
  END IF;

  -- Safe with NULLIF
  v_result := 100 / NULLIF(v_divisor, 0);  -- Returns NULL if div by 0
END;
/

Related Errors

• ORA-00932 - Inconsistent datatypes
• ORA-01722 - Invalid number
• ORA-06512 - At line (PL/SQL stack trace)
• ORA-01476 - Divisor is equal to zero
• ORA-01403 - No data found

Summary Checklist

1. Always use %TYPE for variables that store column values
2. Use generous sizes for computed or concatenated strings
3. Validate input data before type conversion
4. Handle NULL values explicitly
5. Use exception handling for risky operations
6. Test with boundary data including maximum length strings
7. Use SUBSTR/ROUND/TRUNC when you must fit data into constrained variables