REF Constraint Examples The following example creates a duplicate of the sample schema object type cust_address_typ, and then creates a table containing a REF column with a SCOPE constraint:CREATE TYPE cust_address_typ_new AS OBJECT ( street_address VARCHAR2(40) , postal_code VARCHAR2(10) , city VARCHAR2(30) , state_province VARCHAR2(10) , country_id CHAR(2) ); / CREATE TABLE address_table OF cust_address_typ_new;CREATE TABLE customer_addresses ( add_id NUMBER, address REF cust_address_typ_new SCOPE IS address_table);
The sample schema oe contains a type called cust_address_typ, described as follows: Attribute Type ----------------------------- ---------------- STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(10) CITY VARCHAR2(30) STATE_PROVINCE VARCHAR2(10) COUNTRY_ID CHAR(2)The following example creates a table based on the sample type oe.cust_address_typ, inserts a row into the table, and retrieves a REF value for the object instance of the type in the addresses table:CREATE TABLE addresses OF cust_address_typ;INSERT INTO addresses VALUES ( '123 First Street', '4GF H1J', 'Our Town', 'Ourcounty', 'US');SELECT REF(e) FROM addresses e;REF(E) ----------------------------------------------------------------------------------- 00002802097CD1261E51925B60E0340800208254367CD1261E51905B60E034080020825436010101820000
The following example creates a duplicate of the sample schema object type cust_address_typ, and then creates a table containing a REF column with a SCOPE constraint:CREATE TYPE cust_address_typ_new AS OBJECT
( street_address VARCHAR2(40)
, postal_code VARCHAR2(10)
, city VARCHAR2(30)
, state_province VARCHAR2(10)
, country_id CHAR(2)
);
/
CREATE TABLE address_table OF cust_address_typ_new;CREATE TABLE customer_addresses (
add_id NUMBER,
address REF cust_address_typ_new
SCOPE IS address_table);
----------------------------- ----------------
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(10)
CITY VARCHAR2(30)
STATE_PROVINCE VARCHAR2(10)
COUNTRY_ID CHAR(2)The following example creates a table based on the sample type oe.cust_address_typ, inserts a row into the table, and retrieves a REF value for the object instance of the type in the addresses table:CREATE TABLE addresses OF cust_address_typ;INSERT INTO addresses VALUES (
'123 First Street', '4GF H1J', 'Our Town', 'Ourcounty', 'US');SELECT REF(e) FROM addresses e;REF(E)
-----------------------------------------------------------------------------------
00002802097CD1261E51925B60E0340800208254367CD1261E51905B60E034080020825436010101820000