ORA-00907: missing right parenthesis Solution

Did you get an ORA-00907: missing right parenthesis error? Learn what caused it and how to resolve it in this article.

ORA-00907 Cause

When working with Oracle SQL, all left parenthesis (the "(" character) must be paired with a right parenthesis character (the ")" character).

If there are more left parentheses than right parentheses, then you'll get this error.

It can also be caused by syntax errors in your CREATE TABLE statement.

ORA-00907 Solution

There are a few ways to resolve this error.

Solution 1 - Check Your Pairs of Parentheses

The first solution is to check that you have the correct number of parentheses.

If you're using an IDE such as SQL Developer, you can put your cursor next to each parenthesis to see where the matching parenthesis is. If it's in the right spot, great. If the match is showing up somewhere unexpected, then you're missing a parenthesis.

This can often happen if you're using nested functions.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Solution 2 - Check your CREATE TABLE Statement

If you get an ORA-00907 error when running a CREATE TABLE statement, it could be because of an incorrect reference to a foreign key.

For example:

1CREATE TABLE order_test (
2  order_id NUMBER NOT NULL PRIMARY KEY,
3  order_date DATE NOT NULL,
4  customer_id NUMBER FOREIGN KEY REFERENCES customer(customer_id)
5);

Result:

 1Error starting at line : 3 in command -
 2CREATE TABLE order_test (
 3  order_id NUMBER NOT NULL PRIMARY KEY,
 4  order_date DATE NOT NULL,
 5  customer_id NUMBER FOREIGN KEY REFERENCES customer(customer_id)
 6)
 7Error report -
 8SQL Error: ORA-00907: missing right parenthesis
 900907. 00000 -  "missing right parenthesis"
10*Cause:   
11*Action:

This happens because we don't need to have the words FOREIGN KEY when defining a foreign key inline (like we have here).

We can either:

• Remove the words FOREIGN KEY
• Declare the foreign key out of line (recommended)

Option A:

If you want to keep using the inline declaration, remove the words FOREIGN KEY:

1CREATE TABLE order_test (
2  order_id NUMBER NOT NULL PRIMARY KEY,
3  order_date DATE NOT NULL,
4  customer_id NUMBER REFERENCES customer(customer_id)
5);

The issue with this approach is you don't know the name of the foreign key, which can make maintenance harder.

It's better to declare a foreign key on a different line and give it a specific name.

Option B:

Declare the foreign key with a name

1CREATE TABLE order_test_prefer (
2  order_id NUMBER NOT NULL PRIMARY KEY,
3  order_date DATE NOT NULL,
4  customer_id NUMBER NOT NULL,
5  CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
6    REFERENCES customer (customer_id)
7);

This way, you can have the fk_order_customer as the constraint name, and can easily see and refer to it.

For a full guide on using the CREATE TABLE statement, including the syntax for Oracle, read my guide here.

Make sure your CREATE TABLE statement aligns with this syntax, and you shouldn't have any issues.

So, that's how you resolve the ORA-00907: missing right parenthesis error.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: