Q
Problem solve Get help with specific problems with your technologies, process and projects.

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

Continued from part 1.

  1. 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.

  2. 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 between
        cus_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)
);

Continued in part 3.

This was last published in November 2003

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close