Renaming a Column

[Randall Woodman]

Sorry for the cross post but I'm desperate for an answer.

Is there a way to rename a column in an existing table? For example,
suppose I had a table named FOO.

CREATE TABLE FOO (
FOOWHO VARCHAR2 (12) NOT NULL,
BARNONE VARCHAR2 (18) NOT NULL,
FOOBAR VARCHAR2 (8) NOT NULL,
)

Now suppose I want to rename the column FOOBAR to FUBAR. I know I could do
the following
1. Add a new column called FUBAR
2. update the column FUBAR with the data in FOOBAR
3. Drop the column FOOBAR

However, I was thinking that there was a way to do this using a single
ALTER statement. For example,
alter table <table_name> rename column <column_name> new_column_name
However, that doesn't work.

I'm running Oracle 8.1.6

Thanks for your help.

-=} Randall {=-

[Daniel A. Morgan]

Randall Woodman wrote:

In 8i no. But I'd hardly get desparate. There is no need to rename a column.
When you select from the column, in a view or SQL statement just give it an
alias.

SELECT field1 NEWNAME
FROM mytable;

retrieves the values in field1 and renames the column to NEWNAME.

Alternatively ... drop the table and recreate it.

Daniel A. Morgan

[Benjamin.Altman]

Here's what the FAQ says (http://www.orafaq.com/faqsql.htm#RENAMECOL):

Can one rename a column in a table?
No, this is listed as Enhancement Request 163519. Workarounds:
1. rename t1 to t1_base;
create view t1 <column list with new name> as select * from t1_base;

2. create table t2 <column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;

[Karen Abgarian]

In 8.1.6 you can add a column with a new name and drop an old column. There
is a number of reasons not to do this but you said you were desperate...

Regs
Karen Abgarian

[adil]

Hi there,

As per my knowledge u dont have option to rename a column
i have 2 options with me
I
1. alter table <tablename> add(<newcolname> <datatype>)
2.update tablename set newcolname=oldcolname
3.drop col oldcolname.

II
create table <newtablename>(col1,col2,newcolname,col4)as select * from oldtablename

drop the old table.

Hope this will help U

Regards
Adil