DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00901 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00901: invalid CREATE command — Causes, Fixes & Prevention

ORA-00901 is thrown by Oracle Database when it encounters a CREATE statement that it cannot parse due to a syntax violation or an unrecognized object type keyword. In simple terms, Oracle's SQL parser expected a valid DDL construct after the CREATE keyword but received something it could not understand. This error is especially common during database migrations, cross-platform SQL porting, and manual DDL scripting.

Top 3 Causes

1. Typo or Invalid Object Type After CREATE

The most frequent cause is a simple spelling mistake in the object type keyword (e.g., TABEL instead of TABLE) or using an object type that Oracle does not support.

-- WRONG: typo in object type
CREATE TABEL employees (
    emp_id   NUMBER(10),
    emp_name VARCHAR2(100)
);
-- ORA-00901: invalid CREATE command

-- CORRECT
CREATE TABLE employees (
    emp_id   NUMBER(10)    NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (emp_id)
);
Enter fullscreen mode Exit fullscreen mode

2. Using Non-Oracle SQL Syntax (MySQL / PostgreSQL)

Developers migrating from MySQL or PostgreSQL often paste DDL scripts directly into Oracle without converting the syntax. Keywords like ENGINE=InnoDB, AUTO_INCREMENT, or SERIAL are not valid in Oracle and will trigger ORA-00901.

-- WRONG: MySQL syntax in Oracle
CREATE TABLE orders (
    order_id   INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME DEFAULT NOW()
) ENGINE=InnoDB;
-- ORA-00901: invalid CREATE command

-- CORRECT: Oracle equivalent
CREATE SEQUENCE seq_orders
    START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE TABLE orders (
    order_id   NUMBER(10) DEFAULT seq_orders.NEXTVAL NOT NULL,
    order_date TIMESTAMP  DEFAULT SYSTIMESTAMP,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
);
Enter fullscreen mode Exit fullscreen mode

3. Unsupported Clauses: OR REPLACE on TABLE, IF NOT EXISTS (pre-23c)

Oracle supports CREATE OR REPLACE only for certain object types such as VIEW, PROCEDURE, FUNCTION, and TRIGGER — not for TABLE. Additionally, CREATE TABLE IF NOT EXISTS is only supported from Oracle 23c onward.

-- WRONG: OR REPLACE is not valid for TABLE
CREATE OR REPLACE TABLE departments (
    dept_id   NUMBER(5),
    dept_name VARCHAR2(50)
);
-- ORA-00901: invalid CREATE command

-- CORRECT: Check existence first, then create
BEGIN
    EXECUTE IMMEDIATE '
        CREATE TABLE departments (
            dept_id   NUMBER(5)    NOT NULL,
            dept_name VARCHAR2(50) NOT NULL,
            CONSTRAINT pk_departments PRIMARY KEY (dept_id)
        )
    ';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -955 THEN
            DBMS_OUTPUT.PUT_LINE('Table already exists. Skipping.');
        ELSE
            RAISE;
        END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode 
-- Oracle 23c and above: IF NOT EXISTS is now supported
CREATE TABLE IF NOT EXISTS employees (
    emp_id   NUMBER       NOT NULL,
    emp_name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Check your spelling — Verify the object type keyword immediately following CREATE.
  2. Verify Oracle version — Run SELECT * FROM V$VERSION; to confirm your version before using newer syntax.
  3. Convert foreign SQL — Never paste MySQL/PostgreSQL DDL directly into Oracle; always convert to Oracle-native syntax.
  4. Use OR REPLACE only where supported — Valid for VIEW, PROCEDURE, FUNCTION, TRIGGER, TYPE, PACKAGE, but not TABLE.
  5. Handle IF NOT EXISTS via PL/SQL — On versions prior to 23c, wrap your CREATE TABLE in a PL/SQL block and catch ORA-00955.

Prevention Tips

Use an Oracle-aware IDE with syntax checking.
Tools like Oracle SQL Developer or Toad for Oracle provide real-time syntax validation that catches ORA-00901 before execution. Always run a syntax check before deploying DDL scripts to production.

Always test DDL in a development or staging environment first.
Before applying any DDL to production, validate the script on an environment that mirrors the production Oracle version. Include a version verification block at the top of every DDL script to prevent execution on incompatible versions.

-- Add this guard at the top of every DDL deployment script
SET SERVEROUTPUT ON;
DECLARE
    v_ver VARCHAR2(100);
BEGIN
    SELECT version INTO v_ver FROM v$instance;
    DBMS_OUTPUT.PUT_LINE('Oracle Version: ' || v_ver);
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Oracle Errors

Error Code Description
ORA-00900 Invalid SQL statement (broader syntax failure)
ORA-00902 Invalid datatype used in DDL
ORA-00903 Invalid table name
ORA-00955 Name already used by an existing object
ORA-01031 Insufficient privileges to execute CREATE

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