Unable to Modify Column / Query

I use a program that made an SQL script that created several tables with the field 999Id, but everytime I try to run a query using this name it thinks 999Id is a number.  I've tried everything and it breaks all my SQL statements.  Is there anyway I can force Oracle9i to rename this field without breaking it.  I've tried all these statements none of which worked.

alter table een rename column "999id" to nine99id
                              *
ERROR at line 1:
ORA-00904: "999id": invalid identifier


alter table een rename column 999id to nine99id
                              *
ERROR at line 1:
ORA-00904: : invalid identifier


alter table een rename column '999id' to nine99id
                              *
ERROR at line 1:
ORA-00904: : invalid identifier


alter table een rename column '999id' to 'nine99id'
                              *
ERROR at line 1:
ORA-00904: : invalid identifier


alter table een rename column "999id" to "nine99id"
                              *
ERROR at line 1:
ORA-00904: : invalid identifier


alter table een rename column 999id to nine99id
                              *
ERROR at line 1:
ORA-00904: : invalid identifier
gdlp2004Asked:
Who is Participating?
 
geotigerConnect With a Mentor Commented:

The column name might be "999ID" in your table.
0
 
geotigerCommented:
Describe the table.  Make sure that you are using exact  the same column name as in the describe.

SQL> create table tst1 ("999id" number);

Table created.

Elapsed: 00:00:00.06
SQL> desc tst1
 Name                            Null?    Type
 ------------------------------- -------- ----
 999id                                    NUMBER

SQL> alter table tst1 rename column "999id" to nine99id;

Table altered.

Elapsed: 00:00:00.56

0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Or the column name might be "999Id".  If you use SQL*Plus only to create tables, you will usually have all table and column names in upper case, regardless of which case you used when you typed the "create table..." command.  If you use other tools though (especially some Microsoft tools) they may create scripts that put double quotes around table and column names AND use mixed-case names.  If you run these scripts in SQL*Plus, the tables and/or columns may have mixed-case names.  If that is true, you will have to use double quotes around the table and column names when you refer to them, and you must match the case of the table and column names exactly.

One work-around is to create a view with all upper-case table and columns names for each table that is in mixed case.  (The "create view" statement will have to use mixed-case with double quotes in the "as select" part of the statement.) Then you can just use the view in SQL*Plus (or other Oracle tools) and type table and column names in either upper case or lower case and not worry about matching case for table and column names.  Of course, varchar2 data columns may still  contain mixed-case values, and you will have to match these exactly, if they do.
0
 
annamalai77Commented:
hi

i think there might be some invisible characters along with the field name. that could be the reason why it is not allowing to rename the field name.

regards
annamalai
0
 
gdlp2004Author Commented:
Thanks!!! Previously, when I had to rename columns, the SQL did not seem case sensitive, but in this case it was "999ID" not 999id or "999id".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.