ORA-06502: PL/SQL: value or conversion error
ORA-06502: PL/SQL: value or conversion error: character string buffer too smallA 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
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
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;Table created.
1 row created.
1 row created.
Commit complete.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;
/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/SELECT cust_id, LENGTH(name) AS len FROM customer ORDER BY cust_id; 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.
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;
/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
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;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.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;
/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/SELECT line_no, qty_text FROM upload_line
WHERE VALIDATE_CONVERSION(qty_text AS NUMBER) = 0; 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.
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;
/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
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;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.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;
/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/SELECT SUM(amount) AS total FROM orders; TOTAL
----------
1270
1 row selected.Totals grow over time. Size variables for sums from the column type, not from the values you see today.
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;
/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