ORA-00910 Specified Length Too Long for Datatype - Resolution Guide

ORA-00910: Specified Length Too Long for Its Datatype

Error Overview

Error Text: ORA-00910: specified length too long for its datatype

This error occurs when you try to create or alter a column with a size that exceeds the maximum allowed for the datatype. It’s most commonly encountered with VARCHAR2 columns when developers exceed the 4000-byte (or 32767-byte in extended mode) limit.

Common Causes

1. VARCHAR2 Column Exceeds Maximum

• Standard mode: VARCHAR2 limit is 4000 bytes
• Extended mode (12c+): VARCHAR2 limit is 32767 bytes
• Attempting to create VARCHAR2(5000) in standard mode

2. RAW Datatype Limit Exceeded

• RAW maximum is 2000 bytes in standard mode
• RAW maximum is 32767 bytes in extended mode

3. NVARCHAR2 Limit Exceeded

• NVARCHAR2 maximum is 2000 characters (4000 bytes) in standard mode
• NVARCHAR2 maximum is 16383 characters in extended mode

4. CHAR/NCHAR Limits

• CHAR maximum is 2000 bytes
• NCHAR maximum is 1000 characters

5. Migration from Other Databases

• MySQL VARCHAR supports up to 65535 bytes
• SQL Server VARCHAR supports up to 8000 bytes
• PostgreSQL VARCHAR supports unlimited length

Diagnostic Queries

Check Current MAX_STRING_SIZE Setting

-- Check if extended datatypes are enabled
SELECT name, value
FROM v$parameter
WHERE name = 'max_string_size';

-- STANDARD = 4000 byte limit for VARCHAR2
-- EXTENDED = 32767 byte limit for VARCHAR2

Review Column Definitions

-- Check existing column sizes in a table
SELECT column_name, data_type, data_length, char_length, char_used
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
ORDER BY column_id;

-- Find all VARCHAR2 columns near the limit
SELECT owner, table_name, column_name, data_length
FROM dba_tab_columns
WHERE data_type = 'VARCHAR2'
  AND data_length >= 3900
ORDER BY data_length DESC;

Check NLS Character Set (Affects Byte vs Char Semantics)

-- Character set determines byte-per-character ratio
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');

-- AL32UTF8: up to 4 bytes per character
-- WE8MSWIN1252: 1 byte per character

Datatype Maximum Lengths Reference

Standard Mode (MAX_STRING_SIZE = STANDARD)

Datatype	SQL Maximum	PL/SQL Maximum
VARCHAR2	4000 bytes	32767 bytes
NVARCHAR2	2000 chars	32767 bytes
RAW	2000 bytes	32767 bytes
CHAR	2000 bytes	32767 bytes
NCHAR	1000 chars	32767 bytes

Extended Mode (MAX_STRING_SIZE = EXTENDED)

Datatype	SQL Maximum	PL/SQL Maximum
VARCHAR2	32767 bytes	32767 bytes
NVARCHAR2	16383 chars	32767 bytes
RAW	32767 bytes	32767 bytes

Resolution Steps

Solution 1: Use CLOB Instead of VARCHAR2

-- For data exceeding VARCHAR2 limits, use CLOB
CREATE TABLE documents (
  doc_id    NUMBER,
  doc_title VARCHAR2(200),
  doc_body  CLOB  -- Up to 4GB, replaces large VARCHAR2
);

-- Alter existing column to CLOB
ALTER TABLE documents MODIFY (doc_body CLOB);

Solution 2: Enable Extended Datatypes (Oracle 12c+)

-- WARNING: This change is IRREVERSIBLE and requires downtime
-- Must be done in UPGRADE mode

-- 1. Shut down the database
SHUTDOWN IMMEDIATE;

-- 2. Start in UPGRADE mode
STARTUP UPGRADE;

-- 3. Change the parameter
ALTER SYSTEM SET max_string_size = EXTENDED SCOPE=SPFILE;

-- 4. Run the required script
@?/rdbms/admin/utl32k.sql

-- 5. Restart normally
SHUTDOWN IMMEDIATE;
STARTUP;

-- Now VARCHAR2 can be up to 32767 bytes
CREATE TABLE large_strings (
  description VARCHAR2(32767)
);

Solution 3: Reduce Column Size to Fit Within Limits

-- Use appropriate sizes instead of oversizing
-- Bad: VARCHAR2(10000) in standard mode
-- Good: VARCHAR2(4000) or CLOB

CREATE TABLE customers (
  name    VARCHAR2(200),    -- Realistic name length
  email   VARCHAR2(254),    -- RFC 5321 max email length
  address VARCHAR2(500),    -- Reasonable address length
  notes   CLOB              -- Unlimited text
);

Solution 4: Use BYTE vs CHAR Semantics Appropriately

-- If using multibyte character set (AL32UTF8),
-- VARCHAR2(4000 CHAR) may exceed byte limit

-- Use BYTE semantics to stay within limits
CREATE TABLE example (
  col1 VARCHAR2(4000 BYTE)   -- Always fits in standard mode
);

-- Or explicitly limit character length
CREATE TABLE example2 (
  col1 VARCHAR2(1000 CHAR)   -- Up to 4000 bytes in AL32UTF8
);

Prevention Strategies

1. Design Standards

-- Establish column sizing standards for your team
-- Common recommendations:
--   Names:      VARCHAR2(100-200)
--   Emails:     VARCHAR2(254)
--   URLs:       VARCHAR2(2000)
--   Codes:      VARCHAR2(10-50)
--   Free text:  CLOB
--   Comments:   VARCHAR2(4000) or CLOB

2. Migration Validation Script

-- Before migrating from another database, check for oversized columns
-- This simulates Oracle limits against source definitions
SELECT table_name, column_name, data_type, character_maximum_length,
  CASE
    WHEN data_type IN ('varchar', 'nvarchar')
     AND character_maximum_length > 4000
    THEN 'NEEDS CLOB OR EXTENDED MODE'
    ELSE 'OK'
  END as oracle_compatibility
FROM information_schema.columns  -- Source database
WHERE character_maximum_length > 4000;

3. Pre-DDL Validation

-- Check max_string_size before creating tables with large columns
DECLARE
  v_max_string VARCHAR2(20);
BEGIN
  SELECT value INTO v_max_string
  FROM v$parameter WHERE name = 'max_string_size';

  IF v_max_string = 'STANDARD' THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: VARCHAR2 limited to 4000 bytes');
    DBMS_OUTPUT.PUT_LINE('Use CLOB for larger columns or enable EXTENDED mode');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Extended mode: VARCHAR2 up to 32767 bytes');
  END IF;
END;
/

Multitenant (CDB/PDB) Considerations

-- In a CDB environment, max_string_size must be set at CDB level
-- All PDBs inherit the setting

-- Check setting across all PDBs
SELECT con_id, name, value
FROM v$system_parameter
WHERE name = 'max_string_size';

-- The change affects ALL PDBs in the container
-- Cannot have different settings per PDB

Related Errors

• ORA-12899: Value Too Large for Column - Data exceeds column size at INSERT/UPDATE time
• ORA-01489: Result of String Concatenation Too Long - Concatenation exceeds VARCHAR2 limit
• ORA-00932: Inconsistent Datatypes - Datatype mismatch errors
• ORA-01438: Value Larger Than Specified Precision - Numeric precision exceeded