• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

ORA-00904: invalid identifier

by admin

Running DDL or DML statements. Below example shows a sample DDL run over SQL Developer Web and SQL Developer.

CREATE TABLE sample_contract(
Year DATE,
Team VARCHAR2(12),
Conference VARCHAR2(10),
Division VARCHAR2(8),
City VARCHAR2(13),
Price NUMBER,
Size NUMBER,
Pct NUMBER)
/
Error report -
ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

The script uses SIZE as a column name which is an Oracle SQL Reserved Word. Rename the “SIZE” column name to a different name and rerun the script. Reserved words as part of object name or column name has restrictions. If it is being used, should be used with double-quotes. For example:

CREATE TABLE sample_contract(
Year DATE,
Team VARCHAR2(12),
Conference VARCHAR2(10),
Division VARCHAR2(8),
City VARCHAR2(13),
Price NUMBER,
"Size" NUMBER,
Pct NUMBER)
/

Further, while selecting data from column which has name as one of the reserved word a double quote is needed. Apart from this the name of the column should be in exact case as it was used while creating table. In above case this is a valid SELECT statement.

Select "Size" from sample_contract;

Whereas following will fail:

Select "SIZE" from sample_contract;
Select "size" from sample_contract;
Select Size from sample_contract;

Other Reasons for ora-00904

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.

ORA-00904 is a very simple issue. ORA-00904 may occur when we try to create or alter a table with an 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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)
  2. How to determine which user is using what rollback segment?
  3. Oracle GoldenGate: Replicat Sample Parameter File
  4. Oracle ASM 12c – New Features with examples
  5. Oracle Database – Configuring Secure Application Roles
  6. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
  7. How to Clear a Redo Log file in Oracle Database
  8. MAX_STRING_SIZE Parameter in Oracle Database
  9. Oracle RMAN interview questions
  10. Oracle RMAN Pluggable Database Point in Time Recovery

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright