Re: Renaming Columns -- How?

1. Your approach is perfectly ok - To make the first create temp_table work u need to use COPY command of sql*plus as your source table contains LONG data type. Be sure to SET the sqlplus environment by setting SET LONG size and SET COPYCOMMIT attributes before running the command.
2. Other approach for renaming a column is similar to above

step 1 : Rename the original table;
step 2 : create the new table with original name using COPY command (in your case) by specifying the new column list and selecting from original list.
step 3: insert data and create permissions , indexes etc...

3. Another approach which u can explore is renaming the old table and creating a view on it with the original name - where in view query u can specify the alias of column u want to rename. But do check to make sure it does'nt affect the application.  

Hope this helps

Kanwal Dhar
Certified Oracle DBA

Tay Ninh Brown <tay_at_NO.JUNK> wrote in article <01bd52a2$29432dd0$8187d9cf_at_rana>...
> Is there a way to rename table columns?
>
> I've done the following to rename a column while
> preserving all the old table data:
>
> create table temp_tablename as
> select * from tablename
> /
> drop table tablename
> /
> create tablename ( <newcolumns-with-new-names> )
> /
> create indexes, permissions, etc.
> /
> insert into tablename ( <newcolumns-list> )
> select <oldcolumns-list>
> from temp_tablename
> /
>
> The very first statement fails for one of our tables
> because one of the columns is of LONG datatype.
>
> Is there a simple, single command for renaming a column?
>
> Thanks in advance.
>
> (Apologies for anti-spam addressing: see Org line for domain info.)
>
Received on Thu Mar 19 1998 - 00:00:00 CST