Re: How Change Column Name?

From: Joseph S. Testa <jtesta_at_scioto.net>
Date: 1997/12/04
Message-ID: <34874B51.B1FFDCC5@scioto.net>#1/1

Stan Towianski wrote:

> Hi,
>
> I have a table that has a column named 'filler' that was not used
> and I want to use it now and rename it to 'seq_no'.
> What is the best way to do this?
>
> I do not know how to get the data back in if say I export the table.
> If I do then the export file has a create stmt, which I can get by
> with IGNORE=Y on import, but in the INSERT stmt it names all the
> columns
> and therefore will not import.
>

I wrote a drop column package and am working next on the one to rename a column but here is what you want to do. first off rename the table. then do a create table with the columns you want them called. then do insert into new_table as select column1, column2, null, etc from old table and it will look like this:

x_table looks like this: column1 char(20),

                                       filler char(10),
                                       column3 char(2)

rename x_table to x_table_old;
create table x_table(column1 char(20),

                               seq_no char(10),
                               column_3 char(2);

insert into x_table select column1,null,column3 from old_table_x;

this will work if you DONT have any long or long raw fields in the table. if you do you are going to be out of luck, there is no easy way.

Hope that helps,. joe

--
Joseph S. Testa, Oracle Database Administrator, mailto:jtesta_at_scioto.net
(home)
Vice-President Ohio Oracle Users Group,
See the Oracle FAQ at http://www.orafaq.org
Try http://web.scioto.net/jtesta that is also the place to find the FREE,
drop column script
ICQ UIN:  2832230(&oracle, home).

Received on Thu Dec 04 1997 - 00:00:00 CST