Oracle ORA-00905 Error: Causes and Solutions Complete Guide

ORA-00905: Missing Keyword — Causes, Fixes, and Prevention

ORA-00905 is one of Oracle's most common SQL syntax errors, occurring when the Oracle parser encounters a position where a specific keyword is expected but not found. This error typically surfaces during DML (INSERT, UPDATE, DELETE) or DDL (CREATE, ALTER) statement execution. It is especially frequent when developers migrate SQL from other databases like MySQL or PostgreSQL to Oracle, where syntax rules differ.

---

Top 3 Causes and Fixes

1. Missing INTO in INSERT Statement

Oracle strictly requires the INTO keyword after INSERT. Unlike some databases that allow omitting it, Oracle will immediately throw ORA-00905 without it.

Incorrect:

-- ORA-00905: missing keyword
INSERT employees (employee_id, first_name, salary)
VALUES (201, 'Jane', 5000);

Correct:

-- Always include INTO
INSERT INTO employees (employee_id, first_name, salary)
VALUES (201, 'Jane', 5000);

---

2. Missing AS in CREATE TABLE AS SELECT (CTAS)

CTAS is widely used for table backups and data migrations. Forgetting the AS keyword before the SELECT clause will trigger ORA-00905.

Incorrect:

-- ORA-00905: missing keyword
CREATE TABLE employees_backup
SELECT * FROM employees WHERE department_id = 10;

Correct:

-- AS keyword is mandatory
CREATE TABLE employees_backup
AS
SELECT * FROM employees WHERE department_id = 10;

---

3. Unsupported JOIN Syntax in UPDATE or Missing SET Keyword

Oracle does not support the UPDATE ... JOIN syntax used in MySQL. Attempting to use it, or simply forgetting the SET keyword, will cause ORA-00905.

Incorrect (MySQL-style UPDATE JOIN):

-- ORA-00905: Oracle does not support this syntax
UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.1
WHERE d.department_name = 'SALES';

Correct (Oracle inline view approach):

-- Use inline view or subquery instead
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'SALES'
);

Incorrect (Missing SET):

-- ORA-00905: SET keyword missing
UPDATE employees
salary = 9000
WHERE employee_id = 100;

Correct:

UPDATE employees
SET salary = 9000
WHERE employee_id = 100;

---

Quick Fix Checklist

When you encounter ORA-00905, run through this quick checklist:

1. INSERT statements — Did you include INTO?
2. CTAS statements — Did you include AS before SELECT?
3. UPDATE statements — Did you include SET? Are you using Oracle-compatible JOIN syntax?
4. MERGE statements — Did you include THEN after WHEN MATCHED and WHEN NOT MATCHED?

-- Correct MERGE syntax reminder
MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.value = s.value      -- THEN is required
WHEN NOT MATCHED THEN
    INSERT (id, value) VALUES (s.id, s.value);  -- THEN is required

---

Prevention Tips

Use an IDE with real-time SQL validation.
Tools like Oracle SQL Developer, Toad, or DBeaver highlight syntax errors before execution, saving you from runtime failures in production environments.

Maintain a team SQL style guide for Oracle-specific syntax.
If your team works across multiple database platforms, document Oracle-specific rules (mandatory INTO, no UPDATE JOIN, MERGE THEN requirements, etc.) and include them in your onboarding process. Integrating a SQL linter like SQLFluff into your CI/CD pipeline can automatically catch these issues before deployment.

---

Related Oracle Errors

Error Code	Description
ORA-00900	Invalid SQL statement
ORA-00907	Missing right parenthesis
ORA-00933	SQL command not properly ended
ORA-00936	Missing expression

These errors all belong to the same family of SQL syntax errors and often appear together when working with complex or cross-platform SQL scripts.

---

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