Missing right parenthesis oracle ошибка

Обновлено: 06.10.2022

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.

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

Solution

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.

4 Answers 4

ORA-00907: missing right parenthesis

This is one of several generic error messages which indicate our code contains one or more syntax errors. Sometimes it may mean we literally have omitted a right bracket; that's easy enough to verify if we're using an editor which has a match bracket capability (most text editors aimed at coders do). But often it means the compiler has come across a keyword out of context. Or perhaps it's a misspelled word, a space instead of an underscore or a missing comma.

Unfortunately the possible reasons why our code won't compile is virtually infinite and the compiler just isn't clever enough to distinguish them. So it hurls a generic, slightly cryptic, message like ORA-00907: missing right parenthesis and leaves it to us to spot the actual bloomer.

The posted script has several syntax errors. First I will discuss the error which triggers that ORA-0097 but you'll need to fix them all.

Foreign key constraints can be declared in line with the referencing column or at the table level after all the columns have been declared. These have different syntaxes; your scripts mix the two and that's why you get the ORA-00907.

In-line declaration doesn't have a comma and doesn't include the referencing column name.

Table level constraints are a separate component, and so do have a comma and do mention the referencing column.

Here is a list of other syntax errors:

  1. The referenced table (and the referenced primary key or unique constraint) must already exist before we can create a foreign key against them. So you cannot create a foreign key for HISTORYS_T before you have created the referenced ORDERS table.
  2. You have misspelled the names of the referenced tables in some of the foreign key clauses ( LIBRARY_T and FORMAT_T ).
  3. You need to provide an expression in the DEFAULT clause. For DATE columns that is usually the current date, DATE DEFAULT sysdate .

Looking at our own code with a cool eye is a skill we all need to gain to be successful as developers. It really helps to be familiar with Oracle's documentation. A side-by-side comparison of your code and the examples in the SQL Reference would have helped you resolved these syntax errors in considerably less than two days. Find it here (11g) and here (12c).

As well as syntax errors, your scripts contain design mistakes. These are not failures, but bad practice which should not become habits.

  1. You have not named most of your constraints. Oracle will give them a default name but it will be a horrible one, and makes the data dictionary harder to understand. Explicitly naming every constraint helps us navigate the physical database. It also leads to more comprehensible error messages when our SQL trips a constraint violation.
  2. Name your constraints consistently. HISTORY_T has constraints called historys_T_FK and fk_order_id_orders , neither of which is helpful. A useful convention is __fk . So history_customer_fk and history_order_fk respectively.
  3. It can be useful to create the constraints with separate statements. Creating tables then primary keys then foreign keys will avoid the problems with dependency ordering identified above.
  4. You are trying to create cyclic foreign keys between LIBRARY_T and FORMATS . You could do this by creating the constraints in separate statement but don't: you will have problems when inserting rows and even worse problems with deletions. You should reconsider your data model and find a way to model the relationship between the two tables so that one is the parent and the other the child. Or perhaps you need a different kind of relationship, such as an intersection table.
  5. Avoid blank lines in your scripts. Some tools will handle them but some will not. We can configure SQL*Plus to handle them but it's better to avoid the need.
  6. The naming convention of LIBRARY_T is ugly. Try to find a more expressive name which doesn't require a needless suffix to avoid a keyword clash.
  7. T_CUSTOMERS is even uglier, being both inconsistent with your other tables and completely unnecessary, as customers is not a keyword.

Naming things is hard. You wouldn't believe the wrangles I've had about table names over the years. The most important thing is consistency. If I look at a data dictionary and see tables called T_CUSTOMERS and LIBRARY_T my first response would be confusion. Why are these tables named with different conventions? What conceptual difference does this express? So, please, decide on a naming convention and stick to. Make your table names either all singular or all plural. Avoid prefixes and suffixes as much as possible; we already know it's a table, we don't need a T_ or a _TAB .

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

Solution

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

Solution

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.

This happens because we don’t need to have the words FOREIGN KEY when defining a foreign key inline (like we have here).

  • Remove the words FOREIGN KEY
  • Declare the foreign key out of line (recommended)

If you want to keep using the inline declaration, remove the words FOREIGN KEY

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.

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

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

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

ORA-00907: missing right parenthesis .

Вот результаты, которые я получаю, когда запускаю код:

ORA-00907: missing right parenthesis

Ограничения внешнего ключа могут быть объявлены вместе со ссылочным столбцом или на уровне таблицы после того, как были объявлены все столбцы. У них разный синтаксис; ваши сценарии смешивают оба, и поэтому вы получаете ORA-00907.

Встроенное объявление не имеет запятой и не включает имя ссылочного столбца.

Ограничения на уровне таблицы - это отдельный компонент, поэтому они имеют запятую и упоминают ссылочный столбец.

Вот список других синтаксических ошибок:

  1. Указанная таблица (и указанный первичный ключ или ограничение уникальности) должны уже существовать, прежде чем мы сможем создать для них внешний ключ. Таким образом, вы не можете создать внешний ключ HISTORYS_T до тех пор, пока не создадите указанную ORDERS таблицу.
  2. Вы неправильно написали имена таблиц, на которые есть ссылки в некоторых предложениях внешнего ключа ( LIBRARY_T и FORMAT_T ).
  3. Вам необходимо указать выражение в предложении DEFAULT. Для DATE столбцов , которые, как правило , текущая дата, DATE DEFAULT sysdate .

