What causes ORA-06502?

A string value is assigned to a PL/SQL variable with a shorter LENGTH constraint. TO_NUMBER conversion of a non-numeric string inside a PL/SQL block. Arithmetic overflow or underflow in PL/SQL calculations. NULL value assignment to a NOT NULL constrained PL/SQL variable

How do I fix ORA-06502?

Step 1: Check the ORA-06512 stack lines that follow — they point to the exact line in the procedure.. Step 2: Increase the PL/SQL variable length: `v_name VARCHAR2(500)` instead of `VARCHAR2(100)`.. Step 3: Add EXCEPTION WHEN VALUE_ERROR to handle and log the offending value.. Step 4: Validate numeric strings before TO_NUMBER: `IF REGEXP_LIKE(v_str, '^-?[0-9]+(\.[0-9]+)?$') THEN ...`.. Step 5: Review the procedure for hardcoded variable lengths that may be too short for real data.

Medium severitydata

Oracle Database Error:
ORA-06502

What does this error mean?

A PL/SQL program encountered a numeric conversion error, string buffer overflow, or value constraint violation during execution.

Common causes

1A string value is assigned to a PL/SQL variable with a shorter LENGTH constraint
2TO_NUMBER conversion of a non-numeric string inside a PL/SQL block
3Arithmetic overflow or underflow in PL/SQL calculations
4NULL value assignment to a NOT NULL constrained PL/SQL variable

How to fix it

1Step 1: Check the ORA-06512 stack lines that follow — they point to the exact line in the procedure.
2Step 2: Increase the PL/SQL variable length: `v_name VARCHAR2(500)` instead of `VARCHAR2(100)`.
3Step 3: Add EXCEPTION WHEN VALUE_ERROR to handle and log the offending value.
4Step 4: Validate numeric strings before TO_NUMBER: `IF REGEXP_LIKE(v_str, '^-?[0-9]+(\.[0-9]+)?$') THEN ...`.
5Step 5: Review the procedure for hardcoded variable lengths that may be too short for real data.

Example log output

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCHEMA.LOAD_PROCEDURE", line 147
ORA-06512: at line 1

Frequently asked questions

How do I find which line caused ORA-06502?

Look for ORA-06512 in the full error stack — it includes the procedure name and line number. That is the exact location of the failing assignment or conversion.

Source · docs.oracle.com/error-help/db/ora-06502