Datatypes and Collections - Oracle SQL Jumpstart with Examples - page 344

16.2.2.1 Using the REF Datatype

The MUSIC schema ARTIST table contains an address, split into separate
columns. Let's begin by creating a TYPE structure to contain an address. To
illustrate the REF datatype, we create two object tables: ADDRESSES con-
taining artist addresses and ARTIST2 containing the artist names and a ref-
erence to the ADDRESSES table.

CREATE OR REPLACE TYPE TADDRESS AS OBJECT(
STREET VARCHAR2(32), POBOX CHAR(20), CITY VARCHAR2(32)
, STATE_PROVINCE VARCHAR2(32), COUNTRY VARCHAR2(32)
, ZIP CHAR(10), EMAIL VARCHAR2(32));
/

Now we create a table based on the TADDRESS type structure we just
created:

CREATE TABLE ADDRESSES OF TADDRESS;

Now we create a new table for artists from the original ARTIST table
using the type we just created:

CREATE TABLE ARTIST2 AS
SELECT ARTIST_ID, NAME, INSTRUMENTS FROM ARTIST;

Now add the new addresses substructure to the new artists table:

ALTER TABLE ARTIST2 ADD (ADDRESS REF TADDRESS SCOPE IS
ADDRESSES);

Fill up the ADDRESSES table:

INSERT INTO ADDRESSES
SELECT STREET, POBOX, CITY, STATE_PROVINCE
, COUNTRY, ZIP, EMAIL FROM ARTIST;

Now update the REF column in the new artists table with the reference
pointer to each relative address in the ADDRESSES table, establishing the
REF pointer link between the ARTIST2 table and the ADDRESSES table: