DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00922 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00922: Missing or Invalid Option — Causes and Fixes

ORA-00922 is a syntax error thrown by the Oracle database when a DDL or SQL statement contains an unrecognized or incorrectly placed option or keyword. It most commonly appears with CREATE, ALTER, or DROP statements when the parser cannot interpret a clause or option. Since it is a parse-time error, the statement will not execute at all until the syntax issue is corrected.

Top 3 Causes

1. Invalid Data Type or Misplaced Column Constraint

Using unsupported data types (e.g., VARCHAR instead of Oracle's VARCHAR2) or placing keywords like DEFAULT and NOT NULL in the wrong order is one of the most frequent triggers.

-- ❌ Incorrect: Wrong data type and constraint order
CREATE TABLE employees (
    emp_id   NUMBER NOT NULL PRIMARY,
    emp_name VARCHAR(100),
    hire_dt  DATE NOT NULL DEFAULT SYSDATE
);

-- ✅ Correct
CREATE TABLE employees (
    emp_id   NUMBER        NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    hire_dt  DATE          DEFAULT SYSDATE NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (emp_id)
);
Enter fullscreen mode Exit fullscreen mode

In Oracle, DEFAULT must always come before NOT NULL.

2. Using Reserved Words as Object Names

Using Oracle reserved words such as DATE, NUMBER, TABLE, or COMMENT as column or table names confuses the parser, which tries to interpret them as SQL keywords.

-- ❌ Incorrect: Reserved words used as column names
CREATE TABLE orders (
    number  NUMBER(10),
    date    DATE,
    comment VARCHAR2(255)
);

-- ✅ Correct: Use descriptive, non-reserved names
CREATE TABLE orders (
    order_no      NUMBER(10),
    order_date    DATE,
    order_comment VARCHAR2(255)
);
Enter fullscreen mode Exit fullscreen mode

You can check Oracle reserved words with:

SELECT keyword FROM v$reserved_words
WHERE reserved = 'Y'
ORDER BY keyword;
Enter fullscreen mode Exit fullscreen mode

3. Incorrect STORAGE or Physical Attribute Syntax

Omitting parentheses around the STORAGE clause or specifying attributes in the wrong order will trigger ORA-00922.

-- ❌ Incorrect: Missing parentheses in STORAGE clause
CREATE TABLE sales (
    sale_id  NUMBER(10),
    amount   NUMBER(15,2)
)
STORAGE INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED
TABLESPACE users;

-- ✅ Correct: Proper STORAGE syntax with parentheses
CREATE TABLE sales (
    sale_id  NUMBER(10),
    amount   NUMBER(15,2)
)
TABLESPACE users
STORAGE (
    INITIAL     1M
    NEXT        1M
    MAXEXTENTS  UNLIMITED
    PCTINCREASE 0
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • ✅ Replace VARCHAR with VARCHAR2
  • ✅ Place DEFAULT before NOT NULL in column definitions
  • ✅ Wrap STORAGE options in parentheses ( )
  • ✅ Avoid Oracle reserved words in object names
  • ✅ Use DBMS_METADATA.GET_DDL to inspect existing valid DDL as a reference 
-- Retrieve existing DDL as a reference template
SELECT DBMS_METADATA.GET_DDL('TABLE', 'YOUR_TABLE_NAME', USER)
FROM DUAL;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Adopt a strict naming convention — Prefix table names with T_ and use domain-specific abbreviations for columns to avoid accidental use of reserved words.

Use Oracle SQL Developer or SQLcl — Both tools provide real-time syntax highlighting and error detection, catching ORA-00922 issues before you even run the statement. Always validate migrated scripts from other databases (MySQL, PostgreSQL) against Oracle syntax before execution.

Related Errors

Error Code Description
ORA-00900 Invalid SQL statement
ORA-00904 Invalid identifier
ORA-00907 Missing right parenthesis
ORA-00911 Invalid character

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