Oracle move old columns to new User Defined Type

0
votes

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?

2
sqldatabaseoraclerelational-databaseuser-defined-types
I would strongly recommend that you use VARCHAR2 rather than CHAR for the data types, unless you explicitly want to waste space on trailing spaces. – Gordon Linoff

2 Answers

2
votes

What you are asking for is to have a table within a table. The way of doing it is to have your type defined and then define your new table as something like the following (took Gordon's suggestion into account as it is the right way of doing it and added the N prefix to include multi-language support):

Create the type:

CREATE TYPE FULL_ADDRESS_T AS OBJECT
 ( state  NUMBER(6)     ,
   street NVARCHAR2(50) ,
   city   NVARCHAR2(50)   ) ;

Create the new Table:

CREATE TABLE MY_TABLE AS 
 ( First_Name   NVARCHAR2(32)   ,
   Mid_Name     NVARCHAR2(32)   ,
   Last_Name    NVARCHAR2(32)   ,
   Address      FULL_ADDRESS_T    ) ;

Note that this will make things more complex for you to manipulate.

1
votes

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