ORA-00910: specified length too long for its datatype

ORA-00910: specified length too long for its datatype error occurs when a column data type specified length is more than what Oracle supports while creating table, altering table, or declaring a variable in Oracle. All data types in Oracle have a maximum size restriction. If you declare a column with a data type that has a length greater than what Oracle supports, an error message ORA-00910: specified length too long for its datatype will be displayed.

CHAR, NCHAR, and RAW have a maximum byte size of 2000 bytes. Other data types have a maximum capacity of 4000 bytes, such as VARCHAR, VARCHAR2, NVARCHAR2, and so on. If the specified length of a column datatype is more than what Oracle provides, the data type will be unable to create the column. The datatype will throw an error stating that the specified length is greater than the maximum permitted.



When this ORA-00910 error occurs

If you create or alter a table with a column data type size that exceeds what Oracle supports, an error message ORA-00910: specified length too long for its datatype will be displayed. A data type’s specified length should be within the maximum allowed size. If the length is too long, an error message is shown.

Problem

create table dept(
id number primary key,
name char(2001)
);

Error

Error report -
ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW


Root Cause

If the length of a column datatype is more than what Oracle provides, the column cannot be created in a table. The datatype could not allocate more than the maximum number of bytes allowed. As a result, Oracle will throw an error. To resolve the error, the column datatype length should be provided within the maximum allowed byte size.



Solution 1

If the column datatype length exceeds the maximum allowed byte size, decrease the datatype length to stay inside the limit. This will resolve the error. All Oracle datatypes have a maximum byte limit. The value must be given within the maximum allowable byte size.

Problem

create table dept(
id number primary key,
name char(2001)
);

ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

Solution

create table dept(
id number primary key,
name char(2000)
);

Table DEPT created.


Solution 2

If the length of the column datatype exceeds the maximum permitted byte size, switch to a datatype that supports the length. This will fix the problem. The maximum byte restriction applies to all Oracle datatypes. Select the appropriate datatype for the column’s needed length in a table.

Problem

create table dept(
id number primary key,
name char(2001)
);

ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

Solution

create table dept(
id number primary key,
name varchar2(2001)
);

Table DEPT created.


Solution 3

The maximum allowable size is given in byte intervals. When using the char, varchar, or varchar2 datatypes, each character is one byte in size. Each character in a nchar, nvarchar, or nvarchar2 datatype is 2 bytes long. Only half of the characters will be stored by these unicode character datatypes. Changing from a unicode character to an ascii character expands the size.

Problem

create table dept(
id number primary key,
name nvarchar2(2001)
);

ORA-00910: specified length too long for its datatype
00910. 00000 -  "specified length too long for its datatype"
*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
           otherwise, the length specified was > 4000.
*Action:   use a shorter length or switch to a datatype permitting a
           longer length such as a VARCHAR2, LONG CHAR, or LONG RAW

Solution

create table dept(
id number primary key,
name varchar2(2001)
);

Table DEPT created.



Leave a Reply