ORA-12899 Value Too Large for Column - Data Size and Length Errors

ORA-12899: Value Too Large for Column

Error Overview

Error Text: ORA-12899: value too large for column [schema].[table].[column] (actual: [actual_length], maximum: [max_length])

This error occurs when attempting to insert or update data that exceeds the defined column size. The actual data length is larger than the maximum allowed length for the column, which can happen with VARCHAR2, CHAR, or other size-limited data types.

Understanding Column Size Limits

Oracle Data Type Limits

-- Common data types and their limits
VARCHAR2(n)     -- n bytes (1-4000 in table, 1-32767 in PL/SQL)
CHAR(n)         -- n bytes (1-2000)
NVARCHAR2(n)    -- n characters (1-2000)
NCHAR(n)        -- n characters (1-1000)
RAW(n)          -- n bytes (1-2000)

Character Set Considerations

-- Single-byte character sets (like US7ASCII)
'ABC' = 3 bytes

-- Multi-byte character sets (like UTF8)
'ABC' = 3 bytes (ASCII characters)
'ABÇ' = 4 bytes (Ç takes 2 bytes in UTF8)
'中文' = 6 bytes (each Chinese character takes 3 bytes)

Common Causes

Data Length Exceeds Column Definition

-- Column defined as VARCHAR2(10)
INSERT INTO users (username) VALUES ('this_is_too_long');  -- 16 characters, ERROR

-- Column defined as VARCHAR2(50) but using multi-byte characters
INSERT INTO users (description) VALUES ('这是一个很长的中文描述文本');  -- May exceed 50 bytes

Application Data Changes

• Input validation removed or modified
• Data migration from systems with larger fields
• Concatenation operations producing longer strings
• User input exceeding expected limits

Character Set Issues

-- Database character set affects byte calculations
SELECT
    LENGTHB('Hello World') as bytes,      -- Byte length
    LENGTH('Hello World') as characters   -- Character length
FROM dual;

Diagnostic Queries

Check Column Definition

-- Get column information
SELECT
    column_name,
    data_type,
    data_length,
    char_length,
    char_used
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE'
AND column_name = 'YOUR_COLUMN';

-- For all tables
SELECT
    table_name,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE'
ORDER BY column_id;

Analyze Data Lengths

-- Check actual data lengths in table
SELECT
    column_name,
    LENGTH(column_name) as char_length,
    LENGTHB(column_name) as byte_length,
    column_name as sample_data
FROM your_table
WHERE LENGTH(column_name) > expected_length
ORDER BY byte_length DESC;

-- Check maximum data lengths
SELECT
    MAX(LENGTH(column_name)) as max_char_length,
    MAX(LENGTHB(column_name)) as max_byte_length,
    AVG(LENGTH(column_name)) as avg_char_length
FROM your_table;

Find Problematic Records

-- Identify records that would cause ORA-12899
SELECT *
FROM staging_table
WHERE LENGTHB(description) > 100  -- Assuming target column is VARCHAR2(100)
ORDER BY LENGTHB(description) DESC;

-- Count problematic records
SELECT
    CASE
        WHEN LENGTHB(column_name) <= 50 THEN '0-50 bytes'
        WHEN LENGTHB(column_name) <= 100 THEN '51-100 bytes'
        WHEN LENGTHB(column_name) <= 200 THEN '101-200 bytes'
        ELSE 'Over 200 bytes'
    END as size_range,
    COUNT(*) as record_count
FROM your_table
GROUP BY
    CASE
        WHEN LENGTHB(column_name) <= 50 THEN '0-50 bytes'
        WHEN LENGTHB(column_name) <= 100 THEN '51-100 bytes'
        WHEN LENGTHB(column_name) <= 200 THEN '101-200 bytes'
        ELSE 'Over 200 bytes'
    END
ORDER BY record_count DESC;

Resolution Strategies

1. Increase Column Size

Expand Column Definition

-- Increase VARCHAR2 column size
ALTER TABLE your_table MODIFY column_name VARCHAR2(200);

-- Change from BYTE to CHAR semantics (for multi-byte characters)
ALTER TABLE your_table MODIFY column_name VARCHAR2(100 CHAR);

-- Verify the change
SELECT column_name, data_length, char_length, char_used
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE'
AND column_name = 'COLUMN_NAME';

Considerations for Column Expansion

-- Check table size and impact
SELECT
    table_name,
    num_rows,
    blocks,
    avg_row_len
FROM user_tables
WHERE table_name = 'YOUR_TABLE';

-- Check indexes that might be affected
SELECT
    index_name,
    column_name,
    column_position
FROM user_ind_columns
WHERE table_name = 'YOUR_TABLE'
AND column_name = 'YOUR_COLUMN';

2. Data Truncation

Safe Truncation with Validation

