ORA-00906: missing left parenthesis

ORA-00906: missing left parenthesis error occurs when the left parenthesis is missing in SQL statements such as create table, insert, select, subquery, and IN clause. SQL statements that include multiple items should be contained in parentheses. The error ORA-00906: missing left parenthesis will be thrown if the left parenthesis in the SQL Statement is missing.

Oracle’s collection of items is denoted by a parenthesis. If the left parenthesis is missing, Oracle will be unable to recognize the items specified after that. The error message ORA-00906: missing left parenthesis will be shown. The left parenthesis indicates the beginning of the item list. Oracle could not recognize the start of the items list if the left parenthesis was missing.

When the ORA-00906 error occurs

The collection of items could not be provided if the left 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 left parenthesis. In this case, the error message will be displayed. The error will be resolved if the left parenthesis is added before the collection of items

Problem

create table dept;

Error

create table dept
Error report -
ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"
*Cause:    
*Action:

Root Cause

In Oracle, the collection of items is defined using enclosed parentheses. Oracle could not identify the beginning of the collection of items list if the left parenthesis was missing. Oracle anticipates the left parenthesis before to the list. Oracle will give an error if the left parenthesis is missing.

Solution 1

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

Problem

create table dept;

ORA-00906: missing left parenthesis
00906. 00000 -  "missing left 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 not provided in the column definition. Oracle will look for the size by enclosing a value in parentheses. The error message will be displayed if the left parenthesis is missing right after the data type.

Problem

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

ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"

Solution

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

Solution 3

The constrains requires the column name should be specified while declaring. If the column names are not specified in an enclosed parenthesis, the error will be thrown.

Problem

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key references dept(id)
);

ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"

Solution

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key (deptid) references dept(id)
);