Solved

Unable to Modify Column / Query

Posted on 2004-09-23
5
3,930 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:gdlp2004
5 Comments
 
LVL 12

Expert Comment

by:geotiger
ID: 12137297
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
 
LVL 12

Accepted Solution

by:
geotiger earned 200 total points
ID: 12137308

The column name might be "999ID" in your table.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 12139228
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
 
LVL 8

Expert Comment

by:annamalai77
ID: 12139996
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
 

Author Comment

by:gdlp2004
ID: 12142233
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now