ORA-06502 occurs when a PL/SQL variable cannot hold or convert the assigned value due to size, precision, or datatype mismatch.
ORA-06502: PL/SQL numeric or value error appears when a value overflows or mismatches a variable’s datatype or size. Enlarge the variable definition or add explicit TO_CHAR/TO_NUMBER conversions at the failing line to fix the error.
ORA-06502: PL/SQL: numeric or value error
ORA-06502 is a runtime exception raised by Oracle PL/SQL when the engine cannot fit a value into a variable’s declared type or size. The message appears immediately after the offending statement, stops execution, and rolls back uncommitted work.
The error can surface inside anonymous blocks, stored procedures, triggers, or packages and is version-agnostic—from Oracle 9i through 23c.
Correcting it is important because repeated failures can hide data corruption, break application flows, and waste compute resources.
Datatype mismatch tops the list: assigning a VARCHAR2 literal into a NUMBER, or casting a string like 'ABC' to NUMBER triggers ORA-06502.
String overflow occurs when the data length exceeds the target variable’s declared size, e.g., VARCHAR2(10) receiving 20 characters.
Numeric overflow happens when the value’s precision or scale is larger than the variable’s definition, such as NUMBER(5) receiving 123456.
Wrong parameter modes or lengths in DBMS_OUTPUT or UTL_FILE can also raise the exception.
PL/SQL collections throw ORA-06502 when indices are out of range or element assignments break datatype rules.
Identify the statement: enable SERVEROUTPUT or examine stack trace to pinpoint the line number.
Compare source and target datatypes; adjust either the variable definition or the expression with TO_CHAR or TO_NUMBER conversions.
Increase string or number size: change VARCHAR2(10) to VARCHAR2(100) or NUMBER(5) to NUMBER(10).
Use EXCEPTION handling to trap and log the error, but always correct the root size or datatype issue before re-raising.
Implicit conversion failure: pass a CHAR parameter containing letters to a NUMBER column.
Solution: sanitize input or call TO_NUMBER with default ON CONVERSION ERROR handling.
Bulk collect into collection of NUMBER while query selects VARCHAR2 column. Solution: match collection element type to query column.
DBMS_OUTPUT.PUT_LINE buffer overflow with long strings.
Solution: split long messages or call PUT_LINE in loops.
Declare variables with generous sizes and consistent datatypes mirroring table columns.
Validate and cast external inputs at API boundaries using explicit conversion functions with EXCEPTION clauses.
Enable compile-time warnings (PLSQL_WARNINGS) to catch size mismatches early.
Adopt unit tests that populate boundary values to surface hidden overflow conditions.
Galaxy’s AI Copilot highlights datatype conflicts as you type, reducing runtime surprises.
ORA-01403: no data found – arises when SELECT returns nothing into scalar variable; solve with EXISTS checks.
ORA-06512: at line X – not an error itself but a stack trace line accompanying ORA-06502; read it to locate code path.
ORA-06530: reference to uninitialized collection – fix by calling collection_name.EXTEND or assigning constructor.
Assigning a string to a numeric variable, or vice versa, forces Oracle to attempt an implicit cast.
When the cast fails, ORA-06502 is raised.
Input data longer than the target VARCHAR2, CHAR, or RAW size overflows the buffer and triggers the exception.
Values whose precision or scale exceed the NUMBER definition cannot be stored, causing a numeric error.
Assigning to an index that has not been allocated or using mismatched element types in collections produces ORA-06502.
Writing lines longer than 32,767 bytes or passing incorrect datatypes to these utility packages raises the error.
No. ORA-06502 is a PL/SQL runtime exception, whereas ORA-01722 occurs in pure SQL during implicit conversion.
Only if the root cause is string overflow. Datatype mismatches or numeric overflow need explicit conversions or larger NUMBER precision.
Run the block, read the accompanying ORA-06512 stack trace, or enable DBMS_TRACE to capture a detailed execution path.
Galaxy’s AI Copilot flags potential datatype and length issues while you type, helping you fix them before runtime.
.png)
.avif){kind=logo}
