• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

ORA-00922: missing or invalid option - Oracle Error - Relational DB

I keep getting this error: ORA-00922: missing or invalid option

I looked at the snippet numerous times, yet couldn't find the glitch. Can you help me please

P.S. Sorry, the snippet in here doesn't look nested. You can see it nested in TOAD or SQL Management Studio
CREATE TABLE Staff
(
        staffID                 NUMBER(7)    				   , CONSTRAINT staff_staffid_pk       					   PRIMARY KEY(staffID)                                                 ,
        firstName               VARCHAR2(50)       				 CONSTRAINT staff_firstname_nn	          			   NOT NULL                                                             ,
        lastName                VARCHAR2(50)        			 CONSTRAINT staff_lastname_nn	           			   NOT NULL                                                             ,
        email                   VARCHAR2(50)                	 													   	   	   																,
        address1                VARCHAR2(100)    			   	 CONSTRAINT staff_address1_nn	           			   NOT NULL																,
        address2                VARCHAR2(100)                                                                   	       																	,
        cityID                  NUMBER(7))                       CONSTRAINT staff_cityid_nn	                           NOT NULL																,
										                         CONSTRAINT staff_cityid_fk                            FOREIGN KEY(cityID) REFERENCES City(cityID)							,
        provinceID              NUMBER(7)                        CONSTRAINT staff_provinceid_nn	                       NOT NULL				 												,
										                         CONSTRAINT staff_provinceid_fk                        FOREIGN KEY(provinceID) REFERENCES Province(provinceID)           	,
        postalCode              VARCHAR2(50)                     CONSTRAINT staff_postalcode_nn	                       NOT NULL                 											,
        countryID               NUMBER(7)                        CONSTRAINT staff_countryid_nn	                       NOT NULL																,
										                         CONSTRAINT staff_countryid_fk                         FOREIGN KEY(countryID) REFERENCES Country(countryID)					,
        gender                  CHAR(1)                          CONSTRAINT stuff_gender_ck                            CHECK(gender IN ('M', 'F'))		 									,
        jobPositionID           NUMBER(7)                        CONSTRAINT staff_jobpositionid_nn	                   NOT NULL		   		 												,
										                         CONSTRAINT staff_jobpositionid_fk                     FOREIGN KEY(jobPositionID) REFERENCES JobPosition(jobPositionID)		,
		jobStatus		        VARCHAR2(50)                     CONSTRAINT staff_jobstatus_nn	                       NOT NULL					  			 								,
		emergencyAttender       CHAR(1)							 CONSTRAINT staff_emergencyattender_nn	               NOT NULL																,
																 CONSTRAINT staff_emergencyattender_ck                 CHECK(emergencyAttender IN ('Y', 'N'))
);

Open in new window

0
F-J-K
Asked:
F-J-K
  • 4
  • 3
3 Solutions
 
