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:
При использовании коррелированных подзапросов, т.е. подзапросов, результаты которых используются для каждой строки основного запроса — «a correlated subquery is evaluated once for each row», могут встречаются ошибки, неточно объясняющие, что делается неправильно
ora-904 «%s: invalid identifier»
SQL> select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual;
select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual
*
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier
ora-907 «missing right parenthesis»
SQL> select (select empno from scott.emp where ename = dual.dummy order by 1) from dual; select (select empno from scott.emp where ename = dual.dummy order by 1) from dual * ERROR at line 1: ORA-00907: missing right parenthesis
Первая ошибка ora-904 в случае correlated subquery при отсутствии синтаксических ошибок сообщает об использовании более, чем одного уровня глубины вложенности (N-th level sub-query):
SQL> select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual;
select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual
*
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier
что не поддерживается Oracle (до версии 11.1.0.7 включительно) и стандартом SQL: ANSI SQL has table references (correlation names) scoped to just one level deep. Это пишет вице-президент Oracle T.Kyte, отвечая на соответствующий вопрос «Is there some sort of nesting limit for correlated subqueries?», т.е. информация официальная, из первых рук, пример оттуда же. В документации Oracle, начиная с 10.1, пишется иначе:
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery
— но, это, видимо, долгосрочные планы Oracle.
ora-907
SQL> select (select empno from scott.emp where ename = dual.dummy order by 1) from dual; select (select empno from scott.emp where ename = dual.dummy order by 1) from dual * ERROR at line 1: ORA-00907: missing right parenthesis
тут проблема старая и известная: запрет на использование ORDER BY в подзапросах, описанная в документации Oracle 7 (в документации следующих версий уже не упоминается — видимо, тоже есть планы по исправлению):
The ORDER BY clause cannot appear in subqueries within other statements.
По вышеупомянутой ссылке на сайте asktom.oracle.com можно найти пример замены конструкции с ORDER BY типа:
(SELECT column_1 FROM (SELECT column_1 FROM table_1 WHERE ... ORDER BY ...) Q_1 WHERE rownum = 1)
на аналитическую функцию типа :
(SELECT max(column_1) keep (dense_rank first ORDER BY ...) FROM table_1 WHERE ...)
для одновременного понижения уровня вложенности подзапроса (correlated query level deep) до 1 и устранения проблемы с ORDER BY в подзапросе — конструкция dense_rank first ORDER BY допускается Oracle.
Комментарии к записи ora-904 ora-907 при использовании коррелированных подзапросов отключены
ORA-00907 means that SQL parser saw an illegal string or expression where it expects a right parenthesis to close the inner statement. Usually, it’s a syntax problem.
SQL> create table fruits (fruit_name varchar2(20), price number not null default 20);
create table fruits (fruit_name varchar2(20), price number not null default 20)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
Please note that, the right parenthesis is also known as right round bracket. In CREATE TABLE statement, what parenthesis encloses is the column list.
In this case, DEFAULT is a valid reserved keyword, but it should be appeared before NOT NULL, not after. So we should rearrange the order of clauses in the statement to correct the problem.
SQL> create table fruits (fruit_name varchar2(20), price number default 20 not null);
Table created.
We fixed it.
In other words, if a right parenthesis was at the position where the error points out in the message, the statement should have no problem, unfortunately, it’s unlikely what we want.