18.5.1 Approach 1: Permit Full Use of Conventional SQL

This scheme, illustrated in Figure 18.1 , is characterized by ultimate flexibility. It allows any application to use whatever SQL the programmer feels is necessary to achieve the desired result. It requires little or no coordination among programmers, although they will (you hope) be coding against a shared database design.

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4)
);

CREATE TABLE docs OF Doc_t
   (PRIMARY KEY (doc_id));
 

Figure 18.1: Approach 1 (full use of SQL)

It is certainly possible to go wild with SQL, as if we weren't using objects at all. Various applications might be issuing DML as they see fit.

An Oracle Forms application might be issuing inserts...

INSERT INTO docs VALUES (1001, 'Great Expectations', 'Charles Dickens',
   'http://www.literature.org/Works/Charles-Dickens/
    great-expectations/', 1861);

INSERT INTO docs VALUES(42, 'The Hitchhiker''s Guide to the Galaxy',
   'Douglas Adams', 'http://www.bxscience.edu/~grollman/trilogy/
    Hitchhikers.Guide.to.the.Galaxy', 1979);

...while an application programmer is working in SQL*Plus...

UPDATE docs SET author = 'Chuck Dickens' WHERE doc_id = 1001;

SELECT * FROM docs WHERE author LIKE '%Adams';

...and a Pro*C application is deleting data that's not in the mainframe any more:

DELETE docs WHERE doc_id = 42;

Does this possibly look a lot like what you've been doing for years? Consider for a moment the problems this might cause. First, allowing any application to issue any DML statement under the sun makes it difficult simply to assess the impact of a schema change, not to mention the actual cost and time to implement the change. Second, there is little hope of reusing DML statements since there is no central, controlled superstructure in place to facilitate code sharing. And what happens if you discover "bad data" in the database, which might be the result of a bug in an UPDATE statement in some application? Since DML can get mind-numbingly complex, simply locating buggy SQL can be a frustrating, costly process.

With an undisciplined approach, a database system of any substantial size -- regardless of whether the database is relational or object-relational -- may not survive the weight of its own maintenance.

18.5.2 Approach 2: Define Methods and Permit Limited Use of Conventional SQL

By adding methods to your objects, you can publicly announce to other programmers that you will maintain a standard, controlled interface to an object, and that if they use the methods you have defined, they and you will be less prone to getting slapped with maintenance work later on. Figure 18.2 illustrates how clients in this second approach invoke their own DML statements, but use method calls in these statements.

Figure 18.2: Approach 2 (methods and limited SQL)

Looking at a code sample, the set_author method below can be used in an UPDATE statement:

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4),
   -- we return the entire object so methods can be chained if needed
   MEMBER FUNCTION set_author (new_author VARCHAR2) RETURN Doc_t,
   PRAGMA RESTRICT_REFERENCES (set_author, RNDS,WNDS,RNPS,WNPS)
);

CREATE OR REPLACE TYPE BODY Doc_t AS
   MEMBER FUNCTION set_author (new_author VARCHAR2) RETURN Doc_t
   IS
      the_doc Doc_t := SELF;
   BEGIN
      the_doc.author := new_author;
      RETURN the_doc;
   END set_author;
END;

Application DML might look like the following. Inserts are straightforward enough, using the default constructor:

INSERT INTO docs VALUES (
Doc_t(
1005, 'The Tempest',
   'William Shakespeare', 'http://the-tech.mit.edu/Shakespeare/Comedy/
    tempest/thetempest.html', 1611
)
);

Or even (just to illustrate the point):

INSERT INTO docs SELECT VALUE(d) FROM docs d;

Yes, the previous statement duplicates all the objects in the docs table.

In SQL, you can invoke a method in an UPDATE statement in a straightforward fashion. To change all of Shakespeare's documents so that I am the author, the following statement will do the job. Notice the use of the table alias d as the placeholder for the "current" object.

UPDATE docs d
   SET d = d.set_author('Bill')
 WHERE d.author = 'William Shakespeare';

Deleting a persistent object in this second approach is just like the first approach:

DELETE docs WHERE doc_id = 1001;

Or in PL/SQL, if we already have a REF in the doc_ref variable, we could delete as follows:

DELETE docs d WHERE REF(d) = doc_ref;

How does Approach 2 insulate applications from change? It's not hard to see that if we later implement a business rule regarding changing authors -- for example, it's illegal to alter the author of anything written prior to 1900 -- we need only recode the method. When dealing with persistent objects, you will rightly point out, this sort of restriction can be coded using database triggers or stored procedures; however, triggers are no help at all if you want to enforce rules on transient objects.

