Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle alter table question
"Rayden" <durlecht_at_remove-this.msu.edu> wrote in message
news:abe2na$2gfn$1_at_msunews.cl.msu.edu...
> hey,
>
> i have a table with which i need to change one of the columns, however
> because i have data in the tables it will not let me do this (oracle
8i).the
> column change is to increase a varchar in size. how in the world do i do
> this? i tried to exp the data, however it does the table as well. i am a
> little lost here, so any help would be appreciated.
>
>
> tom
>
>
Here's how in pseudo code
create a dummy table with the primary key of the source table and the
affected column
insert into dummy select pk, affected_column from source
update source set affected column = NULL
commit
alter table source
rem restore the original data
update table source
set affected_column =
(select affcted_column
from dummy d
where d.primary key = source.primary key
)
where exists
(select 'x'
from dummy d
where d.primary key = source.primary key
)
commit;
drop table dummy;
Hth
-- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail addressReceived on Thu May 09 2002 - 10:10:10 CDT