Solved

Unable to Modify Column / Query

Posted on 2004-09-23
5
3,937 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

685 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