I'm trying to sync a table on test system to one in production. In production, as bad as it may be, I believe they used the reserved word SITE as a column name.

alter table MY_TABLENAME
   modify ("SITE" varchar2(50),
           LOCATION_ID varchar2(50)

Above gives error:

ORA-00904: "SITE": invalid identifier

Same error without the quotes or with brackets.

  • 1
    Not sure if site is a reserved word, but what are you trying to do here? Add a column? – Andrew Brennan Aug 31 '16 at 10:10
  • Andrew - Since I already answered, not sure why you are asking. – NealWalters Aug 31 '16 at 21:29
  • 1
    @NealWalters Your question is stated as if the column aready exists in the table and you want to modify it. ALTER TABLE .. ADD site .. would fail in such a case. I suggest you edit the question to clarify. We are not mind readers. – ypercubeᵀᴹ Aug 31 '16 at 23:06
  • And anything that clarifies a question should be there, not in answers or comments. – ypercubeᵀᴹ Aug 31 '16 at 23:12

Oracle SQL Reserved Words

If you really want to use a reserved word, you can use it between double quotes, but that is where life becomes complicated.

SQL> alter table t1 add (number number);
alter table t1 add (number number)
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> alter table t1 add ("NUMBER" number);

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 NUMBER                                             NUMBER

SQL> select number from t1;
select number from t1
ERROR at line 1:
ORA-00936: missing expression

SQL> select NUMBER from t1;
select NUMBER from t1
ERROR at line 1:
ORA-00936: missing expression

SQL> select "NUMBER" from t1;

no rows selected

  • I already answered, the question maybe slightly misworded, but I thought the error indicated a reserve word. I was trying to understand what "ORA-00904: "SITE": invalid identifier" meant. – NealWalters Aug 31 '16 at 21:27

Ok, I'm a Microsoft SQL guy, not an Oracle guy. I used the PL/SQL graphical interface, and clicked "view SQL".

alter table MY_TABLENAME TAB add site varchar2(50);
alter table MY_TABLENAME TAB add location_id varchar2(50);

Looks like I should have had "add" instead of "mod".

I misunderstood the error "ORA-00904: "SITE": invalid identifier" to mean that I was using a reserved word because it kind of looked like one, even though my actual problem was simply to change "modify" to "add".


Not the answer you're looking for? Browse other questions tagged or ask your own question.