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

A PL/SQL assignment or conversion failed: most often a string is longer than the variable can hold (character string buffer too small), a text cannot become a number, or NULL went into a NOT NULL variable.

What it means

ORA-06502 is PL/SQL’s general error for values that do not fit. The text after the colon tells you which kind: character string buffer too small, character to number conversion error, number precision too large or NULL index table key value. The ORA-06512 lines that follow show the exact line of code.

Common causes

A VARCHAR2(n) variable smaller than the value assigned to it.
Multi-byte characters: a variable declared in BYTE needs more bytes than characters (Turkish letters such as ğ, ş, ı take 2 bytes in AL32UTF8).
Converting text that is not a number.
A number too large for a NUMBER(p,s) variable.

How to fix it

Read the ORA-06512 line numbers to find the statement.
Declare variables from the column type: l_name employees.name%TYPE.
Use character length semantics for text that may contain multi-byte characters: VARCHAR2(100 CHAR).
Validate or convert input before assigning it.

Example

Size the variable from the column:

DECLARE
  l_name employees.last_name%TYPE;   -- not VARCHAR2(20)
BEGIN
  SELECT last_name INTO l_name FROM employees WHERE id = 1;
END;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-06502. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.