DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00906 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00906: Missing Left Parenthesis — Causes, Fixes & Prevention

ORA-00906 is a syntax error thrown by Oracle when the parser expects a left parenthesis ( at a specific point in a SQL statement but finds something else — or nothing at all. This error can appear in DDL statements like CREATE TABLE, DML statements like INSERT or SELECT, and even in function calls or subqueries. It is one of the most common Oracle errors and is almost always straightforward to fix once you identify the exact location.

Top 3 Causes

1. Missing Parentheses in CREATE TABLE or CREATE INDEX

When defining a table, Oracle requires that the column list be wrapped in parentheses immediately after the table name. Forgetting this — especially when migrating scripts from MySQL or SQL Server — instantly triggers ORA-00906.

-- WRONG: No parentheses around column definitions
CREATE TABLE employees
    emp_id   NUMBER(10),
    emp_name VARCHAR2(100)
;

-- CORRECT: Wrap column list in parentheses
CREATE TABLE employees
(
    emp_id   NUMBER(10)    NOT NULL,
    emp_name VARCHAR2(100) NOT NULL,
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

-- WRONG: Missing parentheses in CREATE INDEX
CREATE INDEX idx_name ON employees emp_name;

-- CORRECT
CREATE INDEX idx_name ON employees (emp_name);
Enter fullscreen mode Exit fullscreen mode

2. Missing Parentheses in Function Calls or IN Clauses

Oracle built-in functions such as NVL, TO_DATE, and SUBSTR require parentheses immediately after the function name. Similarly, the IN operator must be followed by a parenthesized list or subquery.

-- WRONG: NVL called without parentheses
SELECT NVL emp_name, 'N/A' FROM employees;

-- CORRECT
SELECT NVL(emp_name, 'N/A') AS emp_name
FROM employees;

-- WRONG: IN clause without parentheses
SELECT * FROM employees
WHERE dept_id IN 10, 20, 30;

-- CORRECT
SELECT * FROM employees
WHERE dept_id IN (10, 20, 30);
Enter fullscreen mode Exit fullscreen mode

3. Missing Parentheses Around Subqueries or CTE Definitions

Inline views (derived tables) and subqueries used inside IN, FROM, or WHERE clauses must be enclosed in parentheses. The same applies to CTE blocks defined with the WITH clause.

-- WRONG: Inline view without parentheses
SELECT a.dept_id, a.avg_sal
FROM SELECT dept_id, AVG(salary) avg_sal
     FROM employees GROUP BY dept_id a;

-- CORRECT
SELECT a.dept_id, a.avg_sal
FROM (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
) a;

-- WRONG: WITH clause subquery missing parentheses
WITH summary AS
    SELECT dept_id, COUNT(*) cnt FROM employees GROUP BY dept_id
SELECT * FROM summary;

-- CORRECT
WITH summary AS (
    SELECT dept_id, COUNT(*) AS cnt
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM summary;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

When you hit ORA-00906, run through this checklist:

  1. Check your CREATE TABLE / INDEX statements — is the column or key list wrapped in ()?
  2. Check every function call — does each function have ( right after its name?
  3. Check IN clauses — is the value list or subquery enclosed in ()?
  4. Check inline views and CTEs — is the subquery body fully wrapped in ()?
  5. Count parentheses pairs — use your IDE's bracket-matching feature to spot mismatches quickly.

Prevention Tips

Use an IDE with real-time syntax checking.
Tools like Oracle SQL Developer, DBeaver, or Toad highlight unmatched or missing parentheses as you type. Always validate SQL in your IDE before deploying to production. Integrating a linter like SQLFluff into your CI/CD pipeline adds an extra layer of protection for the whole team.

Maintain a SQL migration checklist when switching databases.
If you're porting scripts from MySQL, PostgreSQL, or SQL Server to Oracle, keep a checklist of Oracle-specific syntax requirements — especially mandatory parentheses in CREATE TABLE, CREATE INDEX, function calls, and subqueries. Pair programming or peer code review before any DDL deployment can catch ORA-00906 and similar syntax errors before they ever reach production.

Related Errors

  • ORA-00907 — Missing right parenthesis; the closing ) counterpart to ORA-00906.
  • ORA-00936 — Missing expression; often appears alongside ORA-00906 when the parser loses track of an expression due to a missing bracket.
  • ORA-00917 — Missing comma; can co-occur when both a comma and a parenthesis are omitted in a column or value list.

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