ORA-00904: invalid identifier


ORA-00904: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

Reference: http://docs.oracle.com/cd/B10500_01/server.920/a96525/e900.htm

ORA-00904 is a very simple issue. ORA-00904 may occur when we try to create or alter a table with invalid column name. It also may occur when we try to reference a non existing column in a select / insert / update / delete statement.

Examples which may lead to ORA-00904 are following:

SQL> CREATE TABLE TEST
  2  (
  3     ID      NUMBER,
  4     NAME    VARCHAR2(200),
  5     COMMENT VARCHAR2(4000)
  6  );
        COMMENT VARCHAR2(4000)
        *
ERROR at line 5:
ORA-00904: invalid identifier

==========================================================

SQL> select empid from scott.emp;
select empid from scott.emp
       *
ERROR at line 1:
ORA-00904: "EMPID": invalid identifier

==========================================================

SQL> update scott.emp set salary=1000 where empno = 3625;
update scott.emp set salary=1000 where empno = 3625
                     *
ERROR at line 1:
ORA-00904: "SALARY": invalid identifier

==========================================================

SQL> delete scott.emp where empid = 3625;
delete scott.emp where empid = 3625
                       *
ERROR at line 1:
ORA-00904: "EMPID": invalid identifier

==========================================================

SQL> insert into scott.emp (empno, empname, sal) 
  2  values(3625, 'Amit', 10000);
insert into scott.emp (empno, empname, sal)
                              *
ERROR at line 1:
ORA-00904: "EMPNAME": invalid identifier

==========================================================

SQL> select emp.ename from scott.emp e;
select emp.ename from scott.emp e
       *
ERROR at line 1:
ORA-00904: "EMP"."ENAME": invalid identifier

ORA-00904 can simply be avoided by using the valid column name in create or alter statement. Also for DML statements ORA-00904 can be avoided by making a valid reference to the column name or the alias.

A valid column name must follow following criteria
- The column name must begin with a letter.
- The column name can not be of more than 30 characters.
- The column name must be made up of alphanumeric characters
- The column name may contain following special characters: $, _, and #.
- If the column name uses any other characters, it must be enclosed in double quotation marks.
- The column name can not be a reserved word.


Oracle Documentation on SQL Reserved Words
http://docs.oracle.com/cd/E14072_01/server.112/e10592/ap_keywd.htm#g691972



Related Posts:
- ORA-00936 missing expression
- ORA-00911: invalid character
- ORA-01722: invalid number
- ORA-06550: line n, column n
- ORA-06502: PL/SQL: numeric or value errorstring

16 comments:

  1. Please help. I don't know how to fix this up. Getting an error msg : Contraint fk_ordid Foreign Key (ordid) references Shoporder )
    *
    Error at line 9:
    ORA-00907: missing right parenthesis


    CREATE TABLE Customer(
    custid number(4),
    cfirstname varchar2(30),
    csurname varchar2(30),
    billingaddress varchar2(30),
    cgender varchar2(1),
    CONSTRAINT PK_Customer Primary Key (custid));


    CREATE TABLE Salesperson(
    spid number(4),
    spfirstname varchar2(30),
    spsurname varchar2(30),
    spgender varchar2(1),
    CONSTRAINT PK_Salesperson Primary Key (spid));

    CREATE TABLE Shoporder (
    ordid number(4),
    deliveryaddress varchar2(30),
    CONSTRAINT PK_Shoporder Primary Key (ordid),
    Contraints fk_custid Foreign Key (custid) references Customer(custid),
    Contraints fk_spid Foreign Key (spid) references Salesperson(spid));

    ReplyDelete
    Replies
    1. CREATE TABLE Shoporder (
      ordid number(4),
      deliveryaddress varchar2(30),
      custid number(4),
      spid number(4),
      CONSTRAINT PK_Shoporder Primary Key (ordid),
      CONSTRAINT fk_custid Foreign Key (custid) references Customer(custid),
      CONSTRAINT fk_spid Foreign Key (spid) references Salesperson(spid));

      Delete
  2. Please Help!!!

    create table productinfotwo
    (
    productId number(10),
    CONSTRAINT primary_pk Primary Key(productId),
    productname varchar2(100),
    SUBCATEGORYID number(10),
    CONSTRAINT subcategory_fk Foreign Key(SUBCATEGORYID ) REFERENCES ProductSubCategory(SUBCATEGORYID ),
    COMPANYID varchar2(20),
    CONSTRAINT company_fk Foreign Key(COMPANYID ) References CompanyInfo(COMPANYID ),
    price float,
    quantity number(10),
    description varchar2(1000),
    );

    ReplyDelete
    Replies
    1. one extra comma at the end of
      description varchar2(1000),

      Delete
    2. it is a silly mistake you did developer. Don't send such fresher mstakes to experts

      Delete
  3. What a material of un-ambiguity and preserveness of valuable experience regarding unpredicted feelings.

    ReplyDelete
  4. I do accept as true with all the ideas you have introduced on your post.
    They're very convincing and can definitely work. Nonetheless, the posts are very quick for
    novices. May you please extend them a little from subsequent
    time? Thanks for the post.

    ReplyDelete
  5. Howdy! Would you mind if I share your blog with my
    twitter group? There's a lot of folks that I think would really enjoy your content.

    Please let me know. Cheers

    ReplyDelete
  6. Good article! We are linking to this great post on our website.
    Keep up the great writing.

    ReplyDelete
  7. Hi there! Do you use Twitter? I'd like to follow you
    if that would be okay. I'm definitely enjoying your blog and look forward to new updates.

    ReplyDelete
  8. I could not resist commenting. Exceptionally well written!

    ReplyDelete
  9. Thanks forr finally writing aЬout >"ORA-00904: invalid identifier" <Liked it!

    ReplyDelete
  10. CREATE TABLE "CUSTOMER"
    (
    "CUSTID" VARCHAR(10) NOT NULL ENABLE,
    "CUSTIC" VARCHAR(12),
    "CUSTFIRSTNAME" VARCHAR(50),
    "CUSTLASTNAME" VARCHAR(50),
    "CUSTPHONENUM" NUMBER(12),
    "CUSTADDRESS" VARCHAR(50),
    CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTID")
    USING INDEX ENABLE
    )

    CREATE TABLE "RESERVATION"
    (
    "RESERVATIONID" NUMBER(10) NOT NULL ENABLE,
    "CHECKIN" DATE,
    "CHECKOUT" DATE,
    "RESERVATIONDATE" DATE,
    "NUMOFPEOPLE" NUMBER(5),

    CONSTRAINT "RESERVATION_PK" PRIMARY KEY ("RESERVATIONID")
    USING INDEX ENABLE
    )

    ALTER TABLE "RESERVATION" ADD FOREIGN KEY ("CUSTID")
    REFERENCES "CUSTOMER" ("CUSTID") ENABLE

    I'm not really sure what's the mistake. It said the "CUSTID" was an invalid identifier but the customer table has been created successfully??? so how is the "CUSTID" an invalid identifier? Please help me.

    ReplyDelete
    Replies
    1. your reservation table does not have a "custid" column. So you can't add a foreign key to that (not existing) column

      Delete
  11. Hi ,
    Greetings from Application Plus Technologies !!
    We are providing training with certification for the below skill set ,
    *Oracle SQL
    * Advance SQL
    *PLSQL
    *Advance PLSQL
    * EBS Technical
    * Fusion technical
    If you are looking for training contact us
    WhatsApp No : 8108735227 / 7499992939
    Official Email : nida.k@applplus .com

    ReplyDelete