tigin44Commented:
a comma left in your code... try this
CREATE TABLE Staff
(
        staffID                 NUMBER(7)         CONSTRAINT staff_staffid_pk       PRIMARY KEY(staffID)                                                 ,
        firstName               VARCHAR2(50)      CONSTRAINT staff_firstname_nn     NOT NULL                                                             ,
        lastName                VARCHAR2(50)      CONSTRAINT staff_lastname_nn      NOT NULL                                                             ,
        email                   VARCHAR2(50) ,
        address1                VARCHAR2(100)     CONSTRAINT staff_address1_nn      NOT NULL,
        address2                VARCHAR2(100),
        cityID                  NUMBER(7))        CONSTRAINT staff_cityid_nn        NOT NULL,
                                                 CONSTRAINT staff_cityid_fk         FOREIGN KEY(cityID) REFERENCES City(cityID),
        provinceID              NUMBER(7)        CONSTRAINT staff_provinceid_nn                        NOT NULL ,
                                                                                                         CONSTRAINT staff_provinceid_fk                        FOREIGN KEY(provinceID) REFERENCES Province(provinceID)                  ,
        postalCode              VARCHAR2(50)     CONSTRAINT staff_postalcode_nn                        NOT NULL                                                                                                         ,
        countryID               NUMBER(7)        CONSTRAINT staff_countryid_nn                         NOT NULL                                                                                                                         ,
                                                                                                         CONSTRAINT staff_countryid_fk                         FOREIGN KEY(countryID) REFERENCES Country(countryID)                                     ,
        gender                  CHAR(1)          CONSTRAINT stuff_gender_ck                            CHECK(gender IN ('M', 'F'))                                                                                      ,
        jobPositionID           NUMBER(7)        CONSTRAINT staff_jobpositionid_nn                         NOT NULL                                                                                                                             ,
                                                                                                         CONSTRAINT staff_jobpositionid_fk                     FOREIGN KEY(jobPositionID) REFERENCES JobPosition(jobPositionID)         ,
        jobStatus               VARCHAR2(50)                     CONSTRAINT staff_jobstatus_nn                         NOT NULL                                                                                                                         ,
        emergencyAttender       CHAR(1)                                                  CONSTRAINT staff_emergencyattender_nn                 NOT NULL                                                                                                                         ,
                                                                                                                                 CONSTRAINT staff_emergencyattender_ck                 CHECK(emergencyAttender IN ('Y', 'N'))
);

Open in new window

0
 
F-J-KAuthor Commented:
I'm sure commas are well put. By the way, i just got the glitch, i had extra parenthesis.
cityID                  NUMBER(7)) 

Open in new window

0
 
F-J-KAuthor Commented:
Before i close this question, do you mind helping me finding the mistake in here?

Error is: ORA-00911: invalid character

I really don't know where i went wrong
CREATE TABLE RoomAssociates
(
	roomAssociatesID   	   NUMBER(7) 							 , CONSTRAINT roomasso_roomassoid_pk       	   		   PRIMARY KEY(roomAssociatesID)							  				,											   
	roomID				   NUMBER(7)	  						   CONSTRAINT roomasso_roomid_nn	           	   	   NOT NULL																	,
				 		   										   CONSTRAINT roomasso_roomid_fk             	  	   FOREIGN KEY(roomID) REFERENCES Room(roomID) ON DELETE CASCADE			,
	roomFacilityID 		   NUMBER(7)     						   CONSTRAINT roomasso_roomfid_nn	      	   		   NOT NULL			   			  			   	  		 					,
				 		   										   CONSTRAINT roomasso_roomfid _fk        	   		   FOREIGN KEY(roomFacilityID) REFERENCES RoomFacility(roomFacilityID)		,
	enSuitID	 		   NUMBER(7)	   						   CONSTRAINT roomasso_ensuitid_nn	           		   NOT NULL					   			  									,
				 		   										   CONSTRAINT roomasso_ensuitid_fk               	   FOREIGN KEY(enSuitID) REFERENCES EnSuit(enSuitID)
);

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
tigin44Commented:
staffID                 NUMBER(7)                                  , CONSTRAINT staff_staffid_pk  

line has an extra parenthesis  before constraint definition... and as you find out an extra parenthesis exist too
0
 
F-J-KAuthor Commented:
This is not an extra paran because "CONSTRAINT roomasso_roomassoid_pk   PRIMARY KEY(roomAssociatesID)" is considered an an independent (table level) statement. I took off the parenthesis you referred, i got another error says parenthesis is missed.
 , CONSTRAINT roomasso_roomassoid_pk       	   		 PRIMARY KEY(roomAssociatesID)							  				,

Open in new window

0
 
tigin44Commented:
sorry for my mistake... it should be like

line has an extra COMMA  before constraint definition... and as you find out an extra parenthesis exist too
0
 
F-J-KAuthor Commented:
The comma seems as if it was a mistake, but it isn't. Either way, i solved it and thanks for giving me some of your time
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now