Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unable to Modify Column / Query

Posted on 2004-09-23
5
Medium Priority
?
3,940 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 800 total points
ID: 12137308

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

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 400 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 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