11:                         `Cleveland','TN','37312','4235551212');
12:   address_2 := address_1;
13:
14:   --Change address #1
15:   address_1.ChangeAddress (`2800 Peermore Road','Apt 99',
16:                            `Detroit','MI','48823');
17:
18:   --Instantiate a second object.
19:   address_3 := address (`2700 Eaton Rapids Road','Lot 98',
20:                         `Lansing','MI','48911','5173943551');
21:
22:   --Now print out the attributes from each object.
23:   dbms_output.put_line(`Attributes for address_1:');
24:   dbms_output.put_line(address_1.getStreet(1));
25:   dbms_output.put_line(address_1.getStreet(2));
26:   dbms_output.put_line(address_1.getCity
27:                        || ` ` || address_1.getStateAbbr
28:                        || ` ` || address_1.getPostalCode);
29:   dbms_output.put_line(address_1.getPhone);
30:
31:   dbms_output.put_line(`-------------------------');
32:   dbms_output.put_line(`Attributes for address_2:');
33:   dbms_output.put_line(address_2.getStreet(1));
34:   dbms_output.put_line(address_2.getStreet(2));
35:   dbms_output.put_line(address_2.getCity
36:                        || ` ` || address_2.getStateAbbr
37:                        || ` ` || address_2.getPostalCode);
38:   dbms_output.put_line(address_2.getPhone);
39:
40:   dbms_output.put_line(`-------------------------');
41:   dbms_output.put_line(`Attributes for address_3:');
42:   dbms_output.put_line(address_3.street_1);
43:   dbms_output.put_line(address_3.street_2);
44:   dbms_output.put_line(address_3.city
45:                        || ` ` || address_3.state_abbr
46:                        || ` ` || address_3.zip_code);
47:   dbms_output.put_line(address_3.phone_number);
48: END;
49: /
Attributes for address_1:
2800 Peermore Road
Apt 99
Detroit MI 48823
4235551212
-------------------------
Attributes for address_2:
2700 Peerless Road
Apt 1
Cleveland TN 37312
4235551212
-------------------------
Attributes for address_3:
2700 Eaton Rapids Road
Lot 98
Lansing MI 48911
5173943551

PL/SQL procedure successfully completed.

ANALYSIS
Notice that in lines 4_6, three object variables are defined. They are of type address and will be used to contain address objects. When first created, these objects are considered to be null. Any calls to their member methods will result in an error, and any reference to their attributes will evaluate to null.

The first address object is instantiated in line 10. This is done by calling the constructor function for the address object and assigning the value returned to the object variable address_1. In line 12, a copy of this object is assigned to address_2. Then the value of address_1 is changed. This is done with a call to the ChangeAddress method (lines 15_16), and is done in order to demonstrate that address_1 and address_2 are indeed separate objects. In line 19, a third address object is created.

The values of these three address objects are displayed by the code in lines 22_47. Notice that although the accessor methods are used to retrieve the attribute values from the first two objects, the attributes of the third object are accessed directly.

Storing and Retrieving Objects

NEW TERM
There are two ways to store an object in an Oracle database. One is to store the object as a column within a table. This is the approach this chapter takes to storing the address objects. Each employee record will have one address associated with it. The other approach to storing objects involves the use of an object table. An object table is a relational table that has been defined to store a particular type of object. Each row in the table represents one object, and each column represents one attribute in the object.

Storing Objects as Table Columns

Oracle's object-relational model allows an object to be stored as a column in a database table. In order to do this, a column of the appropriate object type must first be added to the table in question. To create an address column in the employee table, you must first execute the DDL (Data Definition Language) statement shown in Listing 13.3.

INPUT/
OUTPUT
Listing 13.3. Creating a column for the address object.

1: ALTER TABLE employee
2:   ADD (
3:     home_address    address
4:   );
Table altered.

ANALYSIS
The preceding statement simply adds a column, which is named home_address, to the employee table. The column type is given as address, which is a reference to the object type you defined earlier in this chapter. For any existing employee records, the object is considered to be null.

Now that an address column exists in the employee table, you can create some employee records and store each employee's address along with the other information. Listing 13.4 shows two different ways in which to do this.

INPUT/
OUTPUT
Listing 13.4. Saving address objects with employee records.

 1: INSERT INTO employee
 2:   (emp_id, emp_name,pay_rate,pay_type,home_address)
 3:   VALUES (597,'Matthew Higgenbottom',120000,'S',
 4:           address(`101 Maple','','Mio','MI','48640','5173943551'));

1 row created.

COMMIT;

Commit complete.

 1: DECLARE
 2:   emp_home_address    address;
 3: BEGIN
 4:   emp_home_address := address(`911 Pearl','Apt 2','Lewiston',
                                  Â'MI','48645','5173363366');
 5:   INSERT INTO employee
 6:     (emp_id, emp_name,pay_rate,pay_type,home_address)
 7:     VALUES (598, `Raymond Gennick',55,'H',emp_home_address);
 8:   COMMIT;
 9: END;
10: /

PL/SQL procedure successfully completed.

 1: SELECT emp_id, emp_name, home_address
 2:   FROM employee
 3:  WHERE home_address IS NOT null;

   EMP_ID EMP_NAME
--------- --------------------------------
HOME_ADDRESS(STREET_1, STREET_2, CITY, STATE_ABBR, ZIP_CODE, PHONE_NUMBER)
--------------------------------------------------------------------------
      597 Matthew Higgenbottom
ADDRESS(`101 Maple', NULL, `Mio', `MI', `48640', `5173943551')

      598 Raymond Gennick
ADDRESS(`911 Pearl', `Apt 2', `Lewiston', `MI', `48645', `5173363366')

ANALYSIS
Lines 1_4 of the first segment show how a constructor method can be referenced from within a SQL statement. In fact, the statement in question was executed from within SQL*Plus, though it could just as well have been inside a PL/SQL block. Lines 1_10 of the third segment show a PL/SQL block that first instantiates an address object and then inserts that object into the employee table as part of an employee record. The emp_home_address

variable is defined in line 2 of the third segment as being of type address. Then in line 4 of the third segment, the address constructor is used to instantiate a new address object, which is assigned to the emp_home_address variable. Finally, in lines 5_7 of the third segment, an INSERT statement is executed, saving the employee record. The emp_home_address variable is included in the values list and is stored as a part of the record.

The SELECT statement (lines 1_3 of the fourth segment) retrieves the address objects that you have just inserted into the database. Notice how SQL*Plus uses the address type constructor in the resulting output to indicate that the addresses are from an embedded object.

Retrieving and Updating Objects in a Table Column

As with inserting, you can retrieve and update an object that is stored in a column, just as you would any other column value. Listing 13.5 shows a PL/SQL block that retrieves the address for employee number 597, changes the phone number, and then updates the table to contain the new value of the object.

INPUT/
OUTPUT
Listing 13.5. Retrieving and updating the address object.

 1: DECLARE
 2:   emp_addr    address;
 3: BEGIN
 4:   --Retrieve the object from the table
 5:   SELECT home_address INTO emp_addr
 6:     FROM employee
 7:    WHERE emp_id = 597;
 8:
 9:   --Use a mutator method to change the phone number.
10:   emp_addr.setPhone(`3139830301');
11:
12:   UPDATE employee
13:      SET home_address = emp_addr
14:    WHERE emp_id = 597;
15:
16:   COMMIT;
17: END;
18: /

PL/SQL procedure successfully completed.

 1: SELECT emp_id, emp_name, home_address.phone_number home_phone
 2:   FROM employee
 3:  WHERE emp_id = 597;

   EMP_ID EMP_NAME                         HOME_PHONE
--------- -------------------------------- ----------
      597 Matthew Higgenbottom             3139830301