ORA-00907 error missing right parenthesis in CREATE TABLE

Question

I am getting an error as ORA-00907 (missing right parenthesis) but I think it's a generic error and not related to parentheses.

CREATE TABLE reservation
(
  PNRno int(9) primary key, 
  journey_date DATE, 
  no_of_seats int(8), 
  address varchar2(50), 
  contact_no int(10), 
  busno varchar2(10) references bus(busno), 
  seat_no int(25)
)

What exactly is the underlying error?

Answer 1

Specifying a value after INT (e.g. INT(8)) does nothing in MySQL apart from affecting the ZEROFILL option. From here (the poster has ~ 500K rep. on StackOverflow and was MySQL Community Manager, so he probably knows what he's talking about):

The 20 in INT(20) and BIGINT(20) means almost nothing. It's a hint for display width. It has nothing to do with storage, nor the range of values that column will accept.

This syntax:

CREATE TABLE bus
(
  busno VARCHAR (10) PRIMARY KEY
);

and:

CREATE TABLE reservation
(
  PNRno        INT PRIMARY KEY, 
  journey_date DATE, 
  no_of_seats  INT, 
  address      VARCHAR (50), 
  contact_no   INT, 
  busno        VARCHAR (10), 
  seat_no      INT,
  
  CONSTRAINT res_bus_busno_fk FOREIGN KEY (busno) REFERENCES bus (busno)
);

works in both Oracle and MySQL (see the fiddles here and here).

A few points:

• please try and tag your questions with the correct RDBMS - some people here only answer questions on a particular server.

• you cannot just copy and paste code from one server to another - SQL has "dialects" which vary from vendor to vendor! There are some standards, but once you depart from these (as with INT(10) for example), you'll rapidly run into problems porting code.

• here is what happens when you put in something like INT(10) into an Oracle table definition which is exactly your error! Oracle's error messages are not the clearest. Check the PostgreSQL fiddle here where the integer with zerofill error is given a line number.

  Another case in point, VARCHAR2 (xxx) is an Oracle data type and not a MySQL one. You can see that even MySQL's error messages are better than Oracle's!

• please use the formatting tools for your SQL (be it DDL or DML). Having an entire table definition on one line is not great for readability! You could make your own life much easier by having your table defintions over multiple lines and matching brackets.

• please do not upload images for the reasons outlined in this link.

  Anyone reading your question who wanted to Google ORA-00907 would have to type it in manually adding unnecessary work and introducing the possibility of error! There are approx. 15 other factors to consider before posting images given in the link.