DataCraft -> Publications -> PL/SQL 8 Objects paper

Making Sense of Object Technology in PL/SQL 8

Introduction

The Oracle objects option, available starting with Oracle8, includes three major new categories of constructs of which PL/SQL programmers will want to be aware:

• Object types--a means of creating complex user-defined datatypes and defining object "methods" that can operate on those types. They are "complex" in that they can consist of more than one attributes and can implement the concept of abstract datatypes.
• Nested tables and VARRAYs--new collection datatypes, similar to Oracle7’s PL/SQL table datatype, which can now be used to store non-atomic data in a table column.
• Object views--a way of retrofitting object types and collection types onto a relational system.

Why are Objects Cool?

The question of why objects are a good thing can incite arguments of religious intensity. Perhaps less controversially, the main forces behind using objects can be summarized as follows:

Object Types

An object type is an Oracle database construct, managed via DDL extensions, that defines a data structure (attributes) and the legal operations (methods) on the attributes. The type is only a template and holds no data itself; you may create variables, tables, columns, and other constructs of this type. If you are familiar with object terminology, note that an object type is the closest thing to a class. It is also very similar to an abstract data type.

An object is an instance of an Oracle8 object type. The object is the place where the actual data resides. Objects can be stored within tables and in such cases, they are persistent, or they may exist only temporarily in PL/SQL variables.

An attribute is a structural part of an Oracle object, roughly akin to a column in a table. Each attribute must be of a single datatype, either scalar, like VARCHAR2 or INTEGER, or composite, like a user-defined nested table or another (nested) object. Scalar attributes are sometimes called simple, and composite attributes may be referred to as complex.

A method is a procedure or function, usually implemented in PL/SQL, that (typically) operates on an object’s attributes. The methods for an object can only be invoked in the context of a specific object of that type. Methods can also be implemented in C and can be invoked as an Oracle "external procedure." There is a special default method supplied by Oracle, a constructor, that initializes objects.
 

Some Simple Examples

CREATE TYPE Pet_t AS OBJECT (
   tag_no INTEGER, 
   name VARCHAR2(60), 
   MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t 
);

CREATE TYPE BODY Pet_t AS 
   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; 
END;

Row Objects

CREATE TABLE pets OF Pet_t;

After creating the object table called pets, we can create an object (object instance) of type Pet_t using the default constructor, which is a special method that Oracle supplies automatically when you create a user-defined type. The constructor takes the same name as the object type and accepts one argument per attribute defined in the type.

INSERT INTO pets VALUES (Pet_t(23052, 'Mambo'));

INSERT INTO pets VALUES (23052, 'Mambo');

Column Objects

CREATE TABLE families ( 
   surname VARCHAR2(50),
   favorite_pet Pet_t, 
   address Address_t);

Transient Objects

DECLARE 
   my_pet Pet_t := Pet_t(23052, 'Mambo');

CREATE FUNCTION find_pet (the_tag_no IN NUMBER) 
   RETURN Pet_t 
IS 
   the_pet Pet_t; 

   CURSOR pet_cur IS 
      SELECT VALUE(p)
        FROM pets P 
      WHERE tag_no = the_tag_no; 

BEGIN 
   OPEN pet_cur; 
   FETCH pet_cur INTO the_pet; 
   CLOSE pet_cur; 
   RETURN the_pet; 
END;

Comparison: Oracle8 Objects and Earlier Features

An object type is a bit like a package that contains only type declarations and functions that operate on those types. This is especially true since the object type, like the package, can have a separate " body" section in which to implement its procedures and functions (methods). There are key differences, though; perhaps most significantly, code in the object type body can only be invoked on a particular object. That is, you cannot call a method unless you also indicate an object instance on which to apply it. In addition, you can’t create a table based on a package specification, the way you can create a table from an object type; and object types cannot include constants, exceptions, cursors, or datatypes. The first table compares the new object features with features of tables and packages.

