ORA-00907: missing right parenthesis

The “ORA-00907: missing right parenthesis” error in Oracle occurs when a left parenthesis is used without a corresponding right parenthesis in SQL statements, such as those involving the creation of tables, insertion, selection, subqueries, or the IN clause. It indicates that there is an imbalance in the usage of parentheses, and all parentheses must be used in pairs.

This error specifically points to a situation where the left parenthesis has been used in the SQL statement, but the right parenthesis is missing. To resolve this issue, ensure that each left parenthesis has a corresponding right parenthesis, especially in cases involving multiple items or subqueries enclosed in parentheses. This ensures the correct syntax and avoids the “ORA-00907: missing right parenthesis” error.

When the ORA-00906 error occurs

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

Solution 1

If the anticipated SQL statement lacks the required parenthesis, an error will be triggered. In particular, the absence of the expected right parenthesis for specifying the collection of items will result in an error. This error can be rectified by adding the missing right parentheses to ensure a balanced and correctly formed syntax in the SQL statement.

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

When defining a column in Oracle, it is essential to specify both the data type and, if applicable, the size or precision of that data type. An error will occur if the size of the data type is provided without the right parenthesis in the column definition. Oracle expects the size to be enclosed within parentheses, and if the right parenthesis is missing immediately after specifying the data type size, an error message will be displayed. It is crucial to ensure that the syntax includes the correct pairing of parentheses to avoid this error.

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

In SQL, subqueries within the WHERE clause are enclosed in parentheses to delineate their scope. If the right parenthesis is omitted within a subquery, an error occurs, often indicated by an error message. This error highlights the importance of correctly pairing parentheses to maintain syntactical integrity in SQL statements, ensuring the proper representation and execution of subqueries. Adhering to this syntax is crucial to prevent errors and optimize the functionality of complex SQL queries that involve subqueries within the WHERE clause.

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

Values within the IN clause are enclosed in parentheses, and if the right parenthesis is omitted, it disrupts the identification of the list’s closure. This results in an error message being displayed, underscoring the importance of proper parenthesis pairing to maintain syntactic correctness in SQL statements involving the IN clause.

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

In Oracle SQL, every left parenthesis must be accompanied by a corresponding right parenthesis. The “ORA-00907: missing right parenthesis” error occurs when there are more left parentheses than right parentheses, highlighting the need for a balanced pairing of parentheses in SQL statements.

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)) ;