18.2.1 Defining the Object Type Specification

We'll start simple. In this example, all of the attributes are primitive datatypes rather than objects or collections. The photo attribute is of the Oracle8 datatype BFILE, which is simply a reference to a file stored in the operating system (see Chapter 4, Variables and Program Data ). We'll follow the convention that object type names start with a capital letter and end in "_t."

CREATE OR REPLACE TYPE Pet_t AS OBJECT (
   tag_no INTEGER,            -- pet license number
   name VARCHAR2(60),         -- name of pet
   animal_type VARCHAR2(30),  -- dog, cat, ferret,...
   sex VARCHAR2(1),           -- M/F
   photo BFILE,               -- JPG image in O/S file
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
      RETURN Pet_t,
   MEMBER FUNCTION set_photo (file_location IN VARCHAR2)
      RETURN Pet_t,
   MEMBER PROCEDURE print_me
);
/

This type contains scalar attributes and three methods. Note that the member functions return a value of type Pet_t. This affords flexibility in how the methods can be used later on.

NOTE: In SQL*Plus, you must enter the trailing slash after a CREATE TYPE or CREATE TYPE BODY statement. From this point forward in this text, we omit this trailing slash.

18.2.2 Defining the Object Type Body

To implement the methods, create the object type body, using a syntax that is remarkably similar to that used to create a package body:

CREATE OR REPLACE TYPE BODY Pet_t AS
   /* set_tag_no: Starting with the attributes of the "current"
   || pet object, return a new pet object with a new tag number.
   */
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
      RETURN Pet_t
   IS
      the_pet Pet_t := SELF;  -- initialize to current object
   BEGIN
      the_pet.tag_no := new_tag_no;
      RETURN the_pet;
   END;

   /* set_photo: Return new pet object with new photo filename.
   || Use BFILENAME, a built-in Oracle function that accepts
   || a directory alias and filename and returns a value of
   || type BFILE.
   */
   MEMBER FUNCTION set_photo (file_location IN VARCHAR2)
      RETURN Pet_t
   IS
      the_pet Pet_t := SELF;
   BEGIN
      the_pet.photo := BFILENAME('WEB_PHOTOS', file_location);
      RETURN the_pet;
   END;

   /* Do a simple listing of this object. Don't deal with the
   || contents of the photo, just its name.
   */
   MEMBER PROCEDURE print_me
   IS
      directory_alias VARCHAR2(60);
      file_name VARCHAR2(60);
   BEGIN
      DBMS_LOB.FILEGETNAME(SELF.photo, directory_alias, file_name);
      DBMS_OUTPUT.PUT_LINE('Tag No : ' || tag_no);
      DBMS_OUTPUT.PUT_LINE('Name   : ' || name);
      DBMS_OUTPUT.PUT_LINE('Type   : ' || animal_type);
      DBMS_OUTPUT.PUT_LINE('Sex    : ' || sex);
      DBMS_OUTPUT.PUT_LINE('Photo  : ' || file_name);
   END;
END;

This code introduces a couple of syntactic devices that we'll cover in detail later, namely the use of the "default constructor" to create an object of the designated type, and the use of the SELF parameter as a means of referring to the object and its component attributes. Notice that this code makes no mention of any tables in the database.

We haven't created a comprehensive implementation of this type yet, since we haven't coded methods for setting all of the attributes. Eventually, there could be quite a large number of new attributes, each of which could be managed by at least one method. Yes, an object approach can get a bit code-happy if you look at how the objects are implemented "under the covers." You may be asking yourself, why use these trivial procedures for setting attribute values? Why not expose the attributes and let application programs just set the value?

Although exceptions exist, a convention of the object approach is that all operations on object data must be implemented by methods. As soon as you allow the use of a back door to manipulate data, you have violated encapsulation, and you are asking for the same kind of trouble you'd get with excessive use of global variables: code that breaks easily, with low confidence that modifications will be free of errors, and, in general, high maintenance costs (see the discussion of global variables in Chapter 16, Packages ). It's true enough that comprehensive object definitions can be code intensive; the payback comes later on, during maintenance and future development, when well implemented objects are less likely to break and more likely to facilitate reuse and resilience.

18.2.3 Adding Complex Data Structures

As a dues-paying member of the SPCA, Relative Pets keeps a vaccination history on each animal. To introduce the rich data structures available to objects, we can create a nested table datatype to hold information about vaccinations. Each pet can have many vaccinations.

CREATE TYPE Vaccination_list_t AS TABLE OF VARCHAR2(30);

(See Chapter 19, Nested Tables and VARRAYs , for more information about this new collection type.) Since we are going to track the owners of the pets, we could benefit from having a "person" object. For now, we won't give this object type many attributes, but we will implement the RESTRICT_REFERENCES pragma so that we can use its "full_name" function in DML (see Chapter 17, Calling PL/SQL Functions in SQL , for a full discussion of this pragma):

CREATE TYPE Person_t AS OBJECT (
   person_id INTEGER,
   last_name VARCHAR2(60),
   first_name VARCHAR2(30),
   MEMBER FUNCTION full_name RETURN VARCHAR2,
   PRAGMA RESTRICT_REFERENCES (full_name, RNDS, WNDS, RNPS, WNPS)
 );

Here is a simple body that implements the full_name method:

