By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,288 Members | 3,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,288 IT Pros & Developers. It's quick & easy.

ORACLE10: Rename column that has keyword name

100+
P: 115
Hi,

I have a table that has a column with name "comment" in it. I think that "comment" is an oracle keyword so i would like to rename this column.

when i use the syntax "alter table tablename rename column comment to somethingbetter" i get error invalid identifier. I also tried dropping the column altogether and creating a new one but i get the same error on drop.

I tried creating a new table for an example to post here but i get the same error on "comment".

I don't know how this column got created in the first place, but after the trouble im having here I really think this column needs to be renamed to avoid future problems.

Any ideas?
Jun 23 '08 #1
Share this Question
Share on Google+
4 Replies


100+
P: 153
ya thats a tough one, i cant create a table with that column name to try and test some theories; keeps failing with invalid identifier.

Could you try creating a new table as select <columns> from old_table and alias the comment column with something else? Then use dbms_redefinition to swap all the other objects on the old table to the new one (like indexes and such).
Jun 24 '08 #2

amitpatel66
Expert 100+
P: 2,367
The column should have been created using double quotes.

Check this:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SQL> create table t("comment" varchar2(10));
  4.  
  5. Table created.
  6.  
  7. SQL> select "comment" from t;
  8.  
  9. no rows selected
  10.  
  11. SQL> insert into t values('1234');
  12.  
  13. 1 row created.
  14.  
  15. SQL> commit;
  16.  
  17. Commit complete.
  18.  
  19. SQL> select "comment" from t;
  20.  
  21. comment
  22. ----------
  23. 1234
  24.  
  25. SQL> alter table t rename column "comment" to abc;
  26.  
  27. Table altered.
  28.  
  29. SQL> select abc from t;
  30.  
  31. ABC
  32. ----------
  33. 1234
  34.  
  35. SQL> drop table t;
  36.  
  37. Table dropped.
  38.  
  39. SQL> 
  40.  
  41.  
Jun 24 '08 #3

100+
P: 115
Sorry for going quite. What I had ended up doing as i was in a rush was creating a new table and inserting the data into it then just dropping the table.

But, I had a co-worker that just today made the same mistake. (created a column named comment) We tried the syntax given below and it worked perfect.

that is:
Expand|Select|Wrap|Line Numbers
  1. SQL> alter table t rename column "comment" to abc;
ftw

Thanks!

The column should have been created using double quotes.

Check this:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SQL> create table t("comment" varchar2(10));
  4.  
  5. Table created.
  6.  
  7. SQL> select "comment" from t;
  8.  
  9. no rows selected
  10.  
  11. SQL> insert into t values('1234');
  12.  
  13. 1 row created.
  14.  
  15. SQL> commit;
  16.  
  17. Commit complete.
  18.  
  19. SQL> select "comment" from t;
  20.  
  21. comment
  22. ----------
  23. 1234
  24.  
  25. SQL> alter table t rename column "comment" to abc;
  26.  
  27. Table altered.
  28.  
  29. SQL> select abc from t;
  30.  
  31. ABC
  32. ----------
  33. 1234
  34.  
  35. SQL> drop table t;
  36.  
  37. Table dropped.
  38.  
  39. SQL> 
  40.  
  41.  
Jul 28 '08 #4

P: 4
First you add a new Coloum.To add a column named "comment 1" in the table by using Alter command.

Syntax: ALTER TABLE table_name
ADD column_name datatype

and then drop coment coloum in your table by using drop command.

Syntax: ALTER TABLE table_name
DROP COLUMN column_name

I think this will help you. To know more about Oracle so there are number of nice video Tutorils on

http://codervods.com/

Thanks
Aug 4 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.