SQL Error ORA-00922 missing or invalid option

0 votes
CREATE TABLE chartered flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)

Should I not use CHAR data type?

This is the error I am getting:

Error at Command Line:1 Column:23
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Can someone please help me with this?

Sep 13 in Database by Kithuzzz
• 11,640 points
48 views

1 answer to this question.

0 votes

The lack of an underscore between "chartered" and "flight" in the table name appears to be the cause of the problem you're seeing. I'm assuming you want something along this lines, with the table's name being chartered flight.

CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL
, destination CHAR (3) NOT NULL)

Generally, there is no benefit to declaring a column as CHAR(3) rather than VARCHAR2(3). Declaring a column as CHAR(3) doesn't force there to be three characters of (useful) data. It just tells Oracle to space-pad data with fewer than three characters to three characters. That is unlikely to be helpful if someone inadvertently enters an incorrect code. Potentially, you could declare the column as VARCHAR2(3) and then add a CHECK constraint that LENGTH(takeoff_at) = 3. 

CREATE TABLE chartered_flight(flight_no NUMBER(4) PRIMARY KEY
, customer_id NUMBER(6) REFERENCES customer(customer_id)
, aircraft_no NUMBER(4) REFERENCES aircraft(aircraft_no)
, flight_type VARCHAR2 (12)
, flight_date DATE NOT NULL
, flight_time INTERVAL DAY TO SECOND NOT NULL
, takeoff_at CHAR (3) NOT NULL CHECK( length( takeoff_at ) = 3 )
, destination CHAR (3) NOT NULL CHECK( length( destination ) = 3 )
)

You should should create a distinct collection of valid airport codes and establish foreign key restrictions between the chartered flight table and this new airport code table because takeoff at and destination are both airport codes. This makes it much simpler in the future if an airport code changes and guarantees that only legitimate airport codes are added.

I hope this helps you.

answered Sep 15 by narikkadan
• 19,680 points

Related Questions In Database

0 votes
1 answer

SQL error "ORA-01722: invalid number"

When an effort is made to convert ...READ MORE

answered Feb 15 in Database by Neha
• 8,920 points
821 views
0 votes
0 answers

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I experimented with the following code in ...READ MORE

Aug 22 in Database by Kithuzzz
• 11,640 points
201 views
0 votes
0 answers

SQL Error: ORA-00942 table or view does not exist

I'm using SQL Developer, and after creating ...READ MORE

Sep 4 in Database by Kithuzzz
• 11,640 points
29 views
0 votes
0 answers

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Using OJDBC 6, I created some Java ...READ MORE

Aug 15 in Database by Kithuzzz
• 11,640 points
237 views
0 votes
1 answer

Is there any boolean type in Oracle database?

Nope. I don't think there is one But ...READ MORE

answered Oct 10, 2018 in Database by Neha
• 6,300 points
325 views
0 votes
0 answers

ORA-00904: invalid identifier

I am getting this error while doing ...READ MORE

May 20 in Database by Kichu
• 19,040 points
169 views
0 votes
0 answers

How do I list all the columns in a table?

For the various popular database systems, how ...READ MORE

Aug 9 in Database by Kithuzzz
• 11,640 points
23 views
0 votes
0 answers

Creating a new database and new connection in Oracle SQL Developer

I've introduced SQL Developer to my framework. ...READ MORE

Aug 12 in Database by Kithuzzz
• 11,640 points
20 views
0 votes
1 answer

SQL Error: ORA-01861: literal does not match format string 01861

Try replacing the string literal for date ...READ MORE

answered Sep 13 in Database by narikkadan
• 19,680 points
69 views
0 votes
1 answer

SQL Server Error : String or binary data would be truncated

You're trying to write more data than ...READ MORE

answered Sep 15 in Database by narikkadan
• 19,680 points
72 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP