ORA-00902: invalid datatype

ORA-00902: incorrect datatype error occurs when a column is declared with an invalid data type that Oracle does not support. when creating a table, altering a table, or declaring a variable in PL/SQL. The create table command creates a collection of columns with the data types given. The data type must be one that is supported by Oracle. Oracle will throw an incorrect data type error if any other data type is specified.

Oracle does not support data types such as boolean, string, and so on. If you attempt to create a table with one or more columns of an unsupported data type, Oracle is unable to determine the data type. As a result, Oracle will display the error message ORA-00902: incorrect datatype.

When this ORA-00902 error occurs

If you create a table with a column declared with an unsupported data type or alter a table with an unsupported data type, the error ORA-00902: incorrect datatype will be shown. The columns in a table must be declared with oracle supported data types.

CREATE TABLE EMP(
id numbers, 
name VARCHAR2(100)
)

Error

Error starting at line : 4 in command -
CREATE TABLE EMP(
id numbers, 
name VARCHAR2(100)
)
Error report -
ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Root Cause

Oracle supports a number of data types that may be declared and used in an Oracle database. Oracle also provides the option of creating a user-defined data type. Oracle will not recognize an unsupported data type if a table contains one or more columns with unsupported data types. When creating or altering a table, Oracle will give an error stating that an incorrect data type was detected.

Solution 1

If an unsupported data type is defined when creating or altering a table, replace the data type to an Oracle supported data type. Check to see if an Oracle data type is supported by a certain Oracle version. Change the data type to one that is supported by the Oracle version. This will address the problem.

Problem

CREATE TABLE EMP(
id numbers, 
name VARCHAR2(100)
)

Error report -
ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

Solution

CREATE TABLE EMP(
id number, 
name VARCHAR2(100)
)

Solution 2

The data types supported by Oracle are as follows. The list may differ from one Oracle version to another. Check to see if the Oracle version you’re using is supported.

Character data types

char(size), nchar(size)
varchar2(size), nvarchar2(size)
long
raw, long raw

Numeric data types

number(precision, scale), numeric(precision, scale),
integer, int, smallint,
real,  double, double precision,
float, dec(precision, scale), decimal(precision, scale)

Date data types

date, timestamp

Large Object Data types

blob, clob, nclob, bfile

Rowid data type

rowid, nrowid