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



6 Comments

  • Aysuh , May 17, 2022 @ 6:53 AM

    CREATE TABLE residents(
    R_ID NUMBER(4), CONSTRAINTS pk_residents_R_ID PRIMARY KEY,
    R_FN VARCHAR2(15), NOT NULL,
    R_LN VARCHAR2(15), NOT NULL,
    R_Contact NUMBER(10), NOT NULL,
    DoB DATE, NOT NULL
    );

    ORA-00906: missing left parenthesis

    Been getting this error. Any help would be appreciated!

  • Pedro Ferreira , February 10, 2022 @ 4:22 AM

    Thanks !!

    Did it but now i have a new error: – ORA-02270: no matching unique or primary key for this column-list .

  • Pedro Ferreira , February 9, 2022 @ 5:36 AM

    Create Table Classe
    (
    Cd_Classe Number(4) NOT NULL,
    Nr_Serie NUMBER(2) NOT NULL,
    Sg_Turma Varchar2(2) NOT NULL,
    Cd_Escola NUMBER(6) NOT NULL,
    Cd_Grau NUMBER(2) NOT NULL,
    Cd_Periodo NUMBER(2) NOT NULL,
    CONSTRAINT Classe_fk_Cd_Classe FOREIGN KEY References Matricula(Cd_Classe)
    );

    ORA-00906: missing left parenthesis

    –Trying to do this one but always gives me that error. Can you help me with any solution?–

    Thanks

    • Yawin Tutor , February 9, 2022 @ 7:02 AM

      Change constraint line as

      CONSTRAINT Classe_fk_Cd_Classe FOREIGN KEY (Cd_Classe) references Matricula(Cd_Classe)

      • Pedro Ferreira , February 10, 2022 @ 12:40 AM

        Thanks !!

        Did it but now i have a new error: – ORA-02270: no matching unique or primary key for this column-list .

      • Pedro Ferreira , February 10, 2022 @ 4:20 AM

        Thanks !!

        But now i have a new error.

        ORA-02270: no matching unique or primary key for this column-list

Leave a Reply

Your email address will not be published. Required fields are marked *