3 Replies Latest reply: Apr 28, 2014 3:19 PM by Adam1902 RSS

    "ORA-00904 invalid identifier", what's wrong?

    Adam1902

      Hey,

      I'm trying to create 4 very basic tables (via Script Editor) in Application Express 4.1.0.00.32 and keep getting this error and can't understand why. If anyone else can point out where I'm going wrong that'd be perfect!

       

      Here's the script:

      ----------

      DROP TABLE County CASCADE CONSTRAINTS;

      DROP TABLE City CASCADE CONSTRAINTS;

      DROP TABLE Offence CASCADE CONSTRAINTS;

      DROP TABLE Region CASCADE CONSTRAINTS;

       


      CREATE TABLE County(

          County_Name VARCHAR2(55) PRIMARY KEY,

          Location VARCHAR2(55),

          Details VARCHAR2(222));

       


      CREATE TABLE Offence(

          Offence_ID NUMBER(3) PRIMARY KEY,

          Offence_Desc VARCHAR2(333),

          Offence_Type VARCHAR2(111) NOT NULL,

          Date DATE NOT NULL,

          Time NUMBER(5),

          County_Name VARCHAR2(55) REFERENCES County(County_Name),

          City_Name VARCHAR2(55) REFERENCES City(City_Name),

          Region_Name VARCHAR2(55) REFERENCES Region(Region_Name));

       


      CREATE TABLE City(

          County_Name VARCHAR2(55) REFERENCES County(County_Name),

          City_Name VARCHAR2(55) PRIMARY KEY,

          PostCode VARCHAR(8) NOT NULL,

          House# NUMBER(3) NOT NULL,

          Street VARCHAR2(55),

          Town VARCHAR2(55),

          Building_Name VARCHAR2(55));

         

      CREATE TABLE Region(

          Region_Name VARCHAR2(55) PRIMARY KEY,

          City VARCHAR2(55) NOT NULL,

          Est_Population NUMBER(9) NOT NULL);

      -----------


      The error I'm getting in the summary when I run the script is:

      "60.00CREATE TABLE Offence( Offence_ID NUMBER(3) PRIMARY KEY, ORA-00904: : invalid identifier"

      So I'm assuming it's related to the "Offence_ID" line (intended as the primary key / identifier), but I can't see an issue.

       

      Any assistance greatly appreciated!

        • 1. Re: "ORA-00904 invalid identifier", what's wrong?
          RobbR

          DATE is an Oracle reserved word.

           

          Oracle Reserved Words and Keywords

           

          SQL> create table test_tbl

            2  (

            3  date date default sysdate

            4  );

          date date default sysdate

          *

          ERROR at line 3:

          ORA-00904: : invalid identifier

           

           

           

           

          SQL> create table test_tbl

            2  (

            3  "DATE" date default sysdate

            4  );

           

           

          Table created.

           

          I would recommend not naming your column DATE, but use something like Offence_date instead.

          • 2. Re: "ORA-00904 invalid identifier", what's wrong?
            fac586

            Adam1902 wrote:

             

            Hey,

            I'm trying to create 4 very basic tables (via Script Editor) in Application Express 4.1.0.00.32 and keep getting this error and can't understand why. If anyone else can point out where I'm going wrong that'd be perfect!

             

            CREATE TABLE Offence(

                Offence_ID NUMBER(3) PRIMARY KEY,

                Offence_Desc VARCHAR2(333),

                Offence_Type VARCHAR2(111) NOT NULL,

                Date DATE NOT NULL,

                Time NUMBER(5),

                County_Name VARCHAR2(55) REFERENCES County(County_Name),

                City_Name VARCHAR2(55) REFERENCES City(City_Name),

                Region_Name VARCHAR2(55) REFERENCES Region(Region_Name));


            The error I'm getting in the summary when I run the script is:

            "6 0.00 CREATE TABLE Offence( Offence_ID NUMBER(3) PRIMARY KEY, ORA-00904: : invalid identifier"

            So I'm assuming it's related to the "Offence_ID" line (intended as the primary key / identifier), but I can't see an issue.

            DATE and TIME are Oracle keywords and cannot be used as identifiers.

             

            Why are you trying to store an instant ("Time") as a number? The time of the offence should be included in the DATE column (which obviously should be called something other than "Date").

            • 3. Re: "ORA-00904 invalid identifier", what's wrong?
              Adam1902

              Yeah that was the issue, thanks for the help and thanks for that link too btw that's going to be very useful.

              Problem is I'm now getting ORA-00942: table or view does not exist in relation to the same table.

               

              EDIT: Saw your reply, fac586 after posting this. I've changed the 'Date' entry to something else and removed time. Forgot that time was part of the Oracle date format, thanks for your time. Pun intended