Oracle Error ORA-06502

Error code

ORA-06502

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

PL/SQL & Triggers

Last reviewed

24 October 2026

What it means

ORA-06502 means PL/SQL could not store a value in a variable, parameter or return value: the string is too long, the number has too many digits, the conversion failed, or the value breaks a NOT NULL or range constraint.

Every PL/SQL variable has a declared type and size. When an assignment, a SELECT INTO, a FETCH, a parameter or a function result would put a value into a variable that cannot hold it, PL/SQL raises the predefined exception VALUE_ERROR, which is ORA-06502. Oracle's reference gives two typical cases: assigning NULL to a variable declared NOT NULL, and assigning a number larger than 99 to a variable declared NUMBER(2).

The text after the colon tells you which kind of problem it is: character string buffer too small for a string that is too long, number precision too large for a number with too many digits, and no detail for some range violations. Current releases print "value or conversion error"; 19c prints "numeric or value error". The ORA-06512 line under it gives the line of code that failed.

ORA-06502 is a PL/SQL error. The same oversized value written straight into a table column raises ORA-12899 instead, and a bad number conversion inside a SQL statement raises ORA-01722.

Common causes

• A string variable is declared smaller than the data it receives, for example VARCHAR2(50) for a column that is now VARCHAR2(200), or a buffer built up by concatenation in a loop.
• A variable is sized in bytes while the data has multibyte characters, so a value with fewer characters than the limit still does not fit.
• A number has more digits than the declared precision allows, such as 5000 in a NUMBER(3).
• A string that is not a valid number is converted to NUMBER in PL/SQL, explicitly with TO_NUMBER or implicitly in an assignment.
• NULL is assigned to a variable declared NOT NULL, or a value falls outside the range of a constrained subtype.
• A function's return value or an OUT parameter is too large for the variable the caller receives it into.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT line, text
FROM all_source
WHERE owner = 'SHOP'
  AND name = 'FORMAT_CUSTOMER'
  AND type = 'FUNCTION'
  AND line BETWEEN 8 AND 16
ORDER BY line;

SELECT column_name, data_type, data_length, char_length, char_used,
       data_precision, data_scale
FROM all_tab_columns
WHERE owner = 'SHOP'
  AND table_name = 'CUSTOMERS'
  AND column_name IN ('FULL_NAME', 'CREDIT_LIMIT');

SELECT id, LENGTH(full_name) AS chars, LENGTHB(full_name) AS bytes
FROM shop.customers
WHERE LENGTHB(full_name) > 50   -- the declared size of the variable
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

SELECT id, discount_code
FROM shop.orders
WHERE discount_code IS NOT NULL
  AND VALIDATE_CONVERSION(discount_code AS NUMBER) = 0;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE OR REPLACE FUNCTION shop.format_customer (p_id IN shop.customers.id%TYPE)
  RETURN VARCHAR2
IS
  v_name shop.customers.full_name%TYPE;   -- was VARCHAR2(50)
BEGIN
  SELECT full_name INTO v_name FROM shop.customers WHERE id = p_id;
  RETURN UPPER(v_name);
END;
/

CREATE OR REPLACE FUNCTION shop.region_customer_list (p_region IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_list VARCHAR2(4000 CHAR);
BEGIN
  FOR r IN (SELECT full_name FROM shop.customers WHERE region = p_region) LOOP
    EXIT WHEN NVL(LENGTH(v_list), 0) + LENGTH(r.full_name) + 2 > 4000;
    v_list := v_list || r.full_name || ', ';
  END LOOP;
  RETURN v_list;
END;
/

CREATE OR REPLACE FUNCTION shop.to_number_or_null (p_text IN VARCHAR2)
  RETURN NUMBER
IS
BEGIN
  RETURN TO_NUMBER(p_text);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN NULL;   -- or log the value and re-raise
END;
/

Examples

A variable too small for the value

Not reproduced here: no Oracle Database is installed for this site. The result shows the format of the matching example in Oracle's PL/SQL Language Reference (26ai). Oracle Database 19c prints "numeric or value error" in place of "value or conversion error".

DECLARE
  v_code VARCHAR2(3 CHAR);
BEGIN
  v_code := 'GBP1';
END;
/

ORA-06502: PL/SQL: value or conversion error: character string buffer too small
ORA-06512: at line 4

A constant with more digits than its precision

Not reproduced here: no Oracle Database is installed for this site. The result follows the documented example in Oracle's PL/SQL Language Reference. An error raised in a declaration is not caught by the same block's exception handler; an enclosing block must handle it.

DECLARE
  credit_limit CONSTANT NUMBER(3) := 5000;   -- NUMBER(3) holds up to 999
BEGIN
  NULL;
END;
/

ORA-06502: PL/SQL: value or conversion error: number precision too large
ORA-06512: at line 2

A function result fetched into a small variable

Not reproduced here: no Oracle Database is installed for this site. The message format is from Oracle's error reference; the function name and line number are illustrative. The error is raised in the caller, because the caller's variable is the one that is too small.

DECLARE
  v_label VARCHAR2(20);
BEGIN
  -- shop.format_customer returns the full name in capitals
  v_label := shop.format_customer(1001);
END;
/

ORA-06502: PL/SQL: value or conversion error: character string buffer too small
ORA-06512: at line 5

How to prevent it

Declare variables, parameters and record fields with %TYPE and %ROWTYPE wherever they hold column data, and size free-standing buffers in characters for the largest value they can receive. Validate input that should be numeric before it reaches PL/SQL conversions.

When you widen a column, search the code that reads it (ALL_SOURCE, ALL_DEPENDENCIES) for hard-coded sizes. Log the full error stack in a top-level handler so the line that failed is never lost.

Common questions

Learn more on CodeWithSQL

• Stored procedures and functions
• SQL data types explained

Official documentation

• ORA-06502 (Oracle Database Error Messages)
• CHAR and VARCHAR2 variables (Oracle PL/SQL Language Reference 26ai)
• Predefined exceptions (Oracle PL/SQL Language Reference 19c)
• VALIDATE_CONVERSION (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.