Tracking down SQL errors, part 2
I'm simply trying to create a table and I get this error saying ORA-00922. What's wrong with my code and is there a site where I can look at all ORA- errors?
CREATE TABLE customer ( cus_id NUMBER (6), cus_address VARCHAR2 (30) CONSTRAINT customer_cus_address_nn NOT NULL, cus_phone_Number VARCHAR2 (12) CONSTRAINT customer_cus_phone_number_nn NOT NULL, cus_lname VARCHAR2 (15) CONSTRAINT customer_cus_lname_nn NOT NULL, cus_f_name VARCHAR2 (15) customer_cus_fname, CONSTRAINT customer_cus_id_pk PRIMARY KEY (cus_id) ); (cus_id NUMBER(6), * ERROR at line 2: ORA-00922: missing or invalid option
- Narrow down the area where the problem might occur.
Comment out about half of the SQL statement. If the problem remains, comment out half of the remainder. If the problem disappears, un-comment half of what you previously commented out. Repeat until you've narrowed down the range as much as possible. For example, the offending statement has six clauses, so let's remove three of them at first. (Notice I did not remove the last one. Why?)CREATE TABLE customer ( cus_id NUMBER (6), cus_address VARCHAR2 (30) CONSTRAINT customer_cus_address_nn NOT NULL, -- cus_phone_Number VARCHAR2 (12) -- CONSTRAINT customer_cus_phone_number_nn NOT NULL, -- cus_lname VARCHAR2 (15) -- CONSTRAINT customer_cus_lname_nn NOT NULL, -- cus_f_name VARCHAR2 (15) customer_cus_fname, CONSTRAINT customer_cus_id_pk PRIMARY KEY(cus_id) ); Table created.
This indicates that the error probably occurred in the section we just commented out. If we restore the sections defining cus_lname and cus_f_name, the error re-appears. Then if we comment out just the line defining cus_f_name the error goes away again. This tells us that the error is on that line. You might even repeat this process until you're removing fractions of a line. For example:
cus_f_name VARCHAR2 (15) /* customer_cus_fname */ ,
does not produce an error, so the problem must be with that one word.
- Research the specific task being attempted.
Once you've got the problem narrowed down, it's much easier to look up specific items in the documentation, compare the offending segment against similar ones that work (in this example, what's different betweencus_f_name VARCHAR2 (15) customer_cus_fname,
which does not work and
cus_address VARCHAR2 (30) CONSTRAINT customer_cus_address_nn NOT NULL,
which does?), or devise a test case.
By now, you've probably found the error. It looks like you made a pass through the code with your text editor to generate constraint names, and then went back and defined the constraints, but you forgot about the last column (perhaps because you don't want a constraint on it).
Here's one thing you might have intended that does work:
CREATE TABLE customer ( cus_id NUMBER (6), cus_address VARCHAR2 (30) CONSTRAINT customer_cus_address_nn NOT NULL, cus_phone_Number VARCHAR2 (12) CONSTRAINT customer_cus_phone_number_nn NOT NULL, cus_lname VARCHAR2 (15) CONSTRAINT customer_cus_lname_nn NOT NULL, cus_f_name VARCHAR2 (15) CONSTRAINT customer_cus_fname CHECK (cus_f_name = UPPER (cus_f_name)), CONSTRAINT customer_cus_id_pk PRIMARY KEY (cus_id) );
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments