ORA-00902: Invalid Datatype - Fix Column Type Errors

ORA-00902: Invalid Datatype

Error Overview

Error Text: ORA-00902: invalid datatype

The ORA-00902 error is raised when Oracle encounters an unrecognized or unsupported data type in a SQL statement. This most commonly occurs in CREATE TABLE, ALTER TABLE, or CAST expressions where a data type name is misspelled, borrowed from another database platform, or used in an incompatible Oracle version.

Common Causes

1. Misspelled Data Type Names

• INTERGER instead of INTEGER
• VARCHA2 instead of VARCHAR2
• NUMER instead of NUMBER
• BOOL instead of using a NUMBER(1) or CHAR(1) workaround

2. Non-Oracle Data Types Used

• MySQL-specific types: TINYINT, MEDIUMINT, BIGINT, TEXT, ENUM
• SQL Server types: NVARCHAR(MAX), BIT, DATETIME2, UNIQUEIDENTIFIER
• PostgreSQL types: BOOLEAN, SERIAL, BYTEA, UUID

3. Version-Specific Types Used on Older Databases

• JSON type introduced in Oracle 21c / partially in 12.2 — not available in 11g/12.1
• VECTOR type introduced in Oracle 23ai
• BOOLEAN SQL-level type introduced in Oracle 23ai

4. Incorrect Syntax in CAST or CONVERT

• Invalid target type inside CAST(expr AS type)
• Missing precision where required (e.g., VARCHAR without length)

Diagnostic Queries

List All Valid Oracle Data Types

-- View built-in types available in this database version
SELECT type_name, typecode
FROM all_types
WHERE predefined = 'YES'
ORDER BY type_name;

-- Check database version to understand type availability
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';

Find Tables with Potentially Problematic Column Types

-- Audit all column data types in a schema
SELECT
  owner,
  table_name,
  column_name,
  data_type,
  data_length,
  data_precision,
  data_scale,
  nullable
FROM all_tab_columns
WHERE owner = 'HR'
ORDER BY table_name, column_id;

Check Recent DDL Errors in Audit Trail

-- Find recent DDL failures that may indicate bad type usage
SELECT
  event_timestamp,
  db_user_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 902
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY event_timestamp DESC;

Check Object Types in Schema

-- Review user-defined types for conflicts
SELECT
  type_name,
  typecode,
  attributes,
  methods,
  status
FROM all_types
WHERE owner = 'HR'
ORDER BY type_name;

Step-by-Step Resolution

1. Correct Misspelled Type Names

-- WRONG
-- CREATE TABLE orders (
--   order_id    INTERGER,
--   status      VARCHA2(20),
--   amount      NUMER(10,2)
-- );

-- CORRECT
CREATE TABLE orders (
  order_id    INTEGER,
  status      VARCHAR2(20),
  amount      NUMBER(10,2)
);

2. Replace Non-Oracle Types with Oracle Equivalents

-- Migration mapping from MySQL/SQL Server to Oracle

-- WRONG (MySQL types)
-- CREATE TABLE products (
--   id          BIGINT AUTO_INCREMENT,
--   name        TEXT,
--   active      BOOL,
--   price       DECIMAL(10,2)
-- );

-- CORRECT (Oracle equivalents)
CREATE TABLE products (
  id          NUMBER(19)     GENERATED ALWAYS AS IDENTITY,
  name        CLOB,                 -- TEXT -> CLOB (or VARCHAR2 up to 32767)
  active      NUMBER(1,0),          -- BOOL -> NUMBER(1,0) check (0 or 1)
  price       NUMBER(10,2)          -- DECIMAL is a synonym for NUMBER in Oracle
);

-- WRONG (SQL Server types)
-- CREATE TABLE users (
--   user_id   UNIQUEIDENTIFIER,
--   created   DATETIME2,
--   flags     BIT
-- );

-- CORRECT
CREATE TABLE users (
  user_id   RAW(16),               -- UNIQUEIDENTIFIER -> RAW(16) or use SYS_GUID()
  created   TIMESTAMP,             -- DATETIME2 -> TIMESTAMP
  flags     NUMBER(1,0)            -- BIT -> NUMBER(1,0)
);

3. Fix Version-Specific Type Usage

-- Check Oracle version before using newer types
SELECT version FROM v$instance;

-- Oracle 23ai: BOOLEAN is now a native SQL type
-- CREATE TABLE flags (is_active BOOLEAN);  -- Only valid 23ai+

-- For 19c and earlier: use NUMBER(1) with a check constraint
CREATE TABLE flags (
  is_active NUMBER(1,0) DEFAULT 0 NOT NULL,
  CONSTRAINT chk_is_active CHECK (is_active IN (0, 1))
);

-- Oracle 21c+: JSON is a native type
-- For older versions, store JSON as CLOB with IS JSON constraint
CREATE TABLE json_docs (
  id      NUMBER GENERATED ALWAYS AS IDENTITY,
  payload CLOB,
  CONSTRAINT chk_json CHECK (payload IS JSON)
);

4. Fix CAST Expression with Invalid Type

