#

| Chapter 18 Object Types |

## 18.2 Oracle Objects Example

Relative Pets, a combination pet store and animal hospital, is developing a database to help track their clientele. They tend to take an animal-centric view of the world, so their main entity is the Pet. They're also working on a web site where they can post photos of some critters that have been put up for adoption. Being on the foreleg of technology, they of course want to use an object-relational database like Oracle8.

### 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.2.1 PL/SQL usage

To create an object based on a type -- that is, to instantiate it -- you can use a default constructor, a function that Oracle makes available as soon as you create a type. The constructor has the same name as the object type, and accepts each attribute as an argument, in the order in which attributes are declared in the object type definition. That is, Pet_t is both the name of an object type and the name of the corresponding default constructor:

>     DECLARE
>        the_pet Pet_t;
>     BEGIN
>        -- Instantiate the_pet using the default constructor
>        the_pet := Pet_t(104525, 'Mambo', 'DOG', 'M',
>           BFILENAME('WEB_PHOTOS','Mambo.jpg'));

>        -- Invoke a method to change the tag number of the_pet
>        the_pet := the_pet.set_tag_no(104552);

>        -- Print a listing about the_pet
>        the_pet.print_me();
>     END;

Notice the syntax for invoking the set_tag_no and print_me methods on the_pet. This object.method syntax is not uncommon in other OO languages. It's different from package.procedure_or_function syntax, because what you find on the left side of the dot is an object instance. That is, a correct call is:

>     the_pet.set_tag_no(104552)  -- the_pet is a variable of type Pet_t

rather than:

>     Pet_t.set_tag_no(104552)  -- invalid; what pet's no. are we changing?

You have to tell Oracle which particular object instance you want to use.

If you prefer, you can use a method invocation that is almost indistinguishable from a conventional packaged function or procedure call, with either named or positional notation. The following calls show how you can simply supply the object instance as the SELF parameter (more about SELF later):

>     Pet_t.set_tag_no (the_pet, 104552)
>     Pet_t.set_tag_no (SELF=>the_pet, new_tag_no=>104552)

#### 18.2.2.2 DDL usage

To create a database structure that will hold persistent objects of a given type, it's blissfully easy to create an object table:

>     CREATE TABLE pets OF Pet_t
>        (PRIMARY KEY (tag_no));

This creates a table with columns for the attributes. The table also has other special characteristics, such as a hidden object identifier column, on which Oracle automatically creates a unique index. As implied by the PRIMARY KEY clause, it also creates a relational primary key constraint -- and therefore an additional unique index -- on the tag_no column. (You cannot, by the way, use a nonscalar column such as a REF or a collection as the primary key.)

Proving to ourselves that our table looks right, we can do a "describe" on it as follows:

>     SQL> desc pets
>      Name                            Null?    Type
>      ------------------------------- -------- ----
>      TAG_NO                                   NUMBER(38)
>      NAME                                     VARCHAR2(60)
>      ANIMAL_TYPE                              VARCHAR2(30)
>      SEX                                      VARCHAR2(1)
>      PHOTO                                    BINARY FILE LOB

and we can insert a record object into the table:

>     INSERT INTO pets VALUES(Pet_t(1044526,'Socks','CAT', 'M',
>        BFILENAME('WEB_PHOTOS', 'socks.jpg')));

The INSERT statement uses the constructor in the VALUES clause.[10] Now the object can continue its life more or less permanently in a database table. It can get retrieved by PL/SQL programs or referenced by other tables as needed. However, up to this point, we have not defined any methods that will manage the persistent data. We'll see later how to design these additional methods you have to invoke when programs need to change the state of a persistent object.

> [10] This statement also assumes that we have already created the WEB_PHOTOS directory alias using the SQL CREATE DIRECTORY statement, as in: CREATE DIRECTORY web_photos AS '/u01/www/images/photos'.

### 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.

18.1 Introduction to Oracle8 Objects |  | 18.3 Syntax for Creating Object Types

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.