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_redefiniti on 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:

[code=oracle]


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>

[/code]

[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:

[code=oracle]


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>

[/code]

[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