FB

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.

 

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)

Option A:

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.

Option B:

Declare the foreign key with a name

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

 

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

5 thoughts on “ORA-00907: missing right parenthesis Solution”

  1. CREATE TABLE tbl_book (
    book_BookID INT PRIMARY KEY NOT NULL IDENTITY (1,1),
    book_Title VARCHAR(100) NOT NULL,
    book_PublisherName VARCHAR(100) NOT NULL CONSTRAINT fk_publisher_name1 FOREIGN KEY REFERENCES tbl_publisher(publisher_PublisherName) ON UPDATE CASCADE ON DELETE CASCADE,
    );

    error :- missing right parenthesis

  2. CREATE TABLE ABC_TGT1
    (
    id INT,
    name VARCHAR2(20),
    address VARCHAR2(50),
    phone no DOUBLE
    )
    Error report –
    ORA-00907: missing right parenthesis
    00907. 00000 – “missing right parenthesis”
    *Cause:
    *Action:

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.