Oracle ORA-01401 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01401: Inserted Value Too Large for Column — What You Need to Know

ORA-01401 is thrown by Oracle when you attempt to INSERT or UPDATE a value that exceeds the maximum size defined for the target column. For example, trying to store an 11-character string into a VARCHAR2(10) column will immediately trigger this error. It is one of the most common data-related errors encountered in both application development and data migration projects.

Note: In Oracle 9i and later, you may more frequently see ORA-12899 instead, which provides additional detail (actual vs. allowed size). ORA-01401 is its predecessor and still appears in certain contexts.

Top 3 Causes

1. String Value Exceeds Column Length

The most frequent cause — inserting user-supplied or application-generated strings without length validation.

-- This will cause ORA-01401 if LAST_NAME is VARCHAR2(10)
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (1001, 'VeryLongLastNameHere', 'test@example.com', SYSDATE, 'IT_PROG');

-- Check column definition first
SELECT column_name, data_type, data_length, char_length
FROM user_tab_columns
WHERE table_name  = 'EMPLOYEES'
  AND column_name = 'LAST_NAME';

-- Check the actual length of the value you are inserting
SELECT LENGTH('VeryLongLastNameHere') AS char_len,
       LENGTHB('VeryLongLastNameHere') AS byte_len
FROM dual;

2. Column Size Mismatch During Data Migration / ETL

When migrating data from MySQL, PostgreSQL, or a legacy system, source column sizes may be larger than what is defined in the Oracle target schema.

-- Compare source vs. target column sizes before migration
SELECT s.column_name,
       s.data_length AS source_len,
       t.data_length AS target_len,
       CASE
           WHEN s.data_length > t.data_length THEN 'MISMATCH - RISK'
           ELSE 'OK'
       END AS status
FROM all_tab_columns s
JOIN all_tab_columns t ON s.column_name = t.column_name
WHERE s.table_name = 'SOURCE_TABLE'
  AND t.table_name = 'TARGET_TABLE';

-- Find the actual max data length in source data
SELECT MAX(LENGTH(your_column))  AS max_char,
       MAX(LENGTHB(your_column)) AS max_byte
FROM source_table;

3. Application Changes Without Corresponding DDL Updates

Business logic changes often increase the length of data being processed, but developers sometimes forget to update the database column size accordingly. This typically goes undetected in dev/test environments using short sample data, only surfacing in production.

-- Expand the column size safely (increasing size is always safe in Oracle)
ALTER TABLE employees
MODIFY (last_name VARCHAR2(100));

-- Recommended: use CHAR semantics for multibyte character support
ALTER TABLE employees
MODIFY (last_name VARCHAR2(100 CHAR));

-- Verify the change
SELECT column_name, data_type, data_length, char_length, char_used
FROM user_tab_columns
WHERE table_name  = 'EMPLOYEES'
  AND column_name = 'LAST_NAME';

Quick Fix Solutions

Situation	Fix
One-off bad data	Use `SUBSTR` to truncate before inserting
Column too small	`ALTER TABLE ... MODIFY` to increase size
ETL mismatch	Pre-validate with metadata comparison query
Multibyte issue	Switch to `VARCHAR2(n CHAR)` semantics

-- Quick workaround: truncate value on insert
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (1001, SUBSTR('VeryLongLastNameHere', 1, 10),
        'test@example.com', SYSDATE, 'IT_PROG');

Prevention Tips

1. Always use CHAR semantics for character columns

Using VARCHAR2(n CHAR) instead of the default VARCHAR2(n) (which is BYTE semantics) prevents unexpected overflows with multibyte characters like Korean, Japanese, or Chinese.

-- Avoid (BYTE semantics — default)
CREATE TABLE users (username VARCHAR2(30));

-- Prefer (CHAR semantics)
CREATE TABLE users (username VARCHAR2(30 CHAR));

2. Validate data lengths at the database level with triggers

Don't rely solely on application-side validation. A BEFORE INSERT OR UPDATE trigger provides a reliable safety net directly in the database.

CREATE OR REPLACE TRIGGER trg_validate_username
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
BEGIN
    IF LENGTH(:NEW.username) > 30 THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Username exceeds maximum length of 30 characters. '
            || 'Provided length: ' || LENGTH(:NEW.username));
    END IF;
END;
/

Related Errors

ORA-12899 — The modern, more descriptive replacement for ORA-01401, showing actual vs. maximum allowed size.
ORA-01438 — Similar concept but for NUMBER columns; triggers when a numeric value exceeds the defined precision or scale.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.