-3

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

an image of my whole code

What exactly is the underlying error?

1
  • 2
    Judging by the error code, you're running your statements against an Oracle database, yet you have tagged your question with mysql. Which is it? Oracle's INT data type does not support specifying precision.
    – mustaccio
    Oct 2 at 14:55
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.

5
  • What is the MySQL error that is better than Oracle's? (By the way, you've mentioned this twice.) Oct 3 at 10:30
  • @WilliamRobertson - MySQL gives a line number and shows you just before the VARCHAR2 whereas with INT(10) in Oracle, all you get is ORA-00907: missing right parenthesis (no line number) - and the parentheses are in fact balanced. Editing out second mention - thanks for the heads up! Oct 3 at 10:48
  • Oracle also gives the line number. Oct 4 at 21:34
  • Reran it - no line number - at least not on dbfiddle... Maybe if you're running it locally? Oct 5 at 0:39
  • No, dbfidddle does not display the full error details. I don't see how that is Oracle's fault. Oct 6 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.