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:
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.
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:
Thank you!!
what if there are two foreign keys
You can have two foreign keys in a table, which can point to different tables. One foreign key can contain two or more columns as well.
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
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:
CREATE TABLE CONSTE(
CUST_ID NUMBER(4) PRIMARY KEY,
CUST_NAME VARCHAR2(25) NOT NULL,
CUST_MOBILE NUMBER(12)UNIQUE CHECK(LENGTH(CUST_MOBILE)=10),
CUST_AGE NUMBER(3) CK_CUST_AGE CHECK(AGE>=18),
CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (CITY_ID NUMBER(8))
REFERENCES CITY(CITY_ID)
);
ERROR MISSING RIGHT PARENTHSIS
It could be happening because you need a space before the word UNIQUE for the cust_mobile column, and that “ck_cust_age” can’t be defined where it is (you can’t give a name to an inline constraint). Also you don’t need to define the data type of the city_id in the foreign key. Try this:
CREATE TABLE CONSTE(
CUST_ID NUMBER(4) PRIMARY KEY,
CUST_NAME VARCHAR2(25) NOT NULL,
CUST_MOBILE NUMBER(12) UNIQUE CHECK(LENGTH(CUST_MOBILE)=10),
CUST_AGE NUMBER(3),
CONSTRAINT FK_ORDER_CUSTOMER FOREIGN KEY (CITY_ID)
REFERENCES CITY(CITY_ID),
CONSTRAINT CK_CUST_AGE CHECK(AGE>=18)
);
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4), HIREDATE DATE,SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2), CONSTRAINT FK_EMP FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);
ORA-00907: missing right parenthesis
HELP !!
CREATE TABLE city
(id int,
name varchar
)
ORA-00907: missing right parenthesis
HELP !!
You’ll need to add a length to the VARCHAR data type declaration. For example, for 50 characters, use VARCHAR(50).
Also because this is Oracle, you may want to consider using VARCHAR2 instead of VARCHAR.
Create table PR
(IIURM NUMBER(10),
TLLK NUMBER(10),
HGLLK NUMBER(10),
FGLKK NUMBER(10) AS (HGLLK-TLLK));
Error report
ORA-00907: missing right parenthesis
Please help