DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00907 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00907: Missing Right Parenthesis — Causes, Fixes & Prevention

ORA-00907 is one of Oracle's most common SQL parsing errors, triggered when the Oracle engine encounters an opening parenthesis ( without a matching closing parenthesis ), or when a parenthesis appears in a syntactically invalid position. This error can stem from simple typos to complex subquery structural mistakes, and it can be especially tricky to diagnose in long SQL statements.

Top 3 Causes

1. Missing Parenthesis in DDL Statements

Forgetting to close parentheses in CREATE TABLE column definitions or constraint declarations is the most frequent cause.

Incorrect:

-- Missing closing parenthesis in VARCHAR2 and CHECK constraint
CREATE TABLE products (
    product_id   NUMBER,
    product_name VARCHAR2(200,       -- missing closing )
    price        NUMBER(10, 2),
    CONSTRAINT chk_price CHECK price > 0  -- missing parentheses around condition
);
Enter fullscreen mode Exit fullscreen mode

Correct:

CREATE TABLE products (
    product_id   NUMBER,
    product_name VARCHAR2(200),
    price        NUMBER(10, 2),
    CONSTRAINT chk_price CHECK (price > 0)
);
Enter fullscreen mode Exit fullscreen mode

2. Unclosed Subquery or Inline View

Inline views, WHERE clause subqueries, and CTEs all require carefully matched parentheses. Dropping even one closing ) breaks the entire query.

Incorrect:

-- Missing closing parenthesis for the inline view
SELECT a.emp_name, b.avg_sal
FROM employees a,
     (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees
      GROUP BY dept_id    -- missing ) and alias here
WHERE a.dept_id = b.dept_id;
Enter fullscreen mode Exit fullscreen mode

Correct:

SELECT a.emp_name, b.avg_sal
FROM employees a,
     (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees
      GROUP BY dept_id) b
WHERE a.dept_id = b.dept_id;
Enter fullscreen mode Exit fullscreen mode

3. Syntax Errors in CASE, IN, or Nested Functions

When CASE expressions, IN lists with subqueries, or deeply nested function calls are involved, a misplaced or missing parenthesis raises ORA-00907.

Incorrect:

-- Missing closing parenthesis on the IN subquery
SELECT emp_name, salary
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location_id = 100
-- closing ) is missing
AND salary > 3000;
Enter fullscreen mode Exit fullscreen mode

Correct:

SELECT emp_name, salary
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location_id = 100
)
AND salary > 3000;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist 

-- Step 1: Count opening vs closing parentheses (they must match)
SELECT
    LENGTH(:your_sql) - LENGTH(REPLACE(:your_sql, '(', '')) AS open_parens,
    LENGTH(:your_sql) - LENGTH(REPLACE(:your_sql, ')', '')) AS close_parens
FROM DUAL;

-- Step 2: Test subqueries independently before combining
SELECT dept_id
FROM departments
WHERE location_id = 100;  -- validate this first, then wrap it

-- Step 3: Use proper indentation to visually track nesting
SELECT *
FROM (                          -- level 1 open
    SELECT dept_id, COUNT(*) cnt
    FROM (                      -- level 2 open
        SELECT dept_id
        FROM employees
        WHERE salary > 5000
    )                           -- level 2 close
    GROUP BY dept_id
)                               -- level 1 close
WHERE cnt > 5;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Use an IDE with bracket matching. Tools like SQL Developer, Toad, or DBeaver highlight matching parentheses in real time, making it easy to spot mismatches before execution.

Write parentheses in pairs immediately. When you type (, immediately type ) and then fill in the content between them. This simple habit virtually eliminates missing-parenthesis errors in your daily SQL writing.

Related Oracle Errors

Error Code Description
ORA-00906 Missing left parenthesis
ORA-00933 SQL command not properly ended
ORA-00936 Missing expression
ORA-00902 Invalid datatype

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

Top comments (0)