ORA-06502: Accented Names Broke Checkout (Buffer Too Small)

ORA-06502 is a sizing bug that waits for the right data. Code compiles, passes tests with names like 'John Smith', and then fails in production on the first customer whose name is longer, or simply has accents. The error itself is easy to read once you know its forms. 'Character string buffer too small' means a string didn't fit. 'Number precision too large' means a number didn't. 'Character to number conversion error' means TO_NUMBER was given something that isn't a number. The ORA-06512 line under it tells you exactly where.

This guide covers each form, the byte-versus-character trap behind many production cases, how to find the failing line and value, and the declaration habits that stop it happening again.

What ORA-06502 Actually Means (And Why It's Not a Syntax Error)

ORA-06502 is a runtime error. The code compiles cleanly and fails only when a value that doesn't fit actually arrives. That's why it gets past code review and tests. Read the suffix: 'character string buffer too small' means a string was too long for its variable. 'Number precision too large' means a number exceeded NUMBER(p,s). 'Character to number conversion error' means TO_NUMBER, or an implicit conversion, got a non-numeric string. In plain SQL the same bad conversion raises ORA-01722 instead. PL/SQL never truncates silently. It raises the error instead. Only the failing statement fails: earlier work in the transaction is still pending, and your exception handling decides whether to roll back.

-- io.thecodeforge — Database tutorial
-- Demonstration of ORA-06502: character string buffer too small

DECLARE
  -- Declared a 10-character buffer
  customer_name VARCHAR2(10);
BEGIN
  -- This string is 17 characters long — will fail
  customer_name := 'Jonathan Smithson';
  DBMS_OUTPUT.PUT_LINE('Name: ' || customer_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

The Root Cause: Variable Sizing vs. Real Data

Every 'buffer too small' error comes down to one thing: the target is smaller than the value. The source can be a column, a concatenation, a function result or a parameter. Measure in the right unit. LENGTH() counts characters; LENGTHB() counts bytes. If a variable was declared without CHAR, compare its size with LENGTHB(). That's how a value that fits the column, sized in CHAR, can still overflow a variable sized in bytes. The habit that prevents most cases is %TYPE: the variable inherits the column's exact type, size and length semantics, and follows the column when it changes.

-- io.thecodeforge — Database tutorial
-- Safe variable declaration using %TYPE

DECLARE
  -- Inherit the exact type and size from the table column
  v_customer_name customers.customer_name%TYPE;
  v_shipping_address customers.shipping_address%TYPE;
BEGIN
  SELECT customer_name, shipping_address
    INTO v_customer_name, v_shipping_address
    FROM customers
   WHERE customer_id = 1001;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_customer_name);
  DBMS_OUTPUT.PUT_LINE('Address: ' || v_shipping_address);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Customer not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

How to Find the Exact Line That Failed

The error stack already tells you where it failed. The ORA-06512 line under ORA-06502 names the package, procedure and line number of the failing assignment. Inside an exception handler, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE gives you that same location as a string to log. SQLERRM gives you only the message. Log the location together with the value and its LENGTH and LENGTHB. With those three things, finding the bad row takes minutes instead of hours.

-- io.thecodeforge — Database tutorial
-- Logging the full error backtrace for ORA-06502

CREATE OR REPLACE PROCEDURE process_order(p_order_id NUMBER) IS
  v_customer_name VARCHAR2(10); -- intentionally too small for demo
BEGIN
  SELECT customer_name
    INTO v_customer_name
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
   WHERE o.order_id = p_order_id;

  DBMS_OUTPUT.PUT_LINE('Processing order for: ' || v_customer_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Backtrace:');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;
/

-- Test with a long name
BEGIN
  process_order(1001);
EXCEPTION
  WHEN OTHERS THEN
    NULL; -- already logged
END;
/

The Numeric Variant: When ORA-06502 Isn't About Strings

ORA-06502 isn't only about strings. 'Number precision too large' means a value exceeded NUMBER(p,s): NUMBER(5,2) holds at most 999.99. 'Character to number conversion error' means a string such as '12A' was converted to a number inside PL/SQL. Validate before converting. On 12.2 and later, use TO_NUMBER(x DEFAULT NULL ON CONVERSION ERROR) or VALIDATE_CONVERSION(x AS NUMBER), which handle decimals and signs correctly. A simple REGEXP_LIKE('^[0-9]+$') rejects '12.50' and '-3'.

-- io.thecodeforge — Database tutorial
-- Numeric variant of ORA-06502

DECLARE
  v_price NUMBER(5,2); -- max value 999.99
BEGIN
  -- This will fail: 1234.56 exceeds precision
  v_price := 1234.56;
  DBMS_OUTPUT.PUT_LINE('Price: ' || v_price);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Safe conversion example
DECLARE
  v_input VARCHAR2(10) := '12A';
  v_number NUMBER;
BEGIN
  IF REGEXP_LIKE(v_input, '^[0-9]+$') THEN
    v_number := TO_NUMBER(v_input);
    DBMS_OUTPUT.PUT_LINE('Converted: ' || v_number);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Invalid number: ' || v_input);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

The Fix: Enlarge, Truncate, or Use CLOB

Once you've found the failing assignment, there are three fixes. Enlarge the variable when you need the full value: declare it with %TYPE, or size it in CHAR. This is the right fix in most cases. Truncate with SUBSTR only when losing the end of the value is acceptable, such as a display label or a log line. Use SUBSTRB if the target is sized in bytes. Use a CLOB when the value has no practical upper bound, such as JSON or XML. In PL/SQL, VARCHAR2 can hold up to 32,767 bytes. In SQL columns the limit is 4,000 bytes, or 32,767 with MAX_STRING_SIZE = EXTENDED.

-- io.thecodeforge — Database tutorial
-- Three strategies to fix ORA-06502

-- Strategy 1: Enlarge the variable using %TYPE
DECLARE
  v_name customers.customer_name%TYPE; -- inherits exact size
BEGIN
  SELECT customer_name INTO v_name FROM customers WHERE customer_id = 1001;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/

-- Strategy 2: Truncate the source with SUBSTR
DECLARE
  v_short_name VARCHAR2(10);
  v_full_name VARCHAR2(100) := 'Jonathan Smithson';
BEGIN
  v_short_name := SUBSTR(v_full_name, 1, 10);
  DBMS_OUTPUT.PUT_LINE('Short name: ' || v_short_name);
END;
/

-- Strategy 3: Use CLOB for large text
DECLARE
  v_payload CLOB;
BEGIN
  v_payload := '{"order_id": 1001, "items": [...large JSON...]}';
  DBMS_OUTPUT.PUT_LINE('Payload length: ' || DBMS_LOB.GETLENGTH(v_payload));
END;
/

Preventing ORA-06502 in Production: Defensive Coding Patterns

The best fix is prevention. Here are the patterns I use in every PL/SQL procedure that handles external data.

First, always use %TYPE for variables that read from tables. This eliminates the entire class of 'column grew but variable didn't' bugs.

Second, validate input lengths before assignment. If you're accepting a parameter from an application, check its length against your variable size and either truncate or raise a meaningful error.

Third, add an exception handler that logs the offending value and its length. When ORA-06502 does happen, you'll know exactly which record caused it.

Fourth, test with real production data. If you only test with 'John Doe', you'll never catch the 'Bartholomew Montgomery-Fitzgerald' case.

-- io.thecodeforge — Database tutorial
-- Defensive pattern: sizes come from the table, errors carry context
CREATE OR REPLACE PROCEDURE add_customer(p_name IN VARCHAR2, p_email IN VARCHAR2) IS
  v_name  customers.full_name%TYPE;   -- size and CHAR/BYTE semantics from the column
  v_email customers.email%TYPE;
BEGIN
  v_name  := p_name;
  v_email := p_email;
  INSERT INTO customers (full_name, email) VALUES (v_name, v_email);
EXCEPTION
  WHEN VALUE_ERROR THEN   -- ORA-06502
    RAISE_APPLICATION_ERROR(-20010,
      'Value too large: name ' || LENGTH(p_name) || ' chars / ' || LENGTHB(p_name) || ' bytes, '
      || 'email ' || LENGTH(p_email) || ' chars', TRUE);
END;
/

When ORA-06502 Hits a Trigger: Special Considerations

Triggers often cause ORA-06502 because they copy values into audit tables or variables sized differently from the source. The error is not vague: the stack shows ORA-06502, then ORA-06512 at the trigger's name and line, then ORA-04088 'error during execution of trigger'. Go to that line in USER_SOURCE (TYPE = 'TRIGGER'). If you want to log the offending value from inside the trigger, the log insert must be in an autonomous transaction. Otherwise the failing statement's rollback removes the log row along with everything else.

-- io.thecodeforge — Database tutorial
-- Log the failing value in an AUTONOMOUS transaction, so it survives the rollback
CREATE OR REPLACE PROCEDURE log_value_error(p_src VARCHAR2, p_len NUMBER, p_lenb NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO value_error_log (src, char_len, byte_len, logged_at)
  VALUES (p_src, p_len, p_lenb, SYSTIMESTAMP);
  COMMIT;
END;
/

CREATE OR REPLACE TRIGGER trg_customers_audit
AFTER INSERT OR UPDATE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO customer_audit (customer_id, full_name, changed_at)
  VALUES (:NEW.customer_id, :NEW.full_name, SYSDATE);
EXCEPTION
  WHEN VALUE_ERROR THEN
    log_value_error('TRG_CUSTOMERS_AUDIT', LENGTH(:NEW.full_name), LENGTHB(:NEW.full_name));
    RAISE;
END;
/

The %TYPE and %ROWTYPE Advantage: Never Guess Sizes Again

The single best way to prevent ORA-06502 is to stop hardcoding variable sizes. Use %TYPE for individual columns and %ROWTYPE for entire rows. This makes your code resilient to schema changes and eliminates the most common cause of this error.

%TYPE inherits the exact data type and size from a table column or another variable. If the column is VARCHAR2(100), your variable is VARCHAR2(100). If someone later alters the column to VARCHAR2(200), your variable automatically becomes VARCHAR2(200) the next time the procedure is recompiled.

%ROWTYPE does the same for an entire row. Declare v_customer customers%ROWTYPE and you get a record with fields matching every column in the customers table. This is perfect for SELECT * INTO or for passing rows between procedures.

-- io.thecodeforge — Database tutorial
-- Using %TYPE and %ROWTYPE to prevent ORA-06502

DECLARE
  -- %ROWTYPE: entire row matches table structure
  v_customer customers%ROWTYPE;
BEGIN
  SELECT * INTO v_customer FROM customers WHERE customer_id = 1001;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_customer.customer_name);
  DBMS_OUTPUT.PUT_LINE('Email: ' || v_customer.email);
  DBMS_OUTPUT.PUT_LINE('Address: ' || v_customer.shipping_address);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Customer not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Real Production Debugging: A Step-by-Step Recovery

When ORA-06502 hits production, work through it in order. Step 1: take the full stack from the application log. The ORA-06512 lines give the object and line. Step 2: read that line in USER_SOURCE and identify the target variable and its declared size, including CHAR or BYTE. Step 3: find the data. Compare MAX(LENGTH(col)) and MAX(LENGTHB(col)) in the source with the variable's size. Step 4: fix the declaration (%TYPE or CHAR semantics), then test with the exact failing value.

-- io.thecodeforge — Database tutorial
-- Queries to diagnose ORA-06502 in production

-- 1. Find the longest values in a column
SELECT MAX(LENGTH(customer_name))  AS max_chars,
       MAX(LENGTHB(customer_name)) AS max_bytes,
       COUNT(*) AS total_rows
FROM customers;

-- 2. Find rows that exceed a specific length
SELECT customer_id, customer_name, LENGTH(customer_name) AS name_length
FROM customers
WHERE LENGTH(customer_name) > 50
ORDER BY name_length DESC;

-- 3. Check the declared size of a column
SELECT column_name, data_type, data_length, char_length, char_used
FROM user_tab_columns
WHERE table_name = 'CUSTOMERS'
  AND column_name = 'CUSTOMER_NAME';

-- 4. Find the source line that failed (if you have the line number)
SELECT line, text
FROM user_source
WHERE name = 'PROCESS_ORDER'
  AND type = 'PROCEDURE'
  AND line BETWEEN 1 AND 20
ORDER BY line;