ORA-00906: Missing Left Parenthesis - Fix SQL Syntax

ORA-00906: Missing Left Parenthesis

Error Overview

Error Text: ORA-00906: missing left parenthesis

The ORA-00906 error is a parse-time error raised when Oracle expects an opening parenthesis ( in a specific syntactic position but does not find one. It is one of the most common SQL syntax errors and appears in CREATE TABLE, CREATE INDEX, subqueries, function calls, and IN-list expressions.

Common Causes

1. CREATE TABLE Missing Column List Parentheses

• Forgetting the opening ( before the column definition list
• Copying DDL from a source where the parenthesis was truncated

2. Function Calls Without Parentheses

• Calling built-in or user-defined functions without argument list syntax
• Using a function name as a column alias without an AS keyword, confusing the parser

3. Subquery Missing Opening Parenthesis

• Inline views in FROM clause not wrapped in parentheses
• Correlated subqueries in WHERE/HAVING missing the (

4. IN Operator Without Parentheses

• WHERE col IN val1, val2 instead of WHERE col IN (val1, val2)
• Subquery after IN not enclosed

5. Constraint Definitions in CREATE TABLE

• CONSTRAINT pk PRIMARY KEY emp_id instead of CONSTRAINT pk PRIMARY KEY (emp_id)

Diagnostic Queries

Find Recent Statements with Parse Errors

-- Identify SQL statements that failed to parse
SELECT
  sql_id,
  sql_text,
  parse_calls,
  executions,
  last_active_time
FROM v$sql
WHERE parse_calls > 0
  AND executions = 0
  AND last_active_time > SYSDATE - 1/24
ORDER BY last_active_time DESC
FETCH FIRST 20 ROWS ONLY;

Inspect Recent Audit Failures

-- Check unified audit trail for ORA-00906 occurrences
SELECT
  event_timestamp,
  db_user_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 906
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY event_timestamp DESC;

Check Alert Log for Repeated Errors

-- Look for ORA-00906 in diagnostic alert extension view
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00906%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY originating_timestamp DESC;

Validate DDL Programmatically

-- Use DBMS_SQL to parse and catch the error with context
DECLARE
  v_cursor INTEGER;
  v_ddl    VARCHAR2(4000) := 'CREATE TABLE t id NUMBER, name VARCHAR2(50))';
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  BEGIN
    DBMS_SQL.PARSE(v_cursor, v_ddl, DBMS_SQL.NATIVE);
    DBMS_OUTPUT.PUT_LINE('DDL is valid');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  END;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

Step-by-Step Resolution

1. Fix CREATE TABLE Missing Column List Parentheses

-- WRONG: Missing opening parenthesis before column definitions
-- CREATE TABLE employees
--   emp_id   NUMBER PRIMARY KEY,
--   emp_name VARCHAR2(100)
-- );

-- CORRECT
CREATE TABLE employees (
  emp_id   NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100)
);

2. Fix Constraint Definitions

-- WRONG: PRIMARY KEY constraint without column list parentheses
-- CREATE TABLE orders (
--   order_id NUMBER,
--   CONSTRAINT pk_orders PRIMARY KEY order_id
-- );

-- CORRECT
CREATE TABLE orders (
  order_id  NUMBER,
  CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

-- WRONG: Composite unique constraint without parentheses
-- CREATE TABLE order_items (
--   order_id    NUMBER,
--   item_id     NUMBER,
--   CONSTRAINT uq_items UNIQUE order_id, item_id
-- );

-- CORRECT
CREATE TABLE order_items (
  order_id    NUMBER,
  item_id     NUMBER,
  CONSTRAINT uq_items UNIQUE (order_id, item_id)
);

3. Fix IN Operator Syntax

-- WRONG: Values not enclosed in parentheses
-- SELECT * FROM employees
-- WHERE department_id IN 10, 20, 30;

-- CORRECT
SELECT * FROM employees
WHERE department_id IN (10, 20, 30);

-- WRONG: Subquery after IN not enclosed
-- SELECT * FROM employees
-- WHERE department_id IN SELECT department_id FROM departments WHERE location_id = 1700;

-- CORRECT
SELECT * FROM employees
WHERE department_id IN (
  SELECT department_id FROM departments WHERE location_id = 1700
);

4. Fix Inline Views in FROM Clause

-- WRONG: Inline view not wrapped in parentheses
-- SELECT e.dept_name, e.avg_sal
-- FROM
--   SELECT d.department_name AS dept_name, AVG(e.salary) AS avg_sal
--   FROM employees e JOIN departments d ON e.department_id = d.department_id
--   GROUP BY d.department_name
-- ORDER BY avg_sal DESC;

-- CORRECT
SELECT e.dept_name, e.avg_sal
FROM (
  SELECT d.department_name AS dept_name, AVG(e.salary) AS avg_sal
  FROM employees e JOIN departments d ON e.department_id = d.department_id
  GROUP BY d.department_name
) e
ORDER BY avg_sal DESC;

5. Fix CREATE INDEX Syntax

-- WRONG: Missing column list parentheses
-- CREATE INDEX idx_emp_name ON employees emp_name;

-- CORRECT
CREATE INDEX idx_emp_name ON employees (emp_name);

-- WRONG: Composite index without parentheses
-- CREATE INDEX idx_emp_dept ON employees last_name, department_id;

-- CORRECT
CREATE INDEX idx_emp_dept ON employees (last_name, department_id);

6. Fix Function Call Syntax

-- WRONG: COUNT without parentheses
-- SELECT COUNT FROM employees;

-- CORRECT
SELECT COUNT(*) FROM employees;

-- WRONG: TO_DATE without argument parentheses
-- SELECT TO_DATE '2024-01-01', 'YYYY-MM-DD' FROM dual;

-- CORRECT
SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD') FROM dual;

7. Fix PARTITION BY Clause in Window Functions

-- WRONG: PARTITION BY without enclosing OVER parentheses
-- SELECT employee_id,
--        salary,
--        SUM(salary) OVER PARTITION BY department_id AS dept_total
-- FROM employees;

-- CORRECT
SELECT
  employee_id,
  salary,
  SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

Prevention Strategies

1. Use a SQL Formatter/Linter Before Execution

• Format SQL with consistent indentation so missing parentheses are visually obvious
• Use SQL Developer’s formatter or third-party tools like sqlfluff
• Enable syntax checking in your IDE (SQL Developer, DataGrip, Toad)

2. Template-Driven DDL Scripts

-- Standard CREATE TABLE template — fill in blanks
CREATE TABLE schema_name.table_name (
  column1    datatype1     [NOT NULL],
  column2    datatype2     [NOT NULL],
  column3    datatype3,
  CONSTRAINT pk_table_name PRIMARY KEY (column1),
  CONSTRAINT uq_table_name UNIQUE     (column2),
  CONSTRAINT fk_table_name FOREIGN KEY (column3)
    REFERENCES parent_table (parent_col)
);

3. Validate DDL with DBMS_SQL Before Deployment

-- Pre-deployment DDL validation procedure
CREATE OR REPLACE PROCEDURE validate_ddl (p_ddl IN VARCHAR2) AS
  v_cursor INTEGER;
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, p_ddl, DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
  DBMS_OUTPUT.PUT_LINE('DDL valid: ' || SUBSTR(p_ddl, 1, 80));
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(v_cursor) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    END IF;
    DBMS_OUTPUT.PUT_LINE('DDL invalid: ' || SQLERRM);
    RAISE;
END;
/

4. Brace-Matching in Editors

• Configure your editor to highlight matching parentheses
• Use % bracket-matching in Vim, or bracket highlighting in VS Code
• For large DDL scripts, add inline comments after closing parentheses:

CREATE TABLE sales (
  sale_id        NUMBER         NOT NULL,
  customer_id    NUMBER         NOT NULL,
  sale_date      DATE           NOT NULL,
  amount         NUMBER(12,2)   NOT NULL,
  CONSTRAINT pk_sales PRIMARY KEY (sale_id),       -- end of PK
  CONSTRAINT fk_sales_cust FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
);  -- end of CREATE TABLE sales

Related Errors

• ORA-00907 - Missing right parenthesis
• ORA-00900 - Invalid SQL statement
• ORA-00917 - Missing comma
• ORA-00933 - SQL command not properly ended

Emergency Response

Quick Fixes

1. Count parentheses to find imbalance

   -- In SQL*Plus or a script file, count opening vs closing parens
   -- A quick way to check: paste into any text editor with brace matching
   SELECT
     LENGTH('your_sql_here') - LENGTH(REPLACE('your_sql_here', '(', '')) AS open_count,
     LENGTH('your_sql_here') - LENGTH(REPLACE('your_sql_here', ')', '')) AS close_count
   FROM dual;

2. Simplify the statement and add complexity back

   -- Start with the simplest version of the statement
   -- Add clauses back one at a time until the error reappears
   CREATE TABLE t (id NUMBER);  -- minimal version
   -- Then add columns and constraints incrementally

3. Use EXPLAIN PLAN to parse without executing DML

   EXPLAIN PLAN FOR
   SELECT * FROM employees WHERE department_id IN (10, 20);
   
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Post-Resolution Cleanup

-- Verify the object was created correctly after fix
SELECT object_name, object_type, status
FROM user_objects
WHERE object_name = 'YOUR_TABLE'
  AND object_type = 'TABLE';

-- Confirm column definitions
SELECT column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE'
ORDER BY column_id;