CODEWITHSUNDEEP
sqloracle-database
Jessica Wallace
Jessica Wallace

Reputation: 23

SQL Error: ORA-00907: missing right parenthesis - CANNOT FIND ERROR

Having started at the same statements over and over again, I still cannot find the missing right parenthesis. It's appeared when running both of these statements and in order to run the rest of my tables I need to be able to locate the right parenthesis.

I've selected the parenthesis used in the statements and all seemed to match up to the right ones. Can anyone suggest anything? 

CREATE TABLE booking (
bookingNo       NUMBER(8) PRIMARY KEY,
customerNo      NUMBER(8) NOT NULL
        REFERENCES customer(customerNo),
registrationNo      VARCHAR2(10) NOT NULL
        REFERENCES vehicle(registrationNo), 
dateOfBooking       DATE NOT NULL
        DEFAULT SYSDATE, 
pickupStreetAddressLine VARCHAR2(30) NOT NULL, 
pickupTown      VARCHAR2(30) NOT NULL, 
pickupPostcode      VARCHAR2(10) NOT NULL, 
startTime       NUMBER(4,2) NOT NULL, 
startDate       DATE NOT NULL
        DEFAULT SYSDATE,
endTime         NUMBER(4,2) NOT NULL, 
endDate         DATE NOT NULL 
        DEFAULTSYSDATE, 
noOfPassengers      NUMBER(3) NOT NULL
        CONSTRAINT CHECK(noOfPassengers > 0 AND noOfPassengers <= 73) 
price           NUMBER(8,2) NOT NULL
); 

CREATE TABLE employees (
nationalInsuranceNo VARCHAR2(10) PRIMARY KEY, 
fullName        VARCHAR2(50) NOT NULL, 
streetAddress       VARCHAR2(30) NOT NULL, 
town            VARCHAR2(30), 
postcode        VARCHAR2(10) NOT NULL, 
homeNo          NUMBER(11)
dateOfBirth     DATE NOT NULL, 
gender          VARCHAR2(8) NOT NULL
        CONSTRAINT CHECK(gender="Male" OR gender="Female"), 
jobDescription      VARCHAR2(30) NOT NULL, 
currentSalary       NUMBER(6) NOT NULL
        CONSTRAINT CHECK(currentSalary>0)
);

Upvotes: 1

Views: 279

Answers (3)

Moon
Moon

Reputation: 1151

Try using a standard indentation scheme to visually verify your formatting.

Here I moved each significant paren to a newline and indented each piece separately.

I put comments next to a few items that seem wrong:

CREATE TABLE 
    booking 
        (
            bookingNo               NUMBER(8) PRIMARY KEY,
            customerNo              NUMBER(8) NOT NULL 
                REFERENCES          customer(customerNo),
            registrationNo          VARCHAR2(10) NOT NULL
                REFERENCES          vehicle(registrationNo), 
            dateOfBooking           DATE NOT NULL
                DEFAULT SYSDATE, 
            pickupStreetAddressLine VARCHAR2(30) NOT NULL, 
            pickupTown              VARCHAR2(30) NOT NULL, 
            pickupPostcode          VARCHAR2(10) NOT NULL, 
            startTime               NUMBER(4,2) NOT NULL, 
            startDate               DATE NOT NULL
                DEFAULT SYSDATE,
            endTime                 NUMBER(4,2) NOT NULL, 
            endDate                 DATE NOT NULL 
                DEFAULT SYSDATE,    -- THERE WAS A MISSING SPACE HERE *********************
            noOfPassengers      NUMBER(3) NOT NULL, -- MISSING COMMA ********
                CONSTRAINT Check_noOfPassengers CHECK -- ADD A CONSTRAINT NAME *********
                    (
                        noOfPassengers > 0 
                        AND noOfPassengers <= 73
                    ), -- THERE WAS A MISSING COMMA HERE ******************* 
            price                   NUMBER(8,2) NOT NULL
        ); 

CREATE TABLE 
    employees 
        (
            nationalInsuranceNo     VARCHAR2(10) PRIMARY KEY, 
            fullName                VARCHAR2(50) NOT NULL, 
            streetAddress           VARCHAR2(30) NOT NULL, 
            town                    VARCHAR2(30), 
            postcode                VARCHAR2(10) NOT NULL, 
            homeNo                  NUMBER(11), -- THERE WAS A MISSING COMMA HERE ***************
            dateOfBirth             DATE NOT NULL, 
            gender                  VARCHAR2(8) NOT NULL, -- MISSING COMMA *****
                CONSTRAINT Check_gender CHECK -- ADD A CONSTRAINT NAME *********
                    (
                        gender="Male" 
                        OR gender="Female"
                    ), 
            jobDescription      VARCHAR2(30) NOT NULL, 
            currentSalary       NUMBER(6) NOT NULL, -- MISSING COMMA
                CONSTRAINT Check_currentSalary CHECK -- ADD A CONSTRAINT NAME *********
                    (
                        currentSalary>0
                    )
        );

Upvotes: 2

Mick Ashton
Mick Ashton

Reputation: 354

Change this DEFAULTSYSDATE to this DEFAULT SYSDATE on the 17th line Also, add the column name before each CHECK and after the CONSTRAINT words. And add a comma after the first CONSTRAINT statement.

Hope it helps :)

Upvotes: 0

DenverJT
DenverJT

Reputation: 125

I don't have Oracle installed to test, but is there a missing comma for the column noOfPassengers above? More generally, it may not be a missing right parens, but a parens that is out of place because of another syntax error. Hope that helps.

Upvotes: 0

Related Questions