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.