-- Create function for safe truncation
CREATE OR REPLACE FUNCTION safe_truncate(
    p_input VARCHAR2,
    p_max_bytes NUMBER,
    p_suffix VARCHAR2 DEFAULT '...'
) RETURN VARCHAR2 AS
    l_result VARCHAR2(32767);
    l_suffix_len NUMBER := LENGTHB(p_suffix);
BEGIN
    IF LENGTHB(p_input) <= p_max_bytes THEN
        RETURN p_input;
    ELSE
        -- Truncate to fit with suffix
        l_result := SUBSTRB(p_input, 1, p_max_bytes - l_suffix_len) || p_suffix;
        RETURN l_result;
    END IF;
END;
/

-- Use in INSERT/UPDATE
INSERT INTO your_table (description)
VALUES (safe_truncate(long_description, 100));

Batch Data Truncation

-- Update existing data with truncation
UPDATE your_table
SET description = SUBSTRB(description, 1, 100)
WHERE LENGTHB(description) > 100;

-- Verify truncation
SELECT
    COUNT(*) as total_records,
    COUNT(CASE WHEN LENGTHB(description) > 100 THEN 1 END) as oversized_records
FROM your_table;

3. Data Validation and Cleansing

Pre-Insert Validation

-- Create validation procedure
CREATE OR REPLACE PROCEDURE validate_data_length(
    p_table_name VARCHAR2,
    p_column_name VARCHAR2,
    p_data VARCHAR2
) AS
    l_max_length NUMBER;
    l_actual_length NUMBER := LENGTHB(p_data);
BEGIN
    -- Get column max length
    SELECT data_length INTO l_max_length
    FROM user_tab_columns
    WHERE table_name = UPPER(p_table_name)
    AND column_name = UPPER(p_column_name);

    IF l_actual_length > l_max_length THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Data too long: ' || l_actual_length ||
            ' bytes, maximum: ' || l_max_length || ' bytes');
    END IF;
END;
/

Data Cleaning Procedures

-- Clean common data issues
CREATE OR REPLACE FUNCTION clean_data(p_input VARCHAR2)
RETURN VARCHAR2 AS
    l_result VARCHAR2(32767) := p_input;
BEGIN
    -- Remove leading/trailing spaces
    l_result := TRIM(l_result);

    -- Replace multiple spaces with single space
    l_result := REGEXP_REPLACE(l_result, '[ ]{2,}', ' ');

    -- Remove control characters
    l_result := REGEXP_REPLACE(l_result, '[[:cntrl:]]', '');

    RETURN l_result;
END;
/

Advanced Solutions

Dynamic Column Sizing

-- Analyze data and suggest optimal column sizes
WITH data_analysis AS (
    SELECT
        'YOUR_COLUMN' as column_name,
        MAX(LENGTHB(your_column)) as max_bytes,
        AVG(LENGTHB(your_column)) as avg_bytes,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY LENGTHB(your_column)) as p95_bytes
    FROM your_table
)
SELECT
    column_name,
    max_bytes,
    ROUND(avg_bytes, 2) as avg_bytes,
    ROUND(p95_bytes, 2) as p95_bytes,
    -- Suggested size with 20% buffer
    CEIL(max_bytes * 1.2) as suggested_size
FROM data_analysis;

Migration Strategy for Large Tables

-- Step 1: Create staging table with larger column
CREATE TABLE your_table_new AS
SELECT * FROM your_table WHERE 1=0;

ALTER TABLE your_table_new MODIFY column_name VARCHAR2(500);

-- Step 2: Migrate data in batches
DECLARE
    CURSOR c_data IS
        SELECT rowid, your_column FROM your_table;
    TYPE t_batch IS TABLE OF c_data%ROWTYPE;
    l_batch t_batch;
    l_batch_size NUMBER := 10000;
BEGIN
    OPEN c_data;
    LOOP
        FETCH c_data BULK COLLECT INTO l_batch LIMIT l_batch_size;
        EXIT WHEN l_batch.COUNT = 0;

        FORALL i IN 1..l_batch.COUNT
            INSERT INTO your_table_new
            SELECT * FROM your_table WHERE rowid = l_batch(i).rowid;

        COMMIT;
    END LOOP;
    CLOSE c_data;
END;
/

-- Step 3: Rename tables
DROP TABLE your_table;
RENAME your_table_new TO your_table;

Character Set Conversion

-- Check character set information
SELECT
    parameter,
    value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

-- Convert data for different character sets
SELECT
    original_text,
    CONVERT(original_text, 'UTF8', 'US7ASCII') as converted_text,
    LENGTHB(original_text) as original_bytes,
    LENGTHB(CONVERT(original_text, 'UTF8', 'US7ASCII')) as converted_bytes
FROM your_table;

Prevention Strategies

Application-Level Validation

// Java example for input validation
public void validateInputLength(String input, String fieldName, int maxBytes) {
    if (input != null && input.getBytes("UTF-8").length > maxBytes) {
        throw new ValidationException(
            fieldName + " exceeds maximum length of " + maxBytes + " bytes"
        );
    }
}

