Oracle: Add Reserved Word as Column Name [closed]

Question

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.

Answer 1

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>

Answer 2

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