Overall, this second approach, while improved, still leaves much to be desired, since it still relies heavily on programmer discipline to use the object methods in their DML statements.

18.5.3 Approach 3: Do Everything via Methods

If you are wedded to object technology, this approach (illustrated in Figure 18.3 ) may be for you, although it poses some challenges. In the sample code, notice that every DML statement will occur via a procedure call. Remember that DML can only be executed in member procedures because Oracle does not allow functions to modify the database.

Figure 18.3: Approach 3 (everything via methods)

Rather than look at the full code of the object specification and body, let's look first at a client executing a single operation: change the name of the author. You would want a very simple call where you provide only the name of the new author. In other words, the call might ideally be invoked something like this:

DECLARE
   the_doc_ref REF Doc_t;
BEGIN
   SELECT REF(d) INTO the_doc_ref
     FROM docs d
    WHERE d.doc_id = 1001;
   
the_doc_ref.set_author('me');   --  hypothetical call; is invalid

END;

Here, "set_author" is a proposed method of the Doc_t object. Set_author would know how to update the database object corresponding to the_doc_ref. Whoops! Did you forget? You cannot navigate the database through REF variables. And the_doc_ref is certainly a REF variable, so you can't use it in place of an object.

Let's look at this from the other side now. What could we do in the object definition to make an update work easily?

Here again is the specification, which we won't change (yet):

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4),
   MEMBER PROCEDURE set_author (new_author IN VARCHAR2)
);

And here is the body, with a new, but so far incomplete, UPDATE statement:

CREATE OR REPLACE TYPE BODY Doc_t
AS
   MEMBER PROCEDURE set_author (new_author IN VARCHAR2)
   IS
   BEGIN
      /* First update the transient object */
      SELF.author := new_author;

      /* Now update the persistent object  */
      UPDATE docs d SET author = new_author
       WHERE ???
   END;
END;

What exactly do we put in the WHERE clause? You could try this:

WHERE d = SELF;

But then you are doing an object comparison that could be problematic if you have defined a MAP member function. Think about it: MAP only provides some scalar mapping of some object characteristics, and it is possible to have different objects with the same MAPped value!

So what you might think of next is using a REF to the object:

WHERE REF(d) = REF(SELF);

The problem with this WHERE clause is that you can't get the REF of SELF, since SELF is not a table alias; logically, it also fails because it might not exist as a referenceable object in the database. (It kind of makes sense, but you don't have to like it.)

Now what? What about passing in a REF, something like this:

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4),
   
MEMBER PROCEDURE set_author (new_author IN VARCHAR2,


      doc_ref REF Doc_t)

);

CREATE OR REPLACE TYPE BODY Doc_t
AS
   MEMBER PROCEDURE set_author (new_author IN VARCHAR2,
      doc_ref REF Doc_t)
   IS
   BEGIN
      /* First update the transient object */
      SELF.author := new_author;

      /* Now update the persistent object  */
      UPDATE docs d
         SET author = new_author
      
 WHERE REF(d) = doc_ref;

   END;
END;

There! That not only compiles, it does what we want. Here's an example of using the member procedure in PL/SQL to update the persistent object:

DECLARE
   my_doc Doc_t;
   my_doc_ref REF Doc_t;
BEGIN
   SELECT VALUE(d), REF(d) INTO my_doc, my_doc_ref
     FROM docs d
    WHERE doc_id = 1001;
   my_doc.set_author('Yogi Bear', my_doc_ref);
END;

It's a little ugly with the extra REF argument in there, but there is no easy way in Oracle8.0 to get to a more "pure" object approach.

Are we finished? What about the possibility that we want to update only the transient object, rather than the persistent object? For example, we might want to set_author from within a SQL SELECT statement. For that, we can simply define a similar member function, which unfortunately cannot have the overloaded same name as the member procedure, since PRAGMA RESTRICT_REFERENCES will attempt to take effect on all modules of the supplied name.

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4),
   MEMBER FUNCTION set_author (new_author IN VARCHAR2)
      RETURN Doc_t,
   PRAGMA RESTRICT_REFERENCES (set_author, RNDS,WNDS,RNPS,WNPS),

   /* We are changing the name of the procedure below to have "_p"
   ||  on the end, as a mnemonic for Persistent (or Procedure)
   */
   MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2,
      doc_ref REF Doc_t)
);

