Performing DML on the object_customers Table

The object_customers table is an object table of person_typ; person_typ contains an embedded address_typ column object named address . The object_customers table is defined as follows :

 CREATE TABLE object_customers OF person_typ; 

In this section, you ll see how to perform SQL DML statements to insert and select rows in the object_customers table. Since updates and deletes for object_customers are conceptually similar to the previous examples for object_products , I won t show examples of an update and delete for object_products .

Inserting Rows into the object_customers Table

The following examples insert two rows into object_customers . The first example uses constructors for person_typ and address_typ , while the second example omits the person_typ constructor:

 INSERT INTO object_customers VALUES (person_typ(1, 'John', 'Brown', '01-FEB-1955', '800-555-1211',  address_typ('2 State Street', 'Beantown', 'MA', '12345'))); INSERT INTO object_customers (id, first_name, last_name, dob, phone,  address) VALUES (2, 'Cynthia', 'Green', '05-FEB-1968', '800-555-1212',  address_typ('3 Free Street', 'Middle Town', 'CA', '12345')); 

Selecting Rows from the object_customers Table

The object_customers table is an object table of person_typ; person_typ contains an embedded address_typ column object named address . The following example selects all the rows from the object_customers table. Notice that the attributes for the embedded address column object are displayed within the address_typ constructor:

  SELECT *   FROM object_customers;  ID FIRST_NAME LAST_NAME DOB PHONE ---------- ---------- ---------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP) ----------------------------------------------------------  1 John Brown 01-FEB-55 800-555-1211 ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345')  2 Cynthia Green 05-FEB-68 800-555-1212 ADDRESS_TYP('3 Free Street', 'Middle Town', 'CA', '12345') 

The next example selects a single row from object_customers . Notice the use of the table alias oc through which the id attribute is specified:

  SELECT *   FROM object_customers oc   WHERE oc.id = 1;  ID FIRST_NAME LAST_NAME DOB PHONE ---------- ---------- ---------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP) --------------------------------------------------------  1 John Brown 01-FEB-55 800-555-1211 ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345') 

In the following example, a row is selected based on the state attribute of the address column object:

  SELECT *   FROM object_customers oc   WHERE oc.address.state = 'MA';  ID FIRST_NAME LAST_NAME DOB PHONE ---------- ---------- ---------- --------- ------------ ADDRESS(STREET, CITY, STATE, ZIP) ---------------------------------------------------------------------  1 John Brown 01-FEB-55 800-555-1211 ADDRESS_TYP('2 State Street', 'Beantown', 'MA', '12345')