Right Parenthesis error 'INT(8)' in SQLPLUS-CodePudding

I'm trying to rectify this error

SQL> CREATE TABLE Reservation_21BAI1750(

  2  PNR number(9),

  3  Journey_Date Date NULL,

  4  No_of_Seats INT(8) ZEROFILL CHECK (No_of_Seats < 50),

  5  Address varchar2(50) NULL,

  6  Contact_No number(9) UNIQUE,

  7  BusNo varchar2(10),

  8  Seat_No number NOT NULL

  9  );

Error message received:

No_of_Seats INT(8) ZEROFILL CHECK (No_of_Seats < 50), * ERROR at line 4: ORA-00907: missing right parenthesis

I get the exact same error if I don't use ZEROFILL.

CodePudding user response：

There's no ZEROFILL in Oracle, while INT datatype doesn't have size. Also, you don't have to specify NULLs for columns that accept them; that's default.

SQL> CREATE TABLE reservation_21bai1750
  2  (
  3     pnr            NUMBER (9),
  4     journey_date   DATE,
  5     no_of_seats    INT CHECK (no_of_seats < 50),
  6     address        VARCHAR2 (50),
  7     contact_no     NUMBER (9) UNIQUE,
  8     busno          VARCHAR2 (10),
  9     seat_no        NUMBER NOT NULL
 10  );

Table created.

SQL>

CodePudding user response：

The error message ORA-00907: missing right parenthesis is indicating that there is a missing or unbalanced parenthesis in your SQL statement.

It looks like the issue is with the way you are defining the No_of_Seats column. In Oracle, the INT data type does not accept a size parameter. Therefore, you should remove the (8) in the definition of the No_of_Seats column, as it is causing the missing parenthesis error:

CREATE TABLE Reservation_21BAI1750(
  PNR number(9),
  Journey_Date Date NULL,
  No_of_Seats INT ZEROFILL CHECK (No_of_Seats < 50),
  Address varchar2(50) NULL,
  Contact_No number(9) UNIQUE,
  BusNo varchar2(10),
  Seat_No number NOT NULL
);

Also you don't need to use the keyword 'INT', it is only the Integer type, and Oracle automatically makes the column as INTEGER.

CREATE TABLE Reservation_21BAI1750(
  PNR number(9),
  Journey_Date Date NULL,
  No_of_Seats INTEGER ZEROFILL CHECK (No_of_Seats < 50),
  Address varchar2(50) NULL,
  Contact_No number(9) UNIQUE,
  BusNo varchar2(10),
  Seat_No number NOT NULL
);

CodePudding user response：

Instead of using zerofill you can use LPAD when data is read:

CREATE TABLE Reservation_21BAI1750(

PNR number(9),

 Journey_Date Date NULL,

 No_of_Seats number(8),

 Address varchar2(50) NULL,

 Contact_No number(9) UNIQUE,

 BusNo varchar2(10),

 Seat_No number NOT NULL,
  
 CONSTRAINT CH_Reservation_No_of_Seats CHECK  (No_of_Seats < 50) 

 );

For example:

INSERT INTO Reservation_21BAI1750 (PNR, Journey_Date, No_of_Seats, Address, Contact_No, BusNo, Seat_No)
VALUES (1, TO_DATE('2022-01-01','YYYY-MM-DD'), 15, '123 Main St', 10001, 'A100', 1);


INSERT INTO Reservation_21BAI1750 (PNR, Journey_Date, No_of_Seats, Address, Contact_No, BusNo, Seat_No)
VALUES (2, TO_DATE('2022-01-03','YYYY-MM-DD'), 25, '789 Elm St', 10002, 'C300', 3);

SELECT LPAD(No_of_Seats, 8, '0') AS Padded_Seat_No 
FROM Reservation_21BAI1750;

and here is the SQLFiddle.