DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00902 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00902: Invalid Datatype – Causes, Fixes, and Prevention

ORA-00902 is thrown by Oracle when a SQL statement references a datatype that is either misspelled, unsupported, or not valid in the Oracle context. This error most commonly appears in CREATE TABLE, ALTER TABLE, or CAST() expressions. It is especially frequent during database migrations from MySQL, SQL Server, or PostgreSQL to Oracle, where datatype names differ significantly.

Top 3 Causes and Fixes

Cause 1: Using Non-Oracle Datatypes (Migration Issues)

Oracle does not support datatypes like INT (as a standalone alias in all contexts), DATETIME, BOOL, TINYINT, or TEXT the same way other databases do.

Problematic SQL (MySQL syntax):

-- Causes ORA-00902 in Oracle
CREATE TABLE orders (
    order_id   INT AUTO_INCREMENT,
    is_shipped BOOL,
    order_date DATETIME,
    notes      TEXT,
    PRIMARY KEY (order_id)
);
Enter fullscreen mode Exit fullscreen mode

Fixed Oracle SQL:

-- Correct Oracle syntax
CREATE TABLE orders (
    order_id   NUMBER(10)   GENERATED ALWAYS AS IDENTITY,
    is_shipped NUMBER(1)    DEFAULT 0 CHECK (is_shipped IN (0,1)),
    order_date DATE,
    notes      CLOB,
    PRIMARY KEY (order_id)
);
Enter fullscreen mode Exit fullscreen mode

Quick Reference – Datatype Mapping:

Other DBMS Oracle Equivalent
INT / TINYINT NUMBER(10) / NUMBER(3)
DATETIME DATE or TIMESTAMP
BOOL NUMBER(1) or CHAR(1)
TEXT CLOB
FLOAT(p,s) NUMBER(p,s)

Cause 2: Typos in Datatype Names

A simple typo in a datatype keyword causes Oracle's parser to fail immediately with ORA-00902.

Problematic SQL:

-- Typos cause ORA-00902
CREATE TABLE customers (
    cust_id    NUMBR(10),        -- typo: should be NUMBER
    cust_name  VARCAR2(100),     -- typo: should be VARCHAR2
    created_at TIMESTMP          -- typo: should be TIMESTAMP
);
Enter fullscreen mode Exit fullscreen mode

Fixed SQL:

-- Correct spelling of Oracle datatypes
CREATE TABLE customers (
    cust_id    NUMBER(10)     NOT NULL,
    cust_name  VARCHAR2(100)  NOT NULL,
    created_at TIMESTAMP      DEFAULT SYSTIMESTAMP,
    PRIMARY KEY (cust_id)
);
Enter fullscreen mode Exit fullscreen mode

You can verify valid Oracle built-in types with:

SELECT TYPE_NAME
FROM   DBA_TYPES
WHERE  PREDEFINED = 'YES'
ORDER  BY TYPE_NAME;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Invalid Datatype in CAST() or Dynamic SQL

Using unsupported types inside a CAST() function is another common trigger for ORA-00902.

Problematic SQL:

-- ORA-00902: INT and DATETIME not valid in CAST
SELECT CAST(salary AS INT),
       CAST(hire_date AS DATETIME)
FROM   employees;
Enter fullscreen mode Exit fullscreen mode

Fixed SQL:

-- Use Oracle-supported types in CAST
SELECT CAST(salary    AS NUMBER(10)),
       CAST(hire_date AS TIMESTAMP),
       CAST(emp_name  AS VARCHAR2(100))
FROM   employees;

-- Alternatively, use Oracle conversion functions
SELECT TO_NUMBER(salary),
       TO_DATE(hire_date, 'YYYY-MM-DD')
FROM   employees;
Enter fullscreen mode Exit fullscreen mode

Quick Prevention Tips

1. Standardize on Oracle datatypes from the start.
Maintain a team-level DDL coding standard document that lists approved Oracle datatypes. Always review DDL scripts against the Oracle documentation before executing in any environment.

2. Validate DDL in dev/test before production.
Never run migrated or newly written DDL scripts directly in production. Use tools like Oracle SQL Developer, AWS Schema Conversion Tool (SCT), or Oracle Migration Workbench to automatically convert foreign datatypes and catch ORA-00902 before it causes downtime.

-- Always test DDL in a sandbox schema first
CREATE TABLE sandbox.test_table (
    id   NUMBER(10),
    name VARCHAR2(100)
);
-- Confirm success before promoting to production
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00904 – invalid identifier: Often occurs alongside ORA-00902 in malformed DDL statements.
  • ORA-00907 – missing right parenthesis: Triggered when datatype declarations have unmatched parentheses.
  • ORA-01722 – invalid number: A runtime error related to datatype conversion failures.

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