Oracle PL/SQL/Object Oriented Database/Table

CREATE a TABLE with the Column Type in It

SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /
Type created.
SQL> CREATE TABLE emp (empno   NUMBER(3),
  2                    name    VARCHAR2(20),
  3                    address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>

Create table from Object type

SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /
Type created.
SQL>
SQL> CREATE TABLE emp (empno   NUMBER(3),
  2                    name    VARCHAR2(20),
  3                    address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL>
SQL> CREATE TABLE address_table OF ADDRESS_OBJ;
Table created.
SQL>
SQL>
SQL> INSERT INTO Address_table
  2  SELECT e.address
  3  FROM emp e;
1 row created.
SQL>
SQL>
SQL> SELECT city
  2  FROM address_table
  3
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>

dependencies between object types and tables.

 
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE RowObject AS OBJECT (
  2    attributeA NUMBER
  3  );
  4  /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE ColObject AS OBJECT (
  2    attributeB NUMBER
  3  );
  4  /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE TABLE RowTable OF RowObject;
Table created.
SQL>
SQL> CREATE TABLE ColTable (
  2    theObject ColObject
  3  );
Table created.
SQL>
SQL> SELECT object_name, object_type, status
  2    FROM user_objects
  3    WHERE object_name IN ("COLTABLE", "ROWTABLE");
OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
COLTABLE             TABLE               VALID
ROWTABLE             TABLE               VALID
SQL>
SQL>
SQL> DROP TABLE RowTable;
Table dropped.
SQL> DROP TABLE ColTable;
Table dropped.
SQL>
SQL>

Loading the "row object" Table

SQL> --Loading the "row object" Table
SQL>
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /
Type created.
SQL>
SQL> CREATE TABLE emp (empno   NUMBER(3),
  2                    name    VARCHAR2(20),
  3                    address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL>
SQL> CREATE TABLE address_table OF ADDRESS_OBJ;
Table created.
SQL>
SQL> INSERT INTO Address_table
  2  SELECT e.address
  3  FROM emp e;
1 row created.
SQL>
SQL> select * from address_table;
STREET               CITY                 ST ZIP
-------------------- -------------------- -- -----
1 A St.              Mobile               AL 36608

SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>