// Oracle error reference

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

A PL/SQL assignment or conversion failed: a value too long for a variable, a string that isn't a number, or a NULL into a NOT NULL variable.

Error code
ORA-06502
Category
PL/SQL
Error message
PL/SQL: numeric or value error string

What it means

ORA-06502 is raised in PL/SQL when a value can't be stored or converted, and the text after the message says how, for example character string buffer too small or character to number conversion error.

Newer releases word it as PL/SQL: value or conversion error.

Common causes

  • character string buffer too small: assigning a string longer than a VARCHAR2(n) variable, often a local variable smaller than the column it receives.
  • character to number conversion error: assigning non-numeric text to a NUMBER variable.
  • number precision too large: a number that doesn't fit a constrained NUMBER variable.
  • Assigning NULL to a variable declared NOT NULL.

How to fix it

  1. Declare variables with %TYPE so they match the column they hold.

    DECLARE
      l_name customers.name%TYPE;
    BEGIN
      SELECT name INTO l_name FROM customers WHERE id = 1;
    END;
  2. Read the line number in the accompanying ORA-06512 and check the assignment there; convert explicitly with TO_NUMBER(... DEFAULT NULL ON CONVERSION ERROR) when input may be bad.

Related errors

More pl/sql errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.