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

5 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