-- WRONG: VARCHAR is not a valid standalone type in some contexts
-- SELECT CAST(hire_date AS VARCHAR) FROM employees;

-- CORRECT
SELECT CAST(hire_date AS VARCHAR2(30)) FROM employees;

-- WRONG: FLOAT without precision when used in CAST
-- SELECT CAST(salary AS FLOAT) FROM employees;

-- CORRECT
SELECT CAST(salary AS FLOAT(10)) FROM employees;
-- or
SELECT CAST(salary AS NUMBER(10,2)) FROM employees;

5. Fix ALTER TABLE Column Type Changes

-- Check current column definition before altering
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
  AND column_name = 'PHONE_NUMBER';

-- WRONG
-- ALTER TABLE employees MODIFY phone_number TEXT;

-- CORRECT
ALTER TABLE employees MODIFY phone_number VARCHAR2(50);

Prevention Strategies

1. Use a Type Reference Script Before Migrations

-- Standard Oracle type reference for migration work
-- Run this before writing DDL for a new schema

-- Numeric types
-- NUMBER(p,s)   : exact numeric, p=precision, s=scale
-- FLOAT(p)      : binary float approximation
-- BINARY_FLOAT  : 32-bit IEEE 754
-- BINARY_DOUBLE : 64-bit IEEE 754
-- INTEGER       : synonym for NUMBER(38)

-- Character types
-- VARCHAR2(n)   : variable-length up to n bytes (max 4000 SQL / 32767 PL/SQL)
-- CHAR(n)       : fixed-length, padded with spaces
-- NVARCHAR2(n)  : Unicode variable-length
-- NCHAR(n)      : Unicode fixed-length
-- CLOB          : character large object
-- NCLOB         : Unicode character large object

-- Date/time types
-- DATE          : stores date and time to the second
-- TIMESTAMP     : sub-second precision
-- TIMESTAMP WITH TIME ZONE
-- TIMESTAMP WITH LOCAL TIME ZONE
-- INTERVAL YEAR TO MONTH
-- INTERVAL DAY TO SECOND

-- Binary types
-- RAW(n)        : variable-length raw binary, up to n bytes
-- BLOB          : binary large object

SELECT 'Type reference loaded' FROM dual;

2. Validate DDL in Dev/Test First

-- Always run CREATE TABLE in a test schema first
CREATE TABLE test_schema.validate_types (
  col1 NUMBER(10,2),
  col2 VARCHAR2(100),
  col3 DATE,
  col4 TIMESTAMP,
  col5 CLOB
);

-- Drop the test table after validation
DROP TABLE test_schema.validate_types PURGE;

3. Wrap DDL in Exception Handlers During Migration Scripts

BEGIN
  EXECUTE IMMEDIATE '
    CREATE TABLE new_table (
      id     NUMBER GENERATED ALWAYS AS IDENTITY,
      name   VARCHAR2(200),
      status CHAR(1)
    )
  ';
  DBMS_OUTPUT.PUT_LINE('Table created successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('DDL failed: ' || SQLERRM);
    RAISE;
END;
/

4. Keep a Cross-Platform Type Mapping Reference

• TINYINT / SMALLINT → NUMBER(5,0)
• INT / INTEGER / BIGINT → NUMBER(10,0) / NUMBER(19,0)
• BOOLEAN / BIT → NUMBER(1,0) with CHECK constraint (pre-23ai)
• TEXT / NTEXT → CLOB / NCLOB
• DATETIME / TIMESTAMP → DATE or TIMESTAMP
• UNIQUEIDENTIFIER → RAW(16)
• AUTO_INCREMENT / IDENTITY → GENERATED ALWAYS AS IDENTITY

Related Errors

• ORA-00900 - Invalid SQL statement
• ORA-00907 - Missing right parenthesis
• ORA-00955 - Name already used by existing object
• ORA-02267 - Column type incompatible with referenced column

Emergency Response

Quick Fixes

1. Identify the offending column type immediately

   -- Check the exact error position using DBMS_SQL
   DECLARE
     v_c INTEGER := DBMS_SQL.OPEN_CURSOR;
   BEGIN
     DBMS_SQL.PARSE(v_c,
       'CREATE TABLE t (id BIGINT)',  -- replace with your DDL
       DBMS_SQL.NATIVE);
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLERRM);
       DBMS_SQL.CLOSE_CURSOR(v_c);
   END;
   /

2. Check if a similar table already exists with valid types

   SELECT column_name, data_type, data_length
   FROM all_tab_columns
   WHERE table_name = UPPER('&your_table')
   ORDER BY column_id;

3. Review migration script for vendor-specific types

   -- Search for common non-Oracle types in your DDL file
   -- grep -iE "(TINYINT|BIGINT|NVARCHAR\(MAX\)|DATETIME2|BOOLEAN|TEXT|BIT)" migration.sql
   SELECT 'Check migration script for non-Oracle type names' FROM dual;

Post-Resolution Cleanup

-- Verify new table structure is correct after fix
SELECT
  column_name,
  data_type,
  data_length,
  data_precision,
  data_scale,
  nullable
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE'
ORDER BY column_id;

-- Confirm table is accessible
SELECT COUNT(*) FROM your_table;