CREATE OR REPLACE TYPE BODY Doc_t AS
   MEMBER FUNCTION set_author (new_author IN VARCHAR2)
      RETURN Doc_t
   IS
      l_doc Doc_t := SELF;
   BEGIN
      l_doc.author := new_author;
      RETURN l_doc;
   END;

   MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2,
      doc_ref REF Doc_t)
   IS
   BEGIN
      SELF := SELF.set_author (new_author);
      UPDATE docs d
         SET author = new_author
       WHERE REF(d) = doc_ref;
   END;
END;

This code will definitely work. Because of the RESTRICT_REFERENCES, you can use the function in SQL:

SELECT d.set_author('me') FROM docs d;

...and you can use the function from PL/SQL:

DECLARE
   my_doc Doc_t := Doc_t(1,'OPP','Feuerstein',null,null);
BEGIN
   my_doc := my_doc.set_author('Steven Feuerstein');
END;

...and you can use the procedure in PL/SQL, as indicated earlier.

Think for a minute about the possibility that you code a method that changes more than one attribute at a time. You could get a bit more elegant by updating the entire object in the UPDATE statement, rather than using individual assignments in the SET clause.

MEMBER PROCEDURE set_name_and_author (new_name IN VARCHAR2,
   new_author IN VARCHAR2,
   doc_ref IN REF Doc_t) IS
BEGIN
   SELF.name := new_name;
   SELF.author := new_author;
   UPDATE docs d
      SET d = SELF
    WHERE REF(d) = doc_ref;
END;

WARNING: While this code is syntactically correct, it fails in Oracle8.0.3 due to a PL/SQL bug. At press time, summer of 1997, we don't know exactly when this will be fixed -- soon, we hope!

The other "update" methods (for example, set_name, set_url, etc.) in our all-object approach would follow the general patterns we've established so far.

There are two more cases that you'll want to cover: persistent object construction and destruction. These are relatively simple INSERT and DELETE statements, as shown below:

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4),
   MEMBER PROCEDURE construct_and_save (the_doc_id IN INTEGER,
      the_name IN VARCHAR2, the_author IN VARCHAR2,
      the_url IN VARCHAR2, the_year IN INTEGER,
      doc_ref_out OUT REF Doc_t),
   MEMBER PROCEDURE save (doc_ref_out OUT REF Doc_t),
   MEMBER PROCEDURE destroy (doc_ref IN REF Doc_t),
   MEMBER FUNCTION set_author (new_author IN VARCHAR2)
      RETURN Doc_t,
   PRAGMA RESTRICT_REFERENCES (set_author, RNDS,WNDS,RNPS,WNPS),
   MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2,
      doc_ref REF Doc_t)
);

CREATE OR REPLACE TYPE BODY Doc_t AS
   MEMBER FUNCTION set_author (new_author IN VARCHAR2)
      ...as above

   MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2,
      ...as above

   MEMBER PROCEDURE construct_and_save (the_doc_id IN INTEGER,
      the_name IN VARCHAR2, the_author IN VARCHAR2,
      the_url IN VARCHAR2, the_year IN INTEGER,
      doc_ref_out OUT REF Doc_t)
   IS
   /* Encapsulates the constructor and the INSERT. Note that the
   || values passed in via arguments need not match the initial
   || attributes of SELF. In fact, SELF can be atomically null
   || when you first invoke this method.
   */
   BEGIN
      SELF := Doc_t(the_doc_id, the_name, the_author, the_url,
         the_year);
      SELF.save(doc_ref_out);
   END;

   MEMBER PROCEDURE save (doc_ref_out OUT REF Doc_t)
   IS
   /* Use this one for the INSERT if you have already invoked
   || the constructor and have a doc object in a variable.
   || The RETURNING clause returns the object's OID into the
   || host variable doc_ref.
   */
   BEGIN
      INSERT INTO docs d VALUES (SELF)
         RETURNING REF(d) INTO doc_ref_out;
   END;

   MEMBER PROCEDURE destroy (doc_ref IN REF Doc_t)
   IS
   /* Note that we are passing in a REF that might not match
   || whatever might be in SELF. It is up to the calling module to
   || be aware of this idiosyncrasy.
   */
   BEGIN
      SELF := NULL;
      DELETE docs d
       WHERE REF(d) = doc_ref;
   END;
END;

When you store an object using the "save" procedure above, you will have already created an in-memory version of the object using the default constructor or other means. Contrast this with "construct_and_save."

