5 Replies Latest reply on Apr 13, 2006 2:36 PM by APC

    ORA-00922: missing or invalid option.

      i am trying to create the table below, each time i do i get the error message ERROR at line 1:
      ORA-00922: missing or invalid option.

      this error message occurs on the bracket after create table case. what could the problem be as i have created other tables with no problems.

      create table case (case_no number (4) NOT NULL, Court_Name varchar2(20), Start_Date Date, End_Date Date ,Advice varchar2(100) sentence_length varchar2(10),
      region_no number (2), judge_id number (4), CO_no number (4),counsel_no number (4), Prosecutor_no number (4),
      CONSTRAINT case_pk primary key (case_no),
      CONSTRAINT judge_fk foreign key (judge_id) references judge (judge_id),
      CONSTRAINT Prosecutor_fk foreign key (Prosecutor_no) references Prosecution (Prosecutor_no),
      CONSTRAINT CriminalOffenece_fk foreign key (CO_no) references criminal_offence (CO_no),
      CONSTRAINT counsel_fk foreign key (counsel_no) references counsel (counsel_no),
      CONSTRAINT region_fk foreign key (region_no) references region (region_no))
        • 1. Re: ORA-00922: missing or invalid option.
          You have missed a , after Advice varchar2(100).

          check it.
          • 2. Re: ORA-00922: missing or invalid option.

            Case is a reserved world in oracle sql.
            Select another name for the table.

            Ott Karesz
            • 3. Re: ORA-00922: missing or invalid option.
              thanks yeah that was the problem,its created now
              • 4. Re: ORA-00922: missing or invalid option.
                But the table with name Case can be created, oracle doesn't raise any error for it .
                • 5. Re: ORA-00922: missing or invalid option.
                  The statement as pasted has a missing comma
                  ,Advice varchar2(100) sentence_length varchar2(10)
                  If we put the comma in we can build the table:
                  SQL> desc case
                  Name                                      Null?    Type
                  ----------------------------------------- -------- ----------------------------
                  CASE_NO                                   NOT NULL NUMBER(4)
                  COURT_NAME                                         VARCHAR2(20)
                  START_DATE                                         DATE
                  END_DATE                                           DATE
                  ADVICE                                             VARCHAR2(100)
                  SENTENCE_LENGTH                                    VARCHAR2(10)
                  REGION_NO                                          NUMBER(2)
                  JUDGE_ID                                           NUMBER(4)
                  CO_NO                                              NUMBER(4)
                  COUNSEL_NO                                         NUMBER(4)
                  PROSECUTOR_NO                                      NUMBER(4)

                  Yes CASE is a reserved word, but it's one of those Johnny-come-lately resereved words that are deprecated but not banned. TYPE is another. Banning those words would have broken too many systems had been built prior to the introduction of CASE and TYPE in Oracle 8.

                  Cheers, APC