ORACLE10: Rename column that has keyword name

[ShadowLocke]

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?

[Dave44]

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).

[amitpatel66]

The column should have been created using double quotes.

Check this:

 
 
SQL> create table t("comment" varchar2(10));
 
Table created.
 
SQL> select "comment" from t;
 
no rows selected
 
SQL> insert into t values('1234');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select "comment" from t;
 
comment
----------
1234
 
SQL> alter table t rename column "comment" to abc;
 
Table altered.
 
SQL> select abc from t;
 
ABC
----------
1234
 
SQL> drop table t;
 
Table dropped.
 
SQL> 
 
 

[ShadowLocke]

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:

SQL> alter table t rename column "comment" to abc;

ftw

Thanks!

The column should have been created using double quotes.

Check this:

 
 
SQL> create table t("comment" varchar2(10));
 
Table created.
 
SQL> select "comment" from t;
 
no rows selected
 
SQL> insert into t values('1234');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select "comment" from t;
 
comment
----------
1234
 
SQL> alter table t rename column "comment" to abc;
 
Table altered.
 
SQL> select abc from t;
 
ABC
----------
1234
 
SQL> drop table t;
 
Table dropped.
 
SQL> 
 
 

[liza1]

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