ORA-00904: : invalid identifier

ORA-00904: : invalid identifier error occurs when an invalid column name is used to refer a column in a table. The column name should follow the naming conventions. A valid column name must begin with a letter and can only contain alphanumeric characters as well as the special characters $, _, and #. The name of the column cannot be a reserved keyword. The name must be no more than 30 characters long.

If a column is referred to by an incorrect column name, the column cannot be found in the database table. This might be due to incorrect sql syntax or a failure to follow double quotation or naming standards. The column name could not be determined if an incorrect SQL Statement was used. The column could not be referred to if the double quote mark was missed from the column name. The column name should adhere to the naming conventions. If not, the error message ORA-00904: : invalid identifier will be displayed.



When the ORA-00904 error occurs

If an invalid column name is used to refer to a column in an Oracle database table in the SQL Statement, an error message will be displayed. The incorrect column name is the result of incorrect naming standards, missing double quotation marks, or incorrect SQL syntax. The issue will be resolved if a valid column name is followed by a valid SQL Statement.

CREATE TABLE EMP(
1id int, 
name VARCHAR2(100)
)
Error starting at line : 4 in command -
CREATE TABLE EMP(
1id number, 
name VARCHAR2(100)
)
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


Root Cause

The incorrect column name is the result of not following naming conventions, missing double quotation marks, or improper SQL syntax. The database does not recognize the incorrect column name as a column name. Oracle was unable to find the column in the database table. As a result, Oracle displays an error message indicating that an incorrect column name was given and could not be found in the database.



Solution A

The table column name should follow the oracle naming conventions. The naming conventions are as follows

  • The column name should starts with an alphabet.
  • The column name should not be an oracle reserved keyword.
  • The column name should not be more than 30 characters
  • The column name can only contain alphanumeric characters as well as the special characters $, _, and #.

Problem1

-- STARTS WITH NUMBER
CREATE TABLE EMP(
1id int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution1

CREATE TABLE EMP(
"1id" int, 
name VARCHAR2(100)
)
Table EMP created.

Problem 2

-- CONTAINS WITH INVALID SPECIAL CHAR
CREATE TABLE EMP(
!id int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution2

CREATE TABLE EMP(
"!id" int, 
name VARCHAR2(100)
)
Table EMP created.


Problem 3

--  CONTAINS INVALID RESERVED KEYWORDS
CREATE TABLE EMP(
SIZE int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution3

CREATE TABLE EMP(
"SIZE" int, 
name VARCHAR2(100)
)
Table EMP created.

Problem 4

--  CONTAINS MORE THAN 30 CHARACTER
CREATE TABLE EMP(
EMPLOYEE_ID_WITH_MANAGER_ID_AND_SENIOR_MANAGER_ID int, 
name VARCHAR2(100)
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution4

CREATE TABLE EMP(
id int, 
name VARCHAR2(100)
)
Table EMP created.


Solution B

If you add a comma in the last column, the error will be shown. The last column comma should be removed to resolve the error.

Problem

CREATE TABLE EMP(
id number, 
name VARCHAR2(100),
)

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution

CREATE TABLE EMP(
id number, 
name VARCHAR2(100)
)

Table EMP created.


Solution C

If the SQL Statement contains an incorrect syntax, oracle could not find the. column. Oracle will throw the error. The SQL Statement should use the correct and valid syntax.

Problem

insert into emp(id,name) values(1,'kim');

SQL Error: ORA-00904: "ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"

Solution

insert into emp("1id",name) values(1,'kim');


Solution D

If the column is created using number, special character or reserved keywords, the column could be referred using the double quotation enclosed. The error will be thrown if the double quotation is not used.

Problem

select 1id from emp;

select !id from emp;

select size from emp;

solution

select "1id" from emp;

select "!id" from emp;

select "size" from emp;



Leave a Reply