I have been looking at this code for the past two days now and I can not seem to get it to work. It keeps giving me
ORA-00907: missing right parenthesis.
I know that this is a topic that comes up a lot but for some reason none of the examples I have seen has helped me. Can someone please tell me why I got this error and how do I fix it? I am pretty sure that it has nothing to do with my parenthesis, maybe it’s my CONSTRAINTS?
DROP TABLE T_customers CASCADE CONSTRAINTS;
DROP TABLE dvd_collection CASCADE CONSTRAINTS;
DROP TABLE vhs_collection CASCADE CONSTRAINTS;
CREATE TABLE T_customers (
customer_id VARCHAR2 (8) PRIMARY KEY,
last_name VARCHAR2 (30) NOT NULL,
first_name VARCHAR2 (20) NOT NULL,
street VARCHAR2 (30) NOT NULL,
city VARCHAR2 (30) NOT NULL,
state CHAR (2) NOT NULL,
CHECK (state IN ('GA','DC','VA','NY')),
zip_code CHAR (5)
CHECK (TO_NUMBER(zip_code)
BETWEEN 10000 AND 27999),
home_phone VARCHAR2 (12) UNIQUE,
work_phone VARCHAR2 (12) UNIQUE,
email VARCHAR2 (95) NOT NULL);
CREATE TABLE historys_T (
history_record VARCHAR2 (8),
customer_id VARCHAR2 (8),
CONSTRAINT historys_T_FK FOREIGN KEY (customer_id) REFERENCES T_customer
ON DELETE CASCADE,
order_id VARCHAR2 (10) NOT NULL,
CONSTRAINT fk_order_id_orders
REFERENCES orders
ON DELETE CASCADE);
CREATE TABLE orders (
order_id VARCHAR2 (10) PRIMARY KEY,
m_p_unique_id VARCHAR2 (10),
CONSTRAINT orders_FK FOREIGN KEY (m_p_unique_id) REFERENCES library (m_p_unique_id)
order_date DATE DEFAULT);
CREATE TABLE library_T (
m_p_unique_id VARCHAR2 (10) PRIMARY KEY,
movie_title VARCHAR2 (80) NOT NULL,
serial_number VARCHAR2 (10) NOT NULL,
movie_id_number VARCHAR2 (10) NOT NULL,
movie_cast VARCHAR2 (100) NOT NULL,
movie_format CHAR (3) NOT NULL,
CONSTRAINT library_FK REFERENCES formats (movie_format));
CREATE TABLE formats_T (
movie_format CHAR (3) PRIMARY KEY,
movie_title VARCHAR2 (80) NOT NULL,
m_p_unique_id VARCHAR2 (10) NOT NULL,
CONSTRAINT format_FK REFERENCES library (m_p_unique_id));
CREATE TABLE dvd_collection (
m_p_unique_id VARCHAR2 (10) NOT NULL,
serial_number VARCHAR2 (10) NOT NULL,
movie_id_number VARCHAR2 (10) NOT NULL,
movie_title VARCHAR2 (80) NOT NULL,
movie_cast VARCHAR2 (100) NOT NULL,
movie_format VARCHAR2 (80) NOT NULL,
movie_rating VARCHAR2 (6) NOT NULL,
movie_distributer VARCHAR2 (30) NOT NULL,
movie_price NUMBER (3,2) NOT NULL,
movie_length NUMBER (3) NOT NULL,
movie_award VARCHAR2 (175) NOT NULL,
movie_release DATE);
CREATE TABLE vhs_collection
(
m_p_unique_id VARCHAR2 (10)NOT NULL,
serial_number VARCHAR2 (10) NOT NULL,
movie_id_number VARCHAR2 (10) NOT NULL,
movie_title VARCHAR2 (80) NOT NULL,
movie_cast VARCHAR2 (100) NOT NULL,
movie_format VARCHAR2 (80) NOT NULL,
movie_rating VARCHAR2 (6) NOT NULL,
movie_distributer VARCHAR2 (30) NOT NULL,
movie_price NUMBER (3,2) NOT NULL,
movie_length NUMBER (3) NOT NULL,
movie_award VARCHAR2 (175) NOT NULL,
movie_release DATE);
Here are the results I get when I run the code:
Table dropped.
Table dropped.
Table dropped.
Table created.
ON DELETE CASCADE)
*
ERROR at line 10:
ORA-00907: missing right parenthesis
order_date DATE DEFAULT)
*
ERROR at line 6:
ORA-00907: missing right parenthesis
CONSTRAINT library_FK REFERENCES formats (movie_format))
*
ERROR at line 9:
ORA-00907: missing right parenthesis
CONSTRAINT format_FK REFERENCES library (m_p_unique_id))
*
ERROR at line 6:
ORA-00907: missing right parenthesis
Table created.
Table created.
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.
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:
I am using Oracle SQL developer to create a basic table with the following command:
CREATE TABLE chartered_flight(
flight_no NUMBER(4) PRIMARY KEY
, customer_id FOREIGN KEY
, aircraft_no FOREIGN KEY
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time TO_DATE 'HH:MI' NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL
)
Where is the missing right parenthesis? Or is the syntax that I have used incorrect.
I have made the following changes:
CREATE TABLE chartered flight(
flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES [customer]([customer_id])
, aircraft_no NUMBER(4) REFERENCES [aircraft]([aircraft_no])
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)
Now I get this error:
Error at Command Line:1 Column:23
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 - "missing or invalid option"
*Cause:
*Action:
I have a feeling it is something to do with TO_DATE or is it because I have not created my aircraft table yet so aircraft_no is missing? Can some one please help, thanks.
If you’re working with Oracle databases, you might have encountered the ‘ora-00907: Missing Right Parenthesis’ error. This error occurs when there is a syntax error in your SQL statement. It can be frustrating, especially if you’re not sure how to fix it. In this guide, we’ll provide you with some top solutions to fix this error.
Solution 1: Check for Syntax Errors
The first solution to try is to check for syntax errors in your SQL statement. Make sure that all opening and closing parentheses match, and that you have included all necessary commas and semicolons. You can also use an online SQL validator tool to check for syntax errors.
Solution 2: Use a Code Editor
If you’re working with a large SQL statement, it can be difficult to spot syntax errors. Using a code editor with syntax highlighting can make it easier to identify errors. Code editors like Sublime Text, Visual Studio Code, or Notepad++ can help you identify syntax errors and fix them quickly.
Oracle databases come with debugging tools that can help you identify and fix errors. Use these tools to step through your SQL statement and identify the exact location of the error. Tools like SQL Developer and TOAD can help you debug your SQL statement and fix errors.
Solution 4: Check Oracle Documentation
If you’re not sure how to fix the ‘ora-00907: Missing Right Parenthesis’ error, check the Oracle documentation. The documentation provides detailed information on SQL syntax and error messages. Use the search function to find information on the error and how to fix it.
If none of the above solutions work, contact Oracle support for assistance. They have a team of experts who can help you identify and fix the error. Make sure to provide them with details about the error and your SQL statement.
FAQ
Q1. What is the ‘ora-00907: Missing Right Parenthesis’ error?
The ‘ora-00907: Missing Right Parenthesis’ error is a syntax error that occurs in your SQL statement when there is a missing right parenthesis.
Q2. Why am I getting the ‘ora-00907: Missing Right Parenthesis’ error?
You are getting the ‘ora-00907: Missing Right Parenthesis’ error because there is a syntax error in your SQL statement. It could be that you have a missing right parenthesis or a missing comma.
Q3. How do I fix the ‘ora-00907: Missing Right Parenthesis’ error?
You can fix the ‘ora-00907: Missing Right Parenthesis’ error by checking for syntax errors, using a code editor, using debugging tools, checking Oracle documentation, or contacting Oracle support.
Q4. How can I prevent the ‘ora-00907: Missing Right Parenthesis’ error?
You can prevent the ‘ora-00907: Missing Right Parenthesis’ error by double-checking your SQL statement for syntax errors and using a code editor with syntax highlighting.
Q5. What other errors can occur in Oracle databases?
Other errors that can occur in Oracle databases include ‘ora-00904: Invalid Identifier’, ‘ora-00933: SQL command not properly ended’, and ‘ora-01017: Invalid username/password; logon denied’.
- Oracle Documentation
- SQL Validator Tool
- Sublime Text
- Visual Studio Code
- Notepad++
Error ORA-00907 is a syntax error and therefore, is a commonly seen error by users writing code manually. This error indicates that there is a left parenthesis but no corresponding right parenthesis, or that additional information was contained within the parentheses.
To correct this error, you must find the part of code that contains the missing right parenthesis, insert the missing symbol in the correct spot, and run the statement again.
Error ORA-00907 can commonly occur in commands such as CREATE TABLE, CREATE CLUSTER, and INSERT, which all require an itemized list enclosed in parentheses. It can also occur within subqueries such as WHERE clauses, UPDATE table SET column = (SELECT…) statements.
In the following example, the missing right parenthesis after “20” would throw error ORA-00907:
CREATE TABLE employee
(
employee_name VARCHAR(20 NOT NULL,
employee_phone VARCHAR(10) NOT NULL,
PRIMARY KEY(employee_name)
)
This error also occurs often in cases in which quotation marks are improperly used. If you are using single quotation marks in phrases that are enclosed by other single quotation marks, you must add another single quotation by the inner single quotation. In other words, you cannot use 4 single quotation marks together (‘___’___’___’). It must be written as ‘___”___”___’.
For example, the following lines of code is missing the necessary number of quotation marks:
BEGIN
in_fieldname_list := ‘PROTOTYPE’ ’, ‘ ’ADDR1’ ’, ‘ ’CITY’ ’, ‘ ’STATE’ ’,’ ’ZIP’ ’, ‘ ’OAPPRAISAL’;
It should be:
BEGIN
in_fieldname_list := ‘ ‘ ‘PROTOTYPE’ ’, ‘ ’ADDR1’ ’, ‘ ’CITY’ ’, ‘ ’STATE’ ’,’ ’ZIP’ ’, ‘ ’OAPPRAISAL’ ‘ ’;
Error ORA-00907 specifically points to a missing right parenthesis while error ORA-00906 indicates that there is a missing left parenthesis.
To avoid seeing error ORA-00907, make sure to practice writing correct syntax. One of the easiest ways to ensure your syntax is correct is to use an integrated development environment that includes a source code editor. A good source code editor should offer features such as syntax highlighting, autocomplete, indentation, and matching brackets to help the coder automate the process of coding and make it easier to catch mistakes. With such features, you should be able to easily spot any line of code, which is causing error ORA-00907. If still facing this error after reviewing the syntax, check that it is not being caused by FULL/LEFT OUTER joins associated with Bug 4433936. While the bug has been fixed for later versions of Oracle, it can still be seen in earlier versions.