CREATE TYPE BODY Person_t
AS
   MEMBER FUNCTION full_name RETURN VARCHAR2
   IS
   BEGIN
      RETURN first_name || ' ' || last_name;
   END;
END;

Finally, we can put a vaccination list and an owner in our "pet" object type:

CREATE OR REPLACE TYPE Pet_t AS OBJECT (
   tag_no INTEGER,            -- pet license number
   name VARCHAR2(60),         -- name of pet
   animal_type VARCHAR2(30),  -- dog, cat, ferret,...
   sex VARCHAR2(1),           -- M/F
   photo BFILE,               -- JPG image in O/S file
   
vaccinations Vaccination_list_t,

   
owner Person_t,

   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
      RETURN Pet_t,
   MEMBER FUNCTION set_photo (file_location IN VARCHAR2)
      RETURN Pet_t,
   MEMBER PROCEDURE print_me
);

Although not illustrated here, after making this change, we might want to add new member functions in the type specification and body.

In this example, we are setting ourselves up to use a nested "person" object within the pet object. This is not necessarily a good idea, since the enclosed objects are not sharable by anything else. That is, if one person owned many pets, the information about the owner would have to be copied into each pet object. We're passing up an opportunity to control data redundancy, which is one of the reasons we are using a such a wonderful database system in the first place. Not to worry: we can use the REF keyword to tell Oracle that we want to store only a pointer or reference to another (persistent) object. That is, in our final version:

CREATE OR REPLACE TYPE Pet_t AS OBJECT (
   tag_no INTEGER,
   name VARCHAR2(60),
   animal_type VARCHAR2(30),
   sex VARCHAR2(1),
   photo BFILE,
   vaccinations vaccination_list_t,
   
owner_ref REF Person_t,

   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
      RETURN Pet_t,
   MEMBER FUNCTION set_photo (file_location VARCHAR2)
      RETURN Pet_t,
   MEMBER PROCEDURE print_me,
   MEMBER PROCEDURE vaccinate (vaccination VARCHAR2, on_date DATE),
   PRAGMA RESTRICT_REFERENCES (set_tag_no, RNDS, WNDS, RNPS, WNPS),
   PRAGMA RESTRICT_REFERENCES (set_photo, RNDS, WNDS, RNPS, WNPS)
);

we use REF Person_t to indicate that this attribute will store a pointer to an object rather than to its contents.

And we can build the tables:

CREATE TABLE persons OF Person_t
   (PRIMARY KEY (person_id));

CREATE TABLE pets OF Pet_t
   (PRIMARY KEY (tag_no))
   NESTED TABLE  vaccinations STORE AS pet_vaccinations_tab;

Using this separate persons table and the REF attribute will allow the existence of people outside the context of their pets (something the pet-obsessed may not envision, but probably a good idea from a design point of view). In this context, REF is called a type modifier .

Does a REF sound a lot like a foreign key? While there are important differences between REFs and foreign keys (see Table 18.2 ), Oracle actually claims that REFs, are "more reliable and persistent" than foreign keys -- probably because REFs do not refer to user-changeable values, but rather to invisible internal values.

In fact, the problem with REFs is that they are too persistent. Oracle currently allows you to delete an object that is the target of a REF without deleting the reference to it. They even dignify this state with a name: a dangling REF . This is roughly equivalent to what would happen if you delete a department record without changing the records of employees in that department. There is no declarative way to prevent dangling REFs, but it should not be too challenging to do so by implementing pre-delete triggers on the table that contains the "parent" objects.[ 11 ] To make life somewhat easier, Oracle provides a predicate, IS DANGLING, to test for this condition:

[11] It is also possible to use a foreign key in combination with a REF. To do so, you would include an attribute for the foreign key in the Pet_t specification and include a FOREIGN KEY clause in the CREATE TABLE statement.

UPDATE pets
   SET owner_ref = NULL
 WHERE owner_ref 
IS DANGLING
;

Table 18.2: Chief Differences between Foreign Keys and REFs

Characteristic	Foreign Key	REF
Who defines the value used as the "pointer?"	User (programmer)	System
Requirements on the parent	Must have primary or unique key	Must be an object table or object view
Only allows insertions of child if parent exists (or if the referencing columns are null)?	Yes, when enabled	Yes, since you can only insert a "real" REF
Can be defined in such a way that the child may be associated with one of several possible parents?	No (although foreign keys have a little-known ability to point to all of several possible parents)	Yes; by default, a REF can refer to any row object of the given type
Can declaratively restrict the scope of the child so that it can point to only one given parent table?	No	Yes (by using the SCOPE clause in the CREATE TABLE command)
Restricts updates of the parent key when children exist?	Yes	Yes; object identifiers are not updateable
Can prevent the deletion of parent if children exist?	Yes	No
Can cascade deletions of the parent to child (objects)?	Yes, with ON DELETE CASCADE	No
Default type of relationship between parent and child when joined via SQL	Equi-join	Outer join (when using dot navigation)
Parent and child can be on different databases?	No; must be enforced with table-level triggers	Not in Oracle 8.0.3

NOTE: In Table 18.2 , we use the terminology "parent" and "child" only for convenience; these terms are not always accurate descriptions of objects linked via REFs.

Oracle has a special syntax for retrieving and modifying data in both SQL and PL/SQL using the REF operator; they also provide a DEREF operator (can you guess why?). We'll look at those operators a bit later.