SOLVED

ORA-00904: invalid identifier

Asked by OracleDba••11 views•oracle

#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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.

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.
OracleDba•

Post Your Solution