A Relational Table with Object Types

You can create a user-defined datatype and then use the new datatype in a relational table. The data actually resides as column values in the table, rather than the column value being a pointer (REF) to the data.

This methodology creates a column object or nested object that 
 SQL> CREATE TYPE  address_typ  AS OBJECT (
   2    address1   VARCHAR2(32),
   3    address2   VARCHAR2(32),
   4    city       VARCHAR2(16),
   5    state      CHAR(2),
   6    zip        CHAR(5) );

 Type created.

 SQL> CREATE TABLE  customer (
   2    id            INTEGER PRIMARY KEY,
   3    cust_address  address_typ );

 Table created.


The address_typ type is used as the datatype for the cust_address column of a customer relational table. When object types are instantiated in this way, you create a column object or nested object. If the user-defined type is in another schema, you must use the schema name as a qualifier of the datatype. For example, if user scott owns scotts_typ object, use: 
   CREATE TABLE  my_table( x scott.scotts_typ );



      Actions speak louder than words.