Oracle Error ORA-00907

Error code

ORA-00907

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (same wording)

Category

SQL Syntax & Query Structure

Last reviewed

24 October 2026

What it means

ORA-00907 means Oracle reached a point inside parentheses where it expected the closing ")" and found something else: either a parenthesis really is missing, or the parentheses contain a word or clause Oracle does not accept there.

Oracle's reference gives two causes: a left parenthesis entered without a closing right parenthesis, or unrecognised information inside the parentheses. The second is the one that confuses people. When the parser meets a keyword it does not expect in a column list, a function call or a subquery, it assumes the parenthesised part should have ended there and reports a missing right parenthesis, even if every bracket is balanced.

So read the error as "something is wrong at or just before this position inside the brackets". Typical culprits are syntax from other databases in CREATE TABLE (AUTO_INCREMENT, a size on a type that takes none, a misplaced constraint clause), an ORDER BY inside an IN subquery, and a missing comma between two column definitions. SQL*Plus and SQLcl print an asterisk under the token where parsing stopped, which is usually the word to look at.

Common causes

• A closing parenthesis really is missing, often in nested function calls or a long IN list.
• CREATE TABLE uses syntax from another database, such as AUTO_INCREMENT, IDENTITY(1,1) or UNSIGNED, inside the column list.
• A subquery used with IN, EXISTS or a comparison contains an ORDER BY clause.
• A comma is missing between two column or constraint definitions, so the next definition is read as part of the previous one.
• A constraint clause is written in the wrong place or form, for example an inline constraint with a column list.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT column_name, data_type, data_length, data_precision, nullable, identity_column
FROM user_tab_columns
WHERE table_name = 'CUSTOMERS'
ORDER BY column_id;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE TABLE shop.customers (
    id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email VARCHAR2(200) NOT NULL
);

SELECT c.id, c.email
FROM shop.customers c
WHERE c.id IN (SELECT o.customer_id FROM shop.orders o WHERE o.order_date >= DATE '2026-01-01')
ORDER BY c.email;

SELECT order_id, ROUND(SUM(quantity * unit_price), 2) AS total
FROM shop.order_lines
GROUP BY order_id;

Examples

AUTO_INCREMENT copied from a MySQL script

Not reproduced here: no Oracle Database is installed for this site. The message text comes from Oracle's error reference, whose cause includes unrecognised information inside the parentheses; Oracle's CREATE TABLE syntax has no AUTO_INCREMENT keyword. The exact position Oracle reports is illustrative.

CREATE TABLE customers (
    id NUMBER PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR2(200)
);

ORA-00907: missing right parenthesis

ORDER BY inside an IN subquery

Not reproduced here: no Oracle Database is installed for this site. The message text comes from Oracle's error reference. The brackets are balanced; the parser stops at ORDER BY because it does not accept it in this subquery, and reports the parenthesis it expected instead.

SELECT id, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders ORDER BY order_date);

ORA-00907: missing right parenthesis

A missing comma between column definitions

Not reproduced here: no Oracle Database is installed for this site. The message text comes from Oracle's error reference; without the comma after PRIMARY KEY, customer_id is read as unexpected information in the column list. A different statement shape can make Oracle report another syntax error for the same mistake.

CREATE TABLE orders (
    id          NUMBER PRIMARY KEY
    customer_id NUMBER NOT NULL,
    order_date  DATE
);

ORA-00907: missing right parenthesis

How to prevent it

When moving DDL from MySQL, SQL Server or PostgreSQL, convert it to Oracle syntax rather than running it as it is: identity columns instead of AUTO_INCREMENT or IDENTITY(1,1), VARCHAR2 and NUMBER types, and no UNSIGNED. Keep one column or constraint per line so a missing comma is visible.

Format long queries with one level of nesting per indent, and sort only in the outermost query. A formatter or an editor that highlights matching brackets catches most of these errors before they reach the database.

Common questions

Learn more on CodeWithSQL

• SQL syntax guide
• SQL subqueries vs joins

Official documentation

• ORA-00907 (Oracle Database Error Messages)
• CREATE TABLE (Oracle SQL Language Reference 19c)
• Using subqueries (Oracle SQL Language Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.