As noted in the comments, the RETURNING clause of the INSERT statement returns the REF identifier of the newly created object into a program variable. Since this is a very useful value, we return it to the calling program using an OUT parameter. (RETURNING is a new feature of SQL in Oracle8 that allows us to retrieve data on INSERT, UPDATE, and DELETE statements. It's useful not only for returning a REF but also for returning other generated column values such as those computed by database triggers.)

When you "destroy" an object in a program, you simply assign NULL to the object. This returns its state to "atomically null," which is the nonvalue the object would have if you had never constructed it. Oracle does not supply any sort of "destructor" method.

Let's step back and think about the "method-only" approach. Making the object type responsible for its own persistence seems like a good idea. But this code assumes that your Doc_t object type will be intimately tied to the "docs" table. Is that a reasonable assumption? There is nothing in Oracle that forces you to implement the Doc_t object type only one time. If you ever decide to reuse the object types, which you probably will, our "method-only" approach could run into problems.

Maybe you could define an argument to indicate which table(s) needs to receive the data change. You could wind up with messy IF tests and multiple UPDATE statements or, at best, dynamic SQL to apply the change to multiple tables. Worse, you could end up having to drop and rebuild your object tables to accommodate the changes in method specifications (see Section 18.6.3, "Schema Evolution" later on). Very un-object-oriented.

But what is the alternative? Any way you code it, managing the persistent data in multiple object tables defined on the same object type will be more trouble than a single table. You could allow rampant DML statements, which we have already poo-poo'd as the moral equivalent of global variables. Perhaps we could make our code "more object-oriented" if the Oracle objects option allowed inheritance. That way, we might be able to have an object table managed by a child object type. The parent would manage only transient objects, and the children would manage persistent objects.

As we'll see in Approach 4, another method is to rely on the "other" object features of the language that you can find in packages. By using object types for nonpersistent transient data only, you can avoid a number of thorny issues, at the possible expense of clouding your abstraction.

18.5.4 Approach 4: Use an Object and a PL/SQL Container Package

If you typically program with packages, this approach (illustrated in Figure 18.4 ) will look familiar, and you should feel free to pat yourself on the back. If you don't use packages, you should seriously consider their benefits (see Chapter 16 ), which can include many of the same advantages of objects.

In this scheme, we eliminate all references to persistent data from the object definition and body. Instead, we write a PL/SQL package that will manage our object tables. By separating the layer that manages persistence (the package) from the layer that manages objects (the object type), we have a closer fit of PL/SQL with Oracle's object model, especially if we intend to reuse object type definitions. In addition, we gain access to REF CURSORs, which allow the encapsulation of complicated SELECT statements.

Figure 18.4: Approach 4 (object and PL/SQL container package)

Let's start with the type definition, with only the single archetypal set_author function illustrated:

CREATE OR REPLACE TYPE Doc_t AS OBJECT (
   doc_id INTEGER,
   name VARCHAR2(512),
   author VARCHAR2(60),
   url VARCHAR2(2000),
   publication_year INTEGER(4),
   MEMBER FUNCTION set_author (new_author IN VARCHAR2)
      RETURN Doc_t
);

And the body, which might be very familiar by now:

CREATE OR REPLACE TYPE BODY Doc_t AS
   MEMBER FUNCTION set_author (new_author IN VARCHAR2) RETURN Doc_t
   IS
      the_doc Doc_t := SELF;
   BEGIN
      the_doc.author := new_author;
      RETURN the_doc;
   END set_author;
END;

We can create an object table that will hold "appraisal" document objects, as long as they fit the Doc_t definition, and design the package without disturbing the object type:

CREATE TABLE appraisals OF Doc_t
   (PRIMARY KEY (doc_id));

The specification of our package to manage the persistent appraisal data could go something like this:

CREATE OR REPLACE PACKAGE manage_appraisal AS
   /* Allow creation of an appraisal document */
   PROCEDURE create_one (doc_id_in INTEGER, name_in VARCHAR2,
      author_in VARCHAR2, url_in VARCHAR2,
      publication_year_in INTEGER, doc_ref_out OUT REF Doc_t);

   /* Look up an appraisal based on its document ID  */
   FUNCTION find_doc_by_id (doc_id_in INTEGER) RETURN Doc_t;

   /* Look up an appraisal's REF based on its document ID  */
   FUNCTION find_doc_ref_by_id (doc_id_in INTEGER) RETURN REF Doc_t;

   /* Overload the update procedures to accommodate any of:
   ||    1) "primary key" style identification
   ||    2) identification by object
   ||    3) identification by object REF
   */
   PROCEDURE set_one_author (doc_id_in IN INTEGER,
      new_author_in IN VARCHAR2);
   PROCEDURE set_one_author (doc_in IN Doc_t,
      new_author_in IN VARCHAR2);
   PROCEDURE set_one_author (docref_in IN REF Doc_t,
      new_author_in IN VARCHAR2);

   /* You could define other "set_one" update procedures here
   || for other attributes...
   */

   -- don't forget about the deletions!
   PROCEDURE delete_one (doc_id_in IN INTEGER);
   PROCEDURE delete_one (doc_in IN Doc_t);
   PROCEDURE delete_one (docref_in IN REF Doc_t);
END;

And the body:

CREATE OR REPLACE PACKAGE BODY manage_appraisal AS
   PROCEDURE create_one (doc_id_in INTEGER, name_in VARCHAR2,
      author_in VARCHAR2, url_in VARCHAR2,
      publication_year_in INTEGER, doc_ref_out OUT REF Doc_t) IS
   BEGIN
      INSERT INTO appraisals a VALUES (Doc_t(doc_id_in, name_in,
         author_in, url_in, publication_year_in))
         RETURNING REF(a) INTO doc_ref_out;
   END create_one;

   FUNCTION find_doc_by_id (doc_id_in IN INTEGER) RETURN Doc_t IS
      doc Doc_t;
      CURSOR doc_cur IS SELECT VALUE(a) FROM appraisals a
         WHERE doc_id = doc_id_in;
   BEGIN
      OPEN doc_cur;
      FETCH doc_cur INTO doc;
      CLOSE doc_cur;
      RETURN doc;
   END find_doc_by_id;

   FUNCTION find_doc_ref_by_id (doc_id_in IN INTEGER)
      RETURN REF Doc_t IS
      doc_ref REF Doc_t;
      CURSOR doc_cur IS SELECT REF(a) FROM appraisals a
         WHERE doc_id = doc_id_in;
   BEGIN
      OPEN doc_cur;
      FETCH doc_cur INTO doc_ref;
      CLOSE doc_cur;
      RETURN doc_ref;
   END find_doc_ref_by_id;

   /* Unfortunately, the PL/SQL bug mentioned earlier prevents the
   || next three procedures from compiling. The error message is
   || "PLS-00382: expression is of wrong type." Until Oracle fixes
   || this problem, a conventional update statement will do the job,
   || although it will violate encapsulation and bypass the set_author
   || method.
   */
 PROCEDURE set_one_author (doc_id_in IN INTEGER,
      new_author_in IN VARCHAR2) IS
   BEGIN
      UPDATE appraisals a
         SET a = a.set_author(new_author_in)
       WHERE doc_id = doc_id_in;
   END set_one_author;

   PROCEDURE set_one_author (doc_in IN Doc_t,
      new_author_in IN VARCHAR2) IS
   BEGIN
      UPDATE appraisals a
         SET a = a.set_author(new_author_in)
       WHERE doc_id = doc_in.doc_id;
   END set_one_author;

   PROCEDURE set_one_author (docref_in IN REF Doc_t,
      new_author_in IN VARCHAR2) IS
   BEGIN
      UPDATE appraisals a
         SET a = a.set_author(new_author_in)
       WHERE REF(a) = docref_in;
   END set_one_author;

   PROCEDURE delete_one (doc_id_in INTEGER) IS
   BEGIN
      DELETE appraisals
       WHERE doc_id = doc_id_in;
   END delete_one;

   PROCEDURE delete_one (doc_in Doc_t) IS
   BEGIN
      DELETE appraisals
       WHERE doc_id = doc_in.doc_id;
   END delete_one;

   PROCEDURE delete_one (docref_in REF Doc_t) IS
   BEGIN
      DELETE appraisals a
       WHERE REF(a) = docref_in;
   END delete_one;

END manage_appraisal;

An underlying assumption is that you want genuine encapsulation of the persistent data, and that you will grant users only EXECUTE privileges on the package and on the object type. You could further encapsulate the underlying data by declaring public REF CURSOR variables and creating associated procedures that will use them to open and fetch. See Chapter 6, Database Interaction and Cursors , for a discussion of how to do this.

18.5.5 Implications for Developer/2000

One of the challenges of encapsulating DML in packages is that the Oracle application development tools such as Developer/2000 assume that they have full use of SQL to navigate the database and manipulate tables. Oracle Forms' default block functionality relies heavily on this assumption.

Consistently using packages rather than Forms' default processing could force a lot of hand-coding of transactional triggers: on-insert, on-update, and on-delete. You will also need to hand-code your own on-lock trigger. (We haven't explicitly discussed locking in this chapter, but to support the Oracle Forms' transaction model, you would want to add methods or procedures to lock any persistent data that the user begins to change or delete.)

Using a PL/SQL container could be buying your future maintainability at the cost of immediate development productivity -- a tough trade-off!