What causes ORA-00907?

Unbalanced parentheses in a complex query with nested subqueries. Inline view or CTE syntax error. Function call missing the closing parenthesis. Dynamic SQL built by string concatenation with an off-by-one error. Non-Oracle syntax that Oracle interprets differently

How do I fix ORA-00907?

Step 1: Count opening and closing parentheses in the SQL statement.. Step 2: Use a SQL formatter or IDE to highlight matching parenthesis pairs.. Step 3: Isolate the innermost subquery and validate it first, then work outward.. Step 4: If the SQL is generated dynamically (e.g. via ADF expression language), log the resolved SQL before executing.. Step 5: Test the query in SQL Developer or SQL*Plus where the error position is shown.

Medium severitysql

Oracle Database Error:
ORA-00907

What does this error mean?

Oracle found an unmatched or missing closing parenthesis in a SQL statement, subquery, or function call.

Common causes

1Unbalanced parentheses in a complex query with nested subqueries
2Inline view or CTE syntax error
3Function call missing the closing parenthesis
4Dynamic SQL built by string concatenation with an off-by-one error
5Non-Oracle syntax that Oracle interprets differently

How to fix it

1Step 1: Count opening and closing parentheses in the SQL statement.
2Step 2: Use a SQL formatter or IDE to highlight matching parenthesis pairs.
3Step 3: Isolate the innermost subquery and validate it first, then work outward.
4Step 4: If the SQL is generated dynamically (e.g. via ADF expression language), log the resolved SQL before executing.
5Step 5: Test the query in SQL Developer or SQL*Plus where the error position is shown.

Example log output

ORA-00907: missing right parenthesis
SQL: SELECT * FROM (SELECT id, name FROM customers WHERE active = 1

Frequently asked questions

How do I quickly find the unmatched parenthesis?

Paste the SQL into SQL Developer — it highlights matching brackets when you place the cursor next to one. Alternatively, use a regex counter: `echo 'sql' | grep -o '(' | wc -l` vs `echo 'sql' | grep -o ')' | wc -l`.

Source · docs.oracle.com/error-help/db/ora-00907