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

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

A PL/SQL variable could not hold the value assigned to it.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

ORA-06502 is raised when PL/SQL assigns a value that does not fit the variable: a string longer than the VARCHAR2 size, text that is not a number, or a number with more digits than NUMBER(p) allows. Older versions call it "numeric or value error"; 23ai prints "value or conversion error".

The text after the colon tells you which case you have. The ORA-06512: at line N under it points at the line of the assignment or SELECT INTO that failed.

Causes and fixes

  1. A VARCHAR2 variable is shorter than the column value
  2. Assigning text such as '1,200' to a NUMBER variable
  3. A NUMBER(p) variable is too small for the result

1A VARCHAR2 variable is shorter than the column value

The block reads name into v_name VARCHAR2(10). The first customer, Kim, printed fine. The second one, Northwind Trading Company, is 25 characters, and the loop stopped with "character string buffer too small" at line 5, the SELECT INTO.

Declare the variable as customer.name%TYPE so it always has the column's size. After that change both rows printed.

Setup
Setuprun as lab_a
CREATE TABLE customer (cust_id NUMBER PRIMARY KEY, name VARCHAR2(50));
INSERT INTO customer VALUES (1, 'Kim');
INSERT INTO customer VALUES (2, 'Northwind Trading Company');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_name VARCHAR2(10);
BEGIN
  FOR r IN (SELECT cust_id FROM customer ORDER BY cust_id) LOOP
    SELECT name INTO v_name FROM customer WHERE cust_id = r.cust_id;
    DBMS_OUTPUT.PUT_LINE(r.cust_id || ': ' || v_name);
  END LOOP;
END;
/
OUTPUT
1: Kim
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character string buffer too small
ORA-06512: at line 5
ORA-06512: at line 5
Help: https://docs.oracle.com/error-help/db/ora-06502/
Checkrun as lab_a
SELECT cust_id, LENGTH(name) AS len FROM customer ORDER BY cust_id;
OUTPUT
   CUST_ID        LEN
---------- ----------
         1          3
         2         25

2 rows selected.

LENGTH(name) showed 3 and 25. Code that worked in testing often fails only when a long value finally shows up.

After the fixrun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_name customer.name%TYPE;
BEGIN
  FOR r IN (SELECT cust_id FROM customer ORDER BY cust_id) LOOP
    SELECT name INTO v_name FROM customer WHERE cust_id = r.cust_id;
    DBMS_OUTPUT.PUT_LINE(r.cust_id || ': ' || v_name);
  END LOOP;
END;
/
OUTPUT
1: Kim
2: Northwind Trading Company

PL/SQL procedure successfully completed.

2Assigning text such as '1,200' to a NUMBER variable

Uploaded quantities are stored as text and assigned to v_qty NUMBER. Line 2 has '1,200' with a thousands separator, so the implicit conversion failed with "character to number conversion error" at line 6, v_qty := r.qty_text;.

VALIDATE_CONVERSION(qty_text AS NUMBER) = 0 lists the rows that cannot be converted; here only line 2. Converting with a format, TO_NUMBER(r.qty_text, '999G999'), accepted the comma, and the block printed total=1217.

Setup
Setuprun as lab_a
CREATE TABLE upload_line (line_no NUMBER, qty_text VARCHAR2(20));
INSERT INTO upload_line VALUES (1, '10');
INSERT INTO upload_line VALUES (2, '1,200');
INSERT INTO upload_line VALUES (3, '7');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_qty   NUMBER;
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT line_no, qty_text FROM upload_line ORDER BY line_no) LOOP
    v_qty := r.qty_text;
    v_total := v_total + v_qty;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('total=' || v_total);
END;
/
OUTPUT
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character to number conversion error
ORA-06512: at line 6
ORA-06512: at line 6
Help: https://docs.oracle.com/error-help/db/ora-06502/
Checkrun as lab_a
SELECT line_no, qty_text FROM upload_line
 WHERE VALIDATE_CONVERSION(qty_text AS NUMBER) = 0;
OUTPUT
   LINE_NO QTY_TEXT
---------- --------------------
         2 1,200

1 row selected.

G is the group separator of the session's NLS settings. If your data uses a different separator, check the rows with VALIDATE_CONVERSION first.

After the fixrun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_qty   NUMBER;
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT line_no, qty_text FROM upload_line ORDER BY line_no) LOOP
    v_qty := TO_NUMBER(r.qty_text, '999G999');
    v_total := v_total + v_qty;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('total=' || v_total);
END;
/
OUTPUT
total=1217

PL/SQL procedure successfully completed.

3A NUMBER(p) variable is too small for the result

v_sum NUMBER(3) can hold up to 999, but SUM(amount) was 1270. The SELECT INTO at line 4 failed with "number precision too large".

Declare the variable as orders.amount%TYPE (NUMBER(10,2) here). The block then printed sum=1270.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, amount NUMBER(10,2));
INSERT INTO orders VALUES (1, 300);
INSERT INTO orders VALUES (2, 450);
INSERT INTO orders VALUES (3, 520);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_sum NUMBER(3);
BEGIN
  SELECT SUM(amount) INTO v_sum FROM orders;
  DBMS_OUTPUT.PUT_LINE('sum=' || v_sum);
END;
/
OUTPUT
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: number precision too large
ORA-06512: at line 4
Help: https://docs.oracle.com/error-help/db/ora-06502/
Checkrun as lab_a
SELECT SUM(amount) AS total FROM orders;
OUTPUT
     TOTAL
----------
      1270

1 row selected.

Totals grow over time. Size variables for sums from the column type, not from the values you see today.

After the fixrun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_sum orders.amount%TYPE;
BEGIN
  SELECT SUM(amount) INTO v_sum FROM orders;
  DBMS_OUTPUT.PUT_LINE('sum=' || v_sum);
END;
/
OUTPUT
sum=1270

PL/SQL procedure successfully completed.

FAQ

What does "character string buffer too small" mean in ORA-06502?

The value is longer than the VARCHAR2 variable. In this lab a 25-character name went into VARCHAR2(10). Use table.column%TYPE for the variable.

Is ORA-06502 the same as "numeric or value error"?

Yes. That is the older wording. In 23ai the message reads "PL/SQL: value or conversion error" followed by the specific reason.

How do I find which rows cause a character to number conversion error?

Run SELECT ... WHERE VALIDATE_CONVERSION(col AS NUMBER) = 0. In this lab it returned only the row with '1,200'.

How do I find the line that raised ORA-06502?

Look at ORA-06512: at line N below the error. In these examples it pointed at the SELECT INTO or the assignment that failed.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors