ORA-00907: missing right parenthesis

The parser reached a point where it expected a closing parenthesis. Sometimes one really is missing, but often something inside the parentheses is not allowed there — such as ORDER BY inside an IN subquery or a wrong column definition in CREATE TABLE.

What it means

Oracle reads the statement left to right. When the text inside an opening parenthesis stops making sense to the parser, it reports that it expected the closing parenthesis at that point. That is why ORA-00907 often points at valid-looking SQL: the real problem is the first invalid token inside the brackets.

Common causes

An actually missing or extra parenthesis in a long expression.
ORDER BY inside a subquery used with IN ( … ).
An invalid data type or constraint clause in CREATE TABLE, such as VARCHAR2 without a length.
A missing comma between items in a list.

How to fix it

Count the parentheses; an editor with bracket matching makes this quick.
Remove ORDER BY from subqueries inside IN; order the outer query instead.
Check column definitions in CREATE TABLE: every VARCHAR2 needs a length, and constraints need the right syntax.
Shorten the statement step by step until the error disappears — the last removed part contains the problem.

Example

ORDER BY is not allowed inside IN:

-- ORA-00907
SELECT * FROM orders
 WHERE customer_id IN (SELECT id FROM customers ORDER BY name);

-- Works
SELECT * FROM orders
 WHERE customer_id IN (SELECT id FROM customers);

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00907. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.