Database Constraints

-- Add check constraint for length validation
ALTER TABLE your_table
ADD CONSTRAINT chk_description_length
CHECK (LENGTHB(description) <= 500);

-- Add constraint with custom error message
ALTER TABLE your_table
ADD CONSTRAINT chk_name_length
CHECK (LENGTH(name) <= 100);

Trigger-Based Validation

-- Create validation trigger
CREATE OR REPLACE TRIGGER trg_validate_length
    BEFORE INSERT OR UPDATE ON your_table
    FOR EACH ROW
BEGIN
    IF LENGTHB(:NEW.description) > 500 THEN
        RAISE_APPLICATION_ERROR(-20002,
            'Description too long: ' || LENGTHB(:NEW.description) ||
            ' bytes (max: 500 bytes)');
    END IF;
END;
/

Monitoring and Reporting

Length Monitoring Query

-- Regular monitoring of data lengths
SELECT
    table_name,
    column_name,
    data_length as defined_length,
    current_max_length,
    utilization_pct,
    CASE
        WHEN utilization_pct > 90 THEN 'HIGH RISK'
        WHEN utilization_pct > 75 THEN 'MEDIUM RISK'
        ELSE 'LOW RISK'
    END as risk_level
FROM (
    SELECT
        'YOUR_TABLE' as table_name,
        'YOUR_COLUMN' as column_name,
        (SELECT data_length FROM user_tab_columns
         WHERE table_name = 'YOUR_TABLE' AND column_name = 'YOUR_COLUMN') as data_length,
        MAX(LENGTHB(your_column)) as current_max_length,
        ROUND((MAX(LENGTHB(your_column)) /
               (SELECT data_length FROM user_tab_columns
                WHERE table_name = 'YOUR_TABLE' AND column_name = 'YOUR_COLUMN')) * 100, 2) as utilization_pct
    FROM your_table
);

Automated Alert System

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE check_column_utilization AS
BEGIN
    FOR rec IN (
        SELECT table_name, column_name, data_length
        FROM user_tab_columns
        WHERE data_type LIKE '%VARCHAR%'
        AND data_length > 0
    ) LOOP
        DECLARE
            l_max_length NUMBER;
            l_utilization NUMBER;
            l_sql VARCHAR2(4000);
        BEGIN
            l_sql := 'SELECT NVL(MAX(LENGTHB(' || rec.column_name || ')), 0) FROM ' || rec.table_name;
            EXECUTE IMMEDIATE l_sql INTO l_max_length;

            l_utilization := (l_max_length / rec.data_length) * 100;

            IF l_utilization > 90 THEN
                DBMS_OUTPUT.PUT_LINE(
                    'WARNING: ' || rec.table_name || '.' || rec.column_name ||
                    ' utilization: ' || ROUND(l_utilization, 2) || '%'
                );
            END IF;
        END;
    END LOOP;
END;
/

Emergency Procedures

Quick Fix for Production Issues

-- Emergency truncation procedure
CREATE OR REPLACE PROCEDURE emergency_truncate(
    p_table_name VARCHAR2,
    p_column_name VARCHAR2
) AS
    l_max_length NUMBER;
    l_sql VARCHAR2(4000);
BEGIN
    -- Get column max length
    SELECT data_length INTO l_max_length
    FROM user_tab_columns
    WHERE table_name = UPPER(p_table_name)
    AND column_name = UPPER(p_column_name);

    -- Truncate data that exceeds limit
    l_sql := 'UPDATE ' || p_table_name ||
             ' SET ' || p_column_name || ' = SUBSTRB(' || p_column_name || ', 1, ' || l_max_length || ')' ||
             ' WHERE LENGTHB(' || p_column_name || ') > ' || l_max_length;

    EXECUTE IMMEDIATE l_sql;

    DBMS_OUTPUT.PUT_LINE('Truncated ' || SQL%ROWCOUNT || ' records');
    COMMIT;
END;
/

Rollback Strategy

-- Create backup before modifications
CREATE TABLE your_table_backup AS
SELECT * FROM your_table
WHERE LENGTHB(problem_column) > current_limit;

-- After fixing, verify and drop backup
SELECT COUNT(*) FROM your_table_backup;
-- DROP TABLE your_table_backup;

Related Errors

• ORA-01401: Inserted value too large for column
• ORA-06502: PL/SQL numeric or value error
• ORA-22835: Buffer too small for CLOB to CHAR conversion

Best Practices

1. Define appropriate column sizes during design
2. Implement application-level validation
3. Monitor column utilization regularly
4. Use CHAR semantics for multi-byte character sets
5. Test with realistic data volumes
6. Document data length requirements

Troubleshooting Checklist

• Identify the specific column and table
• Check current column definition
• Analyze actual data lengths
• Determine if column expansion is possible
• Consider data truncation options
• Implement validation procedures
• Test solution with sample data
• Monitor post-implementation