Solved

Unable to Modify Column / Query

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

777 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