Solving ORA-00907: Missing right parenthesis

ORA-00907: Missing right parenthesis is a common Oracle Database error. It means Oracle expected a closing parenthesis ) in your SQL statement but didn’t find it where it was required.

What Triggers ORA-00907

1. Unbalanced Parentheses
   • You opened a ( but forgot to close it.
   • Example: SELECT first_name, last_name FROM employees WHERE (salary > 50000; ❌ Missing ) after 50000.
2. Wrong Syntax in Column Definitions or Functions
   • Functions, constraints, or column definitions have mismatched parentheses.
   • Example: CREATE TABLE test_table ( id NUMBER(10, name VARCHAR2(50) ); ❌ Forgot the closing parenthesis for NUMBER(10).
3. Invalid Use of Parentheses in Expressions
   • Extra or misplaced parentheses where Oracle doesn’t expect them.
   • Example: SELECT ((salary + bonus) FROM payroll; ❌ One extra ( without a matching ).
4. Errors Inside Subqueries or Functions
   • If you write a subquery or call a function but leave it incomplete.
   • Example: SELECT employee_id FROM employees WHERE department_id IN (SELECT department_id FROM departments; ❌ Missing closing ) for IN.

How to Solve It

1. Count and Match Parentheses
   • Every ( must have a matching ) in the right place.
   • Use a text editor or IDE that highlights matching parentheses.
2. Check Complex Statements Step by Step
   • Break long queries into smaller parts to find the error.
   • Example: Validate subqueries separately.
3. Verify Function Syntax
   • Make sure functions or data types use the correct format:
     • NUMBER(10) instead of ❌ NUMBER(10,.
     • TO_CHAR(sysdate, 'YYYY-MM-DD').
4. Use SQL Developer or Tools with Formatting
   • Formatting tools can re-indent your SQL and make errors easier to spot.

Real-Life Examples

Correct Table Creation

CREATE TABLE employees (
    emp_id NUMBER(10),
    first_name VARCHAR2(50),
    hire_date DATE
);

Incorrect Table Creation

CREATE TABLE employees (
    emp_id NUMBER(10,
    first_name VARCHAR2(50),
    hire_date DATE
);
-- ORA-00907: Missing right parenthesis

Correct Query with Subquery

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

Incorrect Query

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700;
-- ORA-00907: Missing right parenthesis

Correct Function Usage

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD') AS today FROM dual;

Incorrect Function Usage

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD' AS today FROM dual;
-- ORA-00907: Missing right parenthesis

Quick Checklist to Fix ORA-00907

• Count your parentheses—open and close must match.
• Validate function arguments and data types.
• Check subqueries and constraints for completeness.
• Format your SQL for better readability.
• Test smaller fragments of your SQL to locate the problem.

In Simple Terms: Oracle is very strict about parentheses. If you forget to close one, or put it in the wrong spot, you’ll see ORA-00907. By carefully checking and balancing them, or using a good SQL editor, you can quickly resolve this error.