ORA-00907: missing right parenthesis, and nothing is working

I’ve been sitting and looking at this code for a few hours and I don’t understand where I went wrong, why it doesn’t work.

ORA-00907: missing right parenthesis

I saw that this is a topic that is discussed alot but for some reason none of the examples I have seen has helped me. I’m pretty sure it’s all right with the parentheses.

Here is the code:

CREATE TABLE ADT_FILIALA_AGENTIE (
  id_f  NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
  name  varchar(30) NOT NULL,
  telephone  varchar(30) NOT NULL,
  adress  varchar(30) NOT NULL,
  nr_an  varchar(30) NOT NULL,
  id_a  int(11) NOT NULL,
  PRIMARY KEY(id_f),
  FOREIGN KEY(id_a) REFERENCES ADT_AGENTIE_PRINCIPALA(id_a) 
);

Answer

You can’t specify precision for the int data type in Oracle. You are using it for column id_a. int is shorthand for number(38,0).

To get the desired effect, replace that with number(11,0) – which means precision of 11 digits, of which zero decimal places.

Other than that, you would be well advised, in Oracle, to use varchar2(n) rather than varchar(n). For a very long time Oracle has warned us that in future releases varchar may be used for something else (even though that hasn’t happened and is unlikely to happen).

To explain the error message: Right after the parser reads id_a int it expects (optional space and) a comma, or a constraint, etc. What it does not expect (since it makes no sense in that position) is an opening parenthesis, as you have in (11). The error handling is written to assume that at that point the create table statement should have ended – with a closing parenthesis. It doesn’t find it, so it complains about that.

That’s why you saw so many unrelated mentions of “missing right parenthesis” errors – they are often thrown by the parser when it finds something unexpected in a statement. The parser doesn’t know what the real error is – it just sees something that doesn’t make sense somewhere, and if at that point a closing parenthesis would have ended a valid statement, it throws that error.