Oracle move old columns to new User Defined Type

Question

I have a table in my oracle database with several columns for representing an address fields like: city, street, state... now i want to use an Address UDT created like this:

CREATE TYPE ADDRESS AS OBJECT
( 
state NUMBER(6),
street CHAR(50),
city CHAR(50),
)

How can i move and convert old columns to this new object in a new column?

Answer 1

Create the TYPE

CREATE TYPE ADDRESS_TYP AS OBJECT
( 
state NUMBER(6),
street VARCHAR2(50),
city VARCHAR2(50)
);

ADD the object type column to the TABLE

ALTER TABLE YOURTABLE ADD ( ADDRESS ADDRESS_TYP );

Update the new ADDRESS column from the existing values in the column.

UPDATE YOURTABLE SET  ADDRESS = ADDRESS_TYP( state,street,city );

The rest is left to you whether you want to keep the old columns in the table or not. But, if you are dropping those columns, know clearly the dependancies like INDEX,FOREIGN KEYs etc.

For large data sets, the update may be a bit slow. You could use some of the techniques discussed here: Ask TOM