Хладнокровный взгляд на собственный код - это навык, который нам всем нужно приобрести, чтобы быть успешными разработчиками. Знакомство с документацией Oracle действительно помогает. Параллельное сравнение вашего кода и примеров в Справочнике SQL помогло бы вам разрешить эти синтаксические ошибки значительно менее чем за два дня. Найдите его здесь (11g) и здесь (12c) .

Помимо синтаксических ошибок, ваши скрипты содержат ошибки дизайна. Это не неудачи, а плохая практика, которая не должна переходить в привычку.

Называть вещи сложно. Вы не поверите, сколько лет у меня были споры по поводу названий таблиц. Самое главное - последовательность. Если я смотрю на словарь данных и посмотреть таблицы называется T_CUSTOMERS и LIBRARY_T мой первый ответ будет путаница. Почему эти таблицы названы по-разному? Какую концептуальную разницу выражает это? Поэтому, пожалуйста, определитесь с соглашением об именах и придерживайтесь его. Сделайте имена таблиц в единственном или множественном числе. По возможности избегайте префиксов и суффиксов; мы уже знаем, что это таблица, нам не нужны a T_ или _TAB .

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?

Here are the results I get when I run the code:

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

Solution

4 Answers 4

ORA-00907: missing right parenthesis

This is one of several generic error messages which indicate our code contains one or more syntax errors. Sometimes it may mean we literally have omitted a right bracket; that's easy enough to verify if we're using an editor which has a match bracket capability (most text editors aimed at coders do). But often it means the compiler has come across a keyword out of context. Or perhaps it's a misspelled word, a space instead of an underscore or a missing comma.

Unfortunately the possible reasons why our code won't compile is virtually infinite and the compiler just isn't clever enough to distinguish them. So it hurls a generic, slightly cryptic, message like ORA-00907: missing right parenthesis and leaves it to us to spot the actual bloomer.

The posted script has several syntax errors. First I will discuss the error which triggers that ORA-0097 but you'll need to fix them all.

Foreign key constraints can be declared in line with the referencing column or at the table level after all the columns have been declared. These have different syntaxes; your scripts mix the two and that's why you get the ORA-00907.

In-line declaration doesn't have a comma and doesn't include the referencing column name.

Table level constraints are a separate component, and so do have a comma and do mention the referencing column.

Here is a list of other syntax errors:

  1. The referenced table (and the referenced primary key or unique constraint) must already exist before we can create a foreign key against them. So you cannot create a foreign key for HISTORYS_T before you have created the referenced ORDERS table.
  2. You have misspelled the names of the referenced tables in some of the foreign key clauses ( LIBRARY_T and FORMAT_T ).
  3. You need to provide an expression in the DEFAULT clause. For DATE columns that is usually the current date, DATE DEFAULT sysdate .

Looking at our own code with a cool eye is a skill we all need to gain to be successful as developers. It really helps to be familiar with Oracle's documentation. A side-by-side comparison of your code and the examples in the SQL Reference would have helped you resolved these syntax errors in considerably less than two days. Find it here (11g) and here (12c).

As well as syntax errors, your scripts contain design mistakes. These are not failures, but bad practice which should not become habits.

  1. You have not named most of your constraints. Oracle will give them a default name but it will be a horrible one, and makes the data dictionary harder to understand. Explicitly naming every constraint helps us navigate the physical database. It also leads to more comprehensible error messages when our SQL trips a constraint violation.
  2. Name your constraints consistently. HISTORY_T has constraints called historys_T_FK and fk_order_id_orders , neither of which is helpful. A useful convention is __fk . So history_customer_fk and history_order_fk respectively.
  3. It can be useful to create the constraints with separate statements. Creating tables then primary keys then foreign keys will avoid the problems with dependency ordering identified above.
  4. You are trying to create cyclic foreign keys between LIBRARY_T and FORMATS . You could do this by creating the constraints in separate statement but don't: you will have problems when inserting rows and even worse problems with deletions. You should reconsider your data model and find a way to model the relationship between the two tables so that one is the parent and the other the child. Or perhaps you need a different kind of relationship, such as an intersection table.
  5. Avoid blank lines in your scripts. Some tools will handle them but some will not. We can configure SQL*Plus to handle them but it's better to avoid the need.
  6. The naming convention of LIBRARY_T is ugly. Try to find a more expressive name which doesn't require a needless suffix to avoid a keyword clash.
  7. T_CUSTOMERS is even uglier, being both inconsistent with your other tables and completely unnecessary, as customers is not a keyword.

Naming things is hard. You wouldn't believe the wrangles I've had about table names over the years. The most important thing is consistency. If I look at a data dictionary and see tables called T_CUSTOMERS and LIBRARY_T my first response would be confusion. Why are these tables named with different conventions? What conceptual difference does this express? So, please, decide on a naming convention and stick to. Make your table names either all singular or all plural. Avoid prefixes and suffixes as much as possible; we already know it's a table, we don't need a T_ or a _TAB .

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

ORA-00907: missing right parenthesis Solution

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.

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.

ORA-00907 Solution

There are a few ways to resolve this error.

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

Error

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.

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?

Here are the results I get when I run the code:

Читайте также: