Problem in creating new table ORA-00906: missing left parenthesis

Here is my instruction : it works on my friend computer !

CREATE TABLE  typeRdv1(
    id_type int  PRIMARY KEY,
    des VARCHAR(20)
);

CREATE TABLE  rdv1(
    id_rdv int  PRIMARY KEY,
    cin_pat VARCHAR(20),
    date_rdv VARCHAR NOT NULL,
    type_rdv VARCHAR(20),
    state VARCHAR(20),

    CONSTRAINT `FK_cin_rdv` FOREIGN KEY (cin_pat)
    REFERENCES patients(cin_pat) ON DELETE NO ACTION,

    CONSTRAINT `FK_id_type_rdv` FOREIGN KEY (type_rdv) 
    REFERENCES typeRdv1(id_type) ON DELETE NO ACTION
);

A ORA-00906: missing left parenthesis

Any help !

Answer

Please make sure all the “varchar” fields has a length defined. In your second table, it says: date_rdv VARCHAR NOT NULL, which I believe causing the issue, so try to change it into date_rdv VARCHAR(50) NOT NULL and try again.

...
CREATE TABLE  rdv1(
    id_rdv int  PRIMARY KEY,
    cin_pat VARCHAR(20),
    date_rdv VARCHAR(50) NOT NULL, --length added to varchar 
    type_rdv VARCHAR(20),
    state VARCHAR(20),
    ...

EDIT:

The reason you get “invalid character” error is that you cannot use “on delete no action” while creating a table with a constraint. Instead you should alter your table after to create your constraints with “on delete cascade” option. Probably like below ( you may need to drop your constraints first):

CREATE TABLE  patients(
    cin_pat int  PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE  typeRdv1(
    id_type int  PRIMARY KEY,
    des VARCHAR(20)
);

CREATE TABLE rdv1(
    id_rdv int  PRIMARY KEY,
    cin_pat VARCHAR(20),
    date_rdv VARCHAR(50) NOT NULL,
    type_rdv VARCHAR(20),
    state VARCHAR(20)
);

alter table rdv1 add (
    CONSTRAINT FK_cin_rdv FOREIGN KEY (cin_pat)
    REFERENCES patients (cin_pat) ON DELETE cascade,

    CONSTRAINT FK_id_type_rdv FOREIGN KEY (type_rdv) 
    REFERENCES typeRdv1 (id_type) ON DELETE cascade
);

Check this for more details: https://www.haidongji.com/2006/07/24/defining-no-action-foreign-key-constraints-in-oracle/comment-page-1/