// Oracle error reference

ORA-00907: missing right parenthesis

Oracle hit something it didn't expect inside parentheses. Often the parentheses are balanced and the real problem is a syntax error inside them.

Error code
ORA-00907
Category
Syntax and SQL statements
Error message
missing right parenthesis

What it means

ORA-00907 means that while parsing a parenthesized part, such as a column list, a function call or a subquery, Oracle met a token where it expected ). The name is misleading: the real cause is often invalid syntax inside the parentheses, which makes Oracle think the group should have ended.

Common causes

  • A genuinely missing ).
  • ORDER BY inside a subquery used in an IN list or as a scalar subquery.
  • A datatype without its length, or with options from another database, inside CREATE TABLE: for example VARCHAR2 without a size, or AUTO_INCREMENT.
  • A missing comma between column definitions or constraints.

How to fix it

  1. Check the contents of the parentheses near the reported position, not just the count.

  2. Remove ORDER BY from subqueries where it isn't allowed.

    SELECT * FROM hr.employees
    WHERE department_id IN (SELECT department_id FROM hr.departments WHERE location_id = 1700);
  3. Use Oracle syntax in table definitions, for example an identity column instead of AUTO_INCREMENT.

    CREATE TABLE notes (
        id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        body VARCHAR2(4000)
    );

Related errors

More syntax and sql statements errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.