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.

 

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:

CREATE TABLE order_test (
  order_id NUMBER NOT NULL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id NUMBER FOREIGN KEY REFERENCES customer(customer_id)
);

Result:

Error starting at line : 3 in command -
CREATE TABLE order_test (
  order_id NUMBER NOT NULL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id NUMBER FOREIGN KEY REFERENCES customer(customer_id)
)
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:   
*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)

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

CREATE TABLE order_test (
  order_id NUMBER NOT NULL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id NUMBER REFERENCES customer(customer_id)
);

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.

For example:

CREATE TABLE order_test_prefer (
  order_id NUMBER NOT NULL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id NUMBER NOT NULL,
  CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id)
);

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 to this syntax, and you shouldn’t have any issues.

 

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

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Improve Your Oracle SQL With My 10-Day Email Course

x