Characteristic	Oracle7 Table	Oracle7 Package	Oracle8 Object
Stores data	Yes	Temporary only; package variables exist for duration of session	Object instance data may be persistent (stored in tables) or transient (stored in variables)
Serves as a template	No	No	Object types serve as a template for object instances
May contain complex data	Yes (requires Objects Option installed)	Yes; some datatypes such as RECORD and TABLE types do not require the Objects Option	Yes
Contains procedural code	No (except for table triggers)	Yes	The code is in the object type definition,  but can be invoked only on a specific instance
Has a body separate from its specification	N/A (in the case of triggers, the answer is no)	Yes	Yes (object type definition has separate body  for method implementation)
May expose constants, exceptions, cursors, or datatypes	N/A (in the case of triggers, the answer is no)	Yes	No
Rights Model (see "Privileges" section)	Owner must explicitly grant DML privileges on table to user or role	If owner grants EXECUTE to invoker, latter inherits owner’s DML privileges	Currently, if owner grants EXECUTE to invoker, latter  inherits owner's DML privileges

 

Strategies for Using Packages & Object Types

1. Permit full use of conventional SELECT, INSERT, UPDATE, and DELETE statements on your persistent objects. Other than using complex data types, the objects option will, in this case, look a lot like conventional relational approaches. At this end of the spectrum, you don’t even have to define any methods...but you pay a price.

Recommendation: Avoid if possible, for the same reasons you should avoid excessive use of globals in your applications.
 

2. Permit limited use of conventional SQL, but invoke the constructor method in INSERT, and create various UPDATE methods which will be invoked in clauses of UPDATE statements. Use DELETE as above. This is a better way to go, since you can rely at least partially on the core logic you embed in the methods. However, you still rely on application programmers to invoke the methods properly.

Recommendation: Avoid if possible on the basis that it’s only a half-hearted approach.
 

3. Implement all data manipulations via methods, including all DML on persistent object tables (or at least make an attempt to do so). If you come from an object shop, this might be your preferred approach. This approach absolutely commits you to an object bias in your applications. In addition, it ties the object type to a particular implementation, which might limit reuse.

Recommendation: Worth a try, but watch out for schema evolution issues!
 

4. Design the object methods to avoid references to persistent object tables, instead acting only on the SELF object and on data exchanged via method arguments. Construct PL/SQL "container" packages to manage your persistent object tables (this is similar to what you could do in Oracle7), but code these packages to reuse logic that is localized in the object type definition. When a PL/SQL application needs to manipulate persistent data, it must call the package; when it simply needs to perform an operation on an object variable, it will typically invoke a method. Approach 4 has a number of advantages over approach 2 above; notably, it further increases the likelihood that application programmers will invoke the proper call in their code.

Recommendation: A good (but not great) strategy. However, there are no great strategies.
 

5. A fifth approach may make a great deal of sense once Oracle supports inheritance. It might be possible to implement persistent object types as subtypes of the corresponding transient object type. Doing so could potentially provide the benefits of encapsulation and reuse while circumventing difficult schema evolution problems. (That is, subtypes should be capable of specializing behavior of their supertypes so you don’t have to rebuild the entire dependency tree every time you make slight modifications in object specifications.)

Recommendation: Wait and see

Nested Tables and VARRAYs

To summarize the three types of collection that are available in Oracle8:

• PL/SQL tables are singly dimensioned, unbounded, sparse collection of homogeneous elements, and are available only in PL/SQL. These are now called index-by tables.
• Nested tables are also singly dimensioned, unbounded collections of homogenous elements. They are initially dense but can become sparse through deletions. Nested tables are available in both PL/SQL and the database (for example, as a column in a table).
• VARRAYs, like the other two collection types, are also singly dimensioned collections of homogenous elements. However, they are always bounded and never sparse. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved.

CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;

CREATE TABLE employees ( 
   id NUMBER, 
   name VARCHAR2(50), 
   dependents_ages Dependent_birthdate_t 
);

INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox',
   Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977',
                          '22-MAR-2021'));

Comparing Oracle8 Collection Types

They both bear some resemblance to the PL/SQL Version 2 table data type, the elder member of the "collection" family. The new types are also singly-dimensioned arrays, but differ in areas such as sparseness (not exactly), how they’re initialized (via a constructor) and whether they can be null (yes).

One chief difference between nested tables and VARRAYs surfaces when using them as column datatypes. Although using a VARRAY as a column’s datatype can achieve much the same result as a nested table, VARRAY data must be pre-declared of a maximum size, and is actually stored "in-line" with the rest of the table’s data.

Data in nested tables, by contrast, are stored in special auxiliary tables called store tables, and there is no pre-set limit on how large they can grow. For this reason, Oracle says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays.

The old V2 table datatype, a special case of the new nested table type, is now called an index-by table, referring to its unique requirement of being indexed by a binary integer. Despite the many benefits of the new collection types, index-by tables have one important unique feature: initial sparseness. The second table provides illustrates many of the additional differences among index-by tables and the new collection types.
 

Characteristic	index-by table	Nested Table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL	No	Yes	Yes
Usable as column datatype in a table	No	Yes; data stored "out of line" (in separate table)	Yes; data stored "in line" (in same table)
Uninitialized state	Empty (cannot be null); elements undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment
In PL/SQL, elements referenced via	BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647)	Positive integer between 1 and 2,147,483,647	Positive integer between 1 and 2,147,483,647
Sparse?	Yes	Initially, no; after deletions, yes	No
Bounded?	No	Can be extended	Yes
Subscript values are restricted?	No; any value okay to use	yes; Oracle assigns subscripts	Same as nested tables, but subscripts outside limit will raise exception
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense), with no predefine maximum	EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality	No	No	No
Retain ordering and subscripts when stored in database	N/A	No	Yes

 

Strategies for Collections

Which collection type should I use?

If you want to preserve the order of elements that get stored in the collection column, and your dataset will be "small," use a VARRAY. What is "small?" I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.

Other indications that a VARRAY would be appropriate: you don’t want to worry about deletions occurring in the middle of the dataset; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.

If you need sparse PL/SQL tables, say, for "data-smart" storage, your only practical option is an index-by table. True, you could allocate and then delete elements of a nested table variable, but it is inefficient to do so for anything but the smallest collections.

If your PL/SQL program needs to run under both Oracle7 and Oracle8, again, you have only one option: index-by collections. Or, if your PL/SQL application requires negative subscripts, you have to use index-by tables.
 

Exploit Retrieval & Storage Benefits

Using the CAST and MULTISET operators, you can realize these benefits even with relational tables.
 

Object Views

Object View Example

Our legacy system has one table for image metadata:

CREATE TABLE images ( 
   image_id INTEGER NOT NULL, 
   file_name VARCHAR2(512), 
   file_type VARCHAR2(12), 
   bytes INTEGER, 
   CONSTRAINT image_pk PRIMARY KEY (image_id));

CREATE TABLE keywords ( 
   image_id INTEGER NOT NULL, 
   keyword VARCHAR2(45) NOT NULL, 
   CONSTRAINT keywords_pk PRIMARY KEY (image_id, keyword), 
   CONSTRAINT keywords_for_image FOREIGN KEY (image_id) 
   REFERENCES images (image_id));

Before we can create the top-level type, we will first define a collection to hold the keywords.

CREATE TYPE Keyword_tab_t AS TABLE OF VARCHAR2(45);

CREATE TYPE Image_t AS OBJECT ( 
   image_id INTEGER, 
   file_name VARCHAR2(512), 
   file_type VARCHAR2(12), 
   bytes INTEGER, 
   keywords Keyword_tab_t,



   MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2, 
      new_file_type IN VARCHAR2, new_bytes IN INTEGER) 
   RETURN Image_t,


   MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t) 
   RETURN Image_t,

   PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS, WNDS, RNPS, WNPS) 
);

CREATE TYPE BODY Image_t 
AS 
   MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2, 
      new_file_type IN VARCHAR2, new_bytes IN INTEGER) 
      RETURN Image_t 
   IS 
      image_holder Image_t := SELF; 
   BEGIN 
      image_holder.file_name := new_file_name; 
      image_holder.file_type := new_file_type; 
      image_holder.bytes := new_bytes; 

      RETURN image_holder; 
   END;

   MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t) 
   RETURN Image_t 
   IS 
      image_holder Image_t := SELF; 
   BEGIN 
      image_holder.keywords := new_keywords; 

      RETURN image_holder; 
   END; 

END;

Finally, to create the object view, we use the following statement:

CREATE VIEW images_v 
   OF Image_t 
   WITH OBJECT OID (image_id) 
AS 
   SELECT i.image_id, i.file_name, i.file_type, i.bytes, 
          CAST (MULTISET (SELECT keyword 
                            FROM keywords k 
                           WHERE k.image_id = i.image_id) 
            AS Keyword_tab_t) 
     FROM images i;

Interestingly, there are only a couple of components of this statement that are unique to object views: OF Image_t means the view will return objects of type Image_t. WITH OBJECT OID (image_id) allows you to designate the primary key as the basis of a virtual OID. This allows you to create REFs to virtual objects.

The CAST... clause shown above can be used in any view, not just object views (but it does require the presence of the Oracle objects option). This subquery performs an "on-the-fly" conversion of the detail records into a collection type. (For more information about and examples of the CAST and MULTISET operators, see [Feuer97].)
 

Differences Between Object Views and Object Tables

• Uniqueness of object identifiers. Whereas object tables always have unique OIDs, it is possible to create object views which ccntain duplicate virtual OIDs. Object views may also duplicate the OIDs of other object views or object tables.
• Use of REFs. As with an object table, it is possible to use a REF to point to an instance of an object view. However, these REFs must be constructed from foreign keys with the special built-in MAKE_REF, which turns out to be a rather finicky function. In particular, if your foreign key value is null, MAKE_REF returns an error!
• Storage of REFs. Object tables can store REF values persistently, but REFs to virtual objects in an object view must be constructed dynamically (with MAKE_REF).
• REFs to Non-Unique OIDs. It is impossible in an object table to have a REF to a non-unique OID; but an object view which has such a REF may have this problem. You have to consider it and deal with it if necessary.

Strategies for Object Views

Assessment

It may not be obvious from our examples just how the objects option is going to facilitate reuse, particularly given Oracle 8.0’s lack of inheritance and difficulties with schema evolution. Indeed, the benefits of an object approach do not automatically accrue to the practitioner; large systems, in particular, must exhibit other characteristics [Booch94]. Achieving reuse requires careful planning and deliberate execution.

Experts recommend not attempting object approaches just because someone says they are cool or because everyone else is doing it. Without a financial and time commitment to understanding and without taking advantage of a different programming model, you are not likely to get much benefit, and yours will join the landscape of projects that didn’t deliver.

Almost certainly, Oracle Corporation will be adding needed features such as inheritance and schema evolution tools to their objects option. One day, objects will probably be a standard part of the server. Until the technology matures, early adopters will enjoy the pleasures of finding workarounds, and will gain a deeper appreciation of features that appear later in the product.
 

Acknowledgments

References

[Jacob96] Ivar Jacobson. "Reuse in Reality: The Reuse-Driven Software-Engineering Business." Presented at Object Expo Paris.

[Feuer97] Steven Feuerstein with Bill Pribyl. Oracle PL/SQL Programming, Second Edition, O’Reilly & Associates, 1997. (You definitely want the latest edition of this book.)

Last modified September 27, 2002 5:22 PM .