DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00910 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00910: Specified Length Too Long for Its Datatype

ORA-00910 is thrown by Oracle when you declare a column or variable with a length that exceeds the maximum allowed for that data type. This most commonly occurs during CREATE TABLE or ALTER TABLE statements, but can also appear in PL/SQL variable declarations. Understanding Oracle's hard limits per data type is the fastest way to resolve and prevent this error.

Top 3 Causes

1. VARCHAR2 or CHAR Length Exceeds Oracle's Limit

Oracle limits VARCHAR2 columns to 4,000 bytes (standard) and CHAR to 2,000 bytes. Developers migrating schemas from MySQL or SQL Server frequently hit this wall because those databases allow larger string columns.

-- Causes ORA-00910
CREATE TABLE orders (
    notes VARCHAR2(5000)   -- Exceeds 4000-byte limit
);

-- Fix: Reduce length or switch to CLOB
CREATE TABLE orders (
    notes VARCHAR2(4000)   -- Within limit
);

-- Fix: Use CLOB for large text
CREATE TABLE orders (
    notes CLOB
);

-- Check current MAX_STRING_SIZE setting
SELECT name, value
FROM v$parameter
WHERE name = 'max_string_size';
Enter fullscreen mode Exit fullscreen mode

2. NVARCHAR2 or NCHAR Exceeds Character Limit

NVARCHAR2 has a maximum of 2,000 characters and NCHAR allows only 1,000 characters. Because these types use the National Character Set (typically AL16UTF16), the byte overhead is higher — Oracle enforces character-count limits rather than byte limits here.

-- Causes ORA-00910
CREATE TABLE translations (
    content NVARCHAR2(4000)  -- Exceeds 2000-character limit
);

-- Fix: Stay within 2000 characters
CREATE TABLE translations (
    content NVARCHAR2(2000)
);

-- Fix: Use NCLOB for large national character data
CREATE TABLE translations (
    content NCLOB
);

-- Verify National Character Set
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter = 'NLS_NCHAR_CHARACTERSET';
Enter fullscreen mode Exit fullscreen mode

3. Incorrect Length in PL/SQL Variable Declaration

PL/SQL variables follow slightly different rules: VARCHAR2 in PL/SQL allows up to 32,767 bytes, but any value above that still triggers ORA-00910. Hardcoded lengths in package specs or dynamic SQL generators are common culprits.

-- Causes ORA-00910 (exceeds PL/SQL VARCHAR2 max)
DECLARE
    v_buffer VARCHAR2(40000);
BEGIN
    NULL;
END;
/

-- Fix: Cap at 32767 or use CLOB
DECLARE
    v_buffer VARCHAR2(32767);
BEGIN
    DBMS_OUTPUT.PUT_LINE('OK');
END;
/

-- Best Fix: Use %TYPE to inherit column definition automatically
DECLARE
    v_notes orders.notes%TYPE;  -- Inherits type and length from column
BEGIN
    NULL;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Data Type Table Column Max PL/SQL Variable Max Large Data Alternative
VARCHAR2 4,000 bytes 32,767 bytes CLOB
CHAR 2,000 bytes 32,767 bytes CLOB
NVARCHAR2 2,000 chars 32,767 bytes NCLOB
NCHAR 1,000 chars 32,767 bytes NCLOB 
-- Alter an existing column to CLOB if data has grown too large
ALTER TABLE orders MODIFY (notes CLOB);

-- Inspect columns near the limit in your schema
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
WHERE (data_type = 'VARCHAR2'  AND data_length >= 3900)
   OR (data_type = 'NVARCHAR2' AND char_length >= 1900)
ORDER BY table_name;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Standardize your type mapping before migration. Always review Oracle's data type limits when porting schemas from other databases. Use %TYPE and %ROWTYPE in PL/SQL to avoid hardcoding lengths that may drift out of sync with DDL changes.

  2. Enable MAX_STRING_SIZE = EXTENDED only if truly needed. Oracle 12c+ allows VARCHAR2 up to 32,767 bytes at the table level with this parameter, but it is irreversible and requires running utl32k.sql. Evaluate whether CLOB is a simpler, safer alternative before making this system-wide change.

Related Errors

  • ORA-01401 – inserted value too large for column: runtime error when DML data exceeds column length.
  • ORA-06502 – character string buffer too small: PL/SQL runtime error when assigned value overflows a variable.
  • ORA-00972 – identifier is too long: object name exceeds the allowed length (30 chars pre-12.2, 128 chars from 12.2+).

📖 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.

Top comments (0)