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
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 aVARCHAR2(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
-
Declare variables with
%TYPEso they match the column they hold.DECLARE l_name customers.name%TYPE; BEGIN SELECT name INTO l_name FROM customers WHERE id = 1; END;
-
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
ORA-06512at line (PL/SQL error stack)ORA-01722invalid numberORA-12899value too large for column
More pl/sql errors
ORA-01403no data foundORA-01422exact fetch returns more than requested rowsORA-06550PL/SQL compilation error at line, columnORA-06508PL/SQL: could not find program unit being calledORA-06503PL/SQL: function returned without valueORA-04091table is mutating, trigger may not see itORA-04088error during execution of triggerORA-04068existing state of packages has been discarded
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.