Ora 00907 missing right parenthesis ошибка - Не ошибается лишь тот, кто ничего не делает!

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.

Источник

Side note: Your subquery is going to require a full table scan on accounts, even if you have an index on date_opened, because you are doing math in the WHERE clause.

For every row, Oracle has to evaluate this expression:

((sysdate - a.date_opened) > 365)

to take the value of sysdate and subtract the date opened and then compare it to 365. It has to check each row individually.

However, if you algebraically change that expression to

sysdate - 365 > a.date_opened

then it only has to evaluate the expression sysdate-365 once, at the beginning of the query, and can then compare that to a.date_opened and use the index for an index scan.

Источник

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:

Источник

ORA-00907: missing right parenthesis error occurs when a left parenthesis is used without a right parenthesis to close it in SQL statements such as create table, insert, select, subquery, and IN clause. The right parenthesis is missing. All parentheses must be used in pairs. SQL statements that include multiple items should be contained in parentheses. The error ORA-00907: missing right parenthesis will be thrown If the left parenthesis has been used in the SQL Statement but the right parenthesis is missing.

Oracle’s collection of items is denoted by a parenthesis. If the right parenthesis is missing, Oracle will be unable to recognise the items specified after that. The error message ORA-00907: missing right parenthesis will be shown. The right parenthesis indicates the closing of the item list. Oracle could not recognise the end of the items list if the right parenthesis was missing. All left parenthesis in Oracle SQL must be paired with a right parenthesis. You’ll receive this error ORA-00907: missing right parenthesis if there are more left parenthesis than right parentheses.

When the ORA-00906 error occurs

The collection of items could not be provided if the right parenthesis was missing in the SQL Statement such as create table, insert table, select subquery, and IN clause. Create a SQL query that should include a collection of items but does not include the right parenthesis. In this case, the error message will be displayed. The error will be resolved if the right parenthesis is added before the collection of items

Problem

create table dept(
id number primary key,
name varchar2(100)

Error

Error starting at line : 3 in command -
create table dept(
id number primary key,
name varchar2(100)
Error report -
ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Root Cause

In Oracle, the collection of items is defined using enclosed parentheses. Oracle could not identify the closing of the collection of items list if the right parenthesis was missing. Oracle anticipates the right parenthesis after the list. Oracle will give an error if the right parenthesis is missing.

Solution 1

If the parenthesis in the anticipated SQL Statement is missing, the error will be thrown. The right parenthesis for specifying the item collection is missing. The error will be fixed if you add the missing right parentheses.

Problem

create table dept(
id number primary key,
name varchar2(100)

Error report -
ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Solution

create table dept(
id number primary key,
name varchar2(100)
);

Solution 2

The column data type, as well as the size or precision of the data type, should be provided. The error will be thrown if the size of the data type is provided in the column definition without right parenthesis. Oracle will look for the size by enclosing a value in parentheses. The error message will be displayed if the right parenthesis is missing right after the data type size.

Problem

create table dept(
id number primary key,
name varchar2(100,
sal number
);

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Solution

create table dept(
id number primary key,
name varchar2(100),
sal number
);

Solution 3

The subqueries are added with a enclosed parenthesis in the where clause. If the right parenthesis is missing in the subquery, the error message will be shown.

Problem

select * from employee where deptid in (select id from dept ;

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Solution

select * from employee where deptid in (select id from dept) ;

Solution 4

The values in the IN clause. is enclosed with parenthesis. If the right parenthesis is missing, the closing of the list could not be identified. The error message will be shown.

Problem

select * from employee where deptid in (1,2 ;

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Solution

select * from employee where deptid in (1,2) ;

Solution 5

All left parenthesis in Oracle SQL must be paired with a right parenthesis. You’ll see this error ORA-00907: missing right parenthesis if there are more left parenthesis than right parentheses.

Problem

select * from employee where deptid in (select id from dept where name in (select name from branches) ;

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Solution

select * from employee where deptid in (select id from dept where name in (select name from branches)) ;

Источник

ORA-00907

ORA-00907: упущены правые скобки

Причина:

Были введены левые скобки без закрытия правыми скобками или в скобках была заключена дополнительная информация. Все скобки должны вводится парами.

Действие:

Убедитесь, что вы имеете парное множество скобок, затем выполните выражение снова.

Источник