-1

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.

4
  • 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
5

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

SQL>
1
  • 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
1

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".

0

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