DataCraft, Inc.
DataCraft logo
DataCraft -> Publications -> PL/SQL 8 Objects paper

Making Sense of Object Technology in PL/SQL 8

Includes Excerpts From Oracle PL/SQL Programming, Second Edition
As Presented at Oracle Open World 1997

Bill Pribyl, DataCraft, Inc.
Steven Feuerstein, PL/Solutions & RevealNet


Objects...methods...collections...REFs...casting...object views... Whoa! What is all this new stuff in PL/SQL? Whatís it all good for? How do these new features compare with the old? If I abandon my old ways and throw my eggs in the object basket, will it fix my lunch...or eat my lunch? These are a few of the questions we touch on in this paper. We even have answers to some of them!

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:

While all three of these can have great benefit to new applications, the third, object views, will have particular appeal to Oracle shops migrating their tools and methods to object technology. Weíll take a look at each and how it fits in with other Oracle constructs.

Why are Objects Cool?

Object technology implements the idea that both the model and the implementation should be derived from "things" rather than "processes." This theme should find little argument among database professionals, who have long taken a data-centric view of the world. As something of a twist, though, object approaches emphasize co-locating the thingís behavior with its data. A major theme of object-oriented analysis, design, and programming is that these techniques should help us craft applications that more naturally address the problem space and which possess virtues that contribute toward code reuse.

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 technology, properly applied, can enhance productivity and reliability while reducing lifecycle cost and complexity.
  • With the growing popularity of C++ and particularly Java, object technology has arrived in the mainstream of computer programming, and conventional databases are due for a new model.
  • Some applications require complex data structures that do not map intuitively or efficiently to rows and columns, and objects provide non-scalar structures in which to contain and move such data.
  • Since relational and object models suffer an "impedance mismatch," using an object-oriented programming language with a relational database has historically been problematic at best.
  • Object Types

    The first big hurdle to cross is the nomenclature. For example, even prior to Oracle8, Oracle did have objects--that is, tables, indexes, packages, procedures, etc.--in fact, you can see them all in the USER_OBJECTS view. Now we have something most precisely called object types, which can have object instances, the latter of which are referred to simply as objects. Confusing, isnít it?

    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

    Letís look at some short code samples that use objects. First, letís define an object type.
       tag_no INTEGER, 
       name VARCHAR2(60), 
       MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t 
    This object type has two attributes, tag_no and name, and one method, set_tag_no. We also need to create an "object type body" to supply the body of the method.
       MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) 
    RETURN Pet_t
            the_pet Pet_t := SELF; -- initialize to "current" object     BEGIN        the_pet.tag_no := new_tag_no;        RETURN the_pet;     END;  END;
    Using this object type, here are some code fragments illustrating different applications of the type.

    Row Objects

    First, an object can be the datatype of each of the rows in a table. The table is then referred to as an object table, and it contains row objects; that is, each row is an object instance.
    CREATE TABLE pets OF Pet_t;
    An object table like pets has a special hidden column called an object identifier or OID. This identifier is globally unique across not only tables but also databases! While OIDs are opaque to the programmer, it is possible to store in other tables references which point to a row object. These pointers are called REFs and functionally behave similarly to foreign keys.

    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'));
    Since Oracle8 is an "object-relational" DBMS, it is also possible to perform the same insert using the familiar syntax,
    INSERT INTO pets VALUES (23052, 'Mambo');

    Column Objects

    An object type can also be the datatype of a column in a table. The column is then said to contain column objects. Different columns in a table could be of different object types. (The example below also uses Address_t, a object assumed already defined. Curiously enough, it contains attributes and methods appropriate to street addresses.)
    CREATE TABLE families ( 
       surname VARCHAR2(50),
       favorite_pet Pet_t, 
       address Address_t);

    Transient Objects

    An object can be the datatype of a local variable. Here, we declare and initialize an object variable in one statement. The initialization uses the automatically available constructor which has the same name as the datatype.
       my_pet Pet_t := Pet_t(23052, 'Mambo');
    An object can be the datatype of a PL/SQL formal parameter. Functions may also return object types, as the example shows. The VALUE operator is needed to retrieve a table object.
    CREATE FUNCTION find_pet (the_tag_no IN NUMBER) 
       RETURN Pet_t 
       the_pet Pet_t; 
       CURSOR pet_cur IS 
          SELECT VALUE(p)
            FROM pets P 
          WHERE tag_no = the_tag_no; 
       OPEN pet_cur; 
       FETCH pet_cur INTO the_pet; 
       CLOSE pet_cur; 
       RETURN the_pet; 

    Comparison: Oracle8 Objects and Earlier Features

    Object types in Oracle8 live inside the Oracle database rather than inside PL/SQL programs. That is, you must issue the CREATE TYPE Ö AS OBJECT statement outside of PL/SQL if you want to use the type within PL/SQL.

    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

    There are at least five different ways you can combine packages and 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.
    2. Recommendation: Avoid if possible, for the same reasons you should avoid excessive use of globals in your applications.

    3. 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.
    4. Recommendation: Avoid if possible on the basis that itís only a half-hearted approach.

    5. 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.
    6. Recommendation: Worth a try, but watch out for schema evolution issues!

    7. 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.
    8. Recommendation: A good (but not great) strategy. However, there are no great strategies.

    9. 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.)
    10. Recommendation: Wait and see

    Nested Tables and VARRAYs

    In PL/SQL Version 2, Oracle introduced the TABLE datatype as a way of storing singly dimensioned sparse arrays in PL/SQL. Known as the "PL/SQL table," this structure is thoroughly documented in many sources, particularly [Feuer97]. With Oracle8, the Objects Option introduces two new "collection" structures that have a wide range of new uses. These structures are nested tables and variable-size arrays (VARRAYs). Like PL/SQL tables, the new structures can also be used in PL/SQL programs. But what is dramatic and new is the ability to use the new collections as the datatypes of fields in conventional tables and attributes of objects. While not an exhaustive implementation of user-defined datatypes, collections offer rich new physical (and, by extension, logical) design opportunities for Oracle practitioners.

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

    Using a nested table or VARRAY, you can store and retrieve non-atomic data in a single column. For example, the employee table used by the HR department could store the date of birth for each employeeís dependents in a single column. Itís not terribly difficult to create such a table. First we define the collection type:
    CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;
    Now we can use it in the table definition:
    CREATE TABLE employees ( 
       id NUMBER, 
       name VARCHAR2(50), 
       dependents_ages Dependent_birthdate_t 
    We can populate this table using the following INSERT syntax, which relies on the typeís default constructor to transform a list of dates into value of the proper datatype:
    INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox',
       Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977',
    Oracle8 collections also provide a dramatic advantage from an application programmerís perspective: you can pass an entire collection between the database and PL/SQL using a single fetch. This feature alone could have significant positive impact on application performance.

    Comparing Oracle8 Collection Types

    Within PL/SQL, both nested tables and VARRAYs are an ordered collection of homogenous elements.

    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

    There are two main strategies here: first, identifying which of the three collection types are appropriate to a given problem; and two, realizing other benefits of collections in your applications.

    Which collection type should I use?

    If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle will then use a separate table behind the scenes to hold the collection data, so you can have almost limitless growth.

    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

    One of the great things about collections that we havenít mentioned yet is PL/SQLís ability to fetch and store them in a single trip to the database. Without collections, even by fetching from a join of master and detail tables, multiple fetches are always required to retrieve multiple rows. By retrieving and storing a collection type, you can not only reduce network round trips but also make your programs more concise.

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

    Object Views

    Although Oracleís object extensions offer rich possibilities for design of new systems, few Oracle shops with large relational databases in place will want or be able to completely re-engineer those systems to use objects. In order to allow established applications to take advantage of these new features over time, Oracle8 provides object views. With object views, you can achieve the following benefits:
  • Efficiency of object access. In PL/SQL, and particularly in Oracle Call Interface (OCI) applications, object programming constructs provide for convenient retrieval, caching, and updating of object data. These programming facilities can provide performance improvements, with the added benefit that application code can be more succinct.
  • Ability to navigate using REFs. By designating unique identifiers as the basis of an object identifier (OID), you can reap the benefits of object navigation. or example, you can retrieve attributes from related "virtual objects" using dot notation rather than explicit joins.
  • Easier schema evolution. In early versions of Oracle8, a pure object approach renders almost any kind of schema change at best ugly. In contrast, object views offer more ways that you can change both table structure and object type definitions of an existing system.
  • Consistency with new object-based applications. If you need to extend the design of a legacy database, the new components can be implemented in object tables; new object-oriented applications requiring access to existing data can employ a consistent programming model. Legacy applications can continue to work without modification.
  • Other new features of Oracle can improve the expressiveness of any type of view, not just object views. Two features which are not strictly limited to object views are collections and "INSTEAD OF" triggers. Consider two relational tables with a simple master-detail relationship. Using the Oracle objects option, you can portray the detail records as a single non-scalar attribute (collection) of the master, which could be a very useful abstraction. In addition, using INSTEAD OF triggers, you can tell Oracle exactly how to perform inserts, updates, and deletes on any view. These two features are available to both object views and non-object views.

    Object View Example

    In this example, we look at how object views might be used at a fictitious firm that designs web sites. Their existing relational application tracks JPEG, GIF, and other images that they use when designing client web sites. These images are stored in files, but data about them are stored in relational tables. To help the graphic artists locate the right image, each image has one or more associated keywords, stored in a straightforward master-detail relationship.

    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));
    ...and one table for the keywords associated with the images:
    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));
    To create a more useful abstraction, weíve decided to logically merge these two tables into a single object view. In order to do so, we must first create an object type with appropriate attributes. Since there are usually only a few keywords for a given image, this relationship lends itself to using an Oracle collection to hold the keywords.

    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);
    From here, itís a simple matter to define the object type. To keep the example short, weíll define only a couple of methods. In the following object type specification, notice that the keywords attribute is defined on the Keyword_tab_t collection type.
       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,
    Here is the body:
       MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2, 
          new_file_type IN VARCHAR2, new_bytes IN INTEGER) 
          RETURN Image_t 
          image_holder Image_t := SELF; 
          image_holder.file_name := new_file_name; 
          image_holder.file_type := new_file_type; 
          image_holder.bytes := new_bytes; 
          RETURN image_holder; 
       MEMBER FUNCTION set_keywords (new_keywords IN Keyword_tab_t) 
       RETURN Image_t 
          image_holder Image_t := SELF; 
          image_holder.keywords := new_keywords; 
          RETURN image_holder; 
    At this point there is no connection between the relational tables and the object type. They are independent organisms. It is when we build the object view that we "overlay" the object definition onto the tables.

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

    CREATE VIEW images_v 
       OF Image_t 
       WITH OBJECT OID (image_id) 
       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;
    (According to Oracle documentation, this statement is syntactically correct. Unfortunately, this statement will fail to compile in Oracle 8.0.3. The workaround is to define a packaged function that accepts an image_id and returns a collection of type Keyword_tab_t, and to use this function in the SELECT above. For more details, see Feuer97].)

    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

    In addition to the obvious difference between a view and a table, more subtle differences exist between an object view and an object table. Areas of difference include:

    Strategies for Object Views

    Here are some of the things that should guide your thinking about object views:
  • Because of their ability to tolerate schema changes, object views can provide advantages over conventional objects. What is unclear is the degree of performance impact they may cause (when compared against object tables).
  • Use care when defining the SELECT in an object view which uses MAKE_REF. You want to avoid the possibility of passing MAKE_REF a null key value.
  • Unless you have a very good reason to do otherwise, define the OID of an object view to be a unique value.
  • Adopt a consistent approach in the localization of DML on object views. INSTEAD OF triggers are cool, but triggers can exhibit confusing interactions with each other. Packages may still be the optimal construct in which to define insert, update, and delete logic.
  • Assessment

    This stuff isnít designed to be easy for the beginner, and the complexities are more than syntax-deep. In addition to the operational limitations we have discussed, the act of "thinking objects" is not a trait that comes naturally to programmers schooled in database or structured approaches. So what drives many organizations to consider object approaches in the first place? The overriding interest of managers seems to be their desire to reuse rather than reinvent the software needed to run their business[Jacob96]. In industries whose automation needs are not satisfied by off-the-shelf solutions, IS managers are continuously squeezed by the need to deliver more and more solutions while maintaining their legacy code, all while attempting to keep costs under control.

    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.


    The authors are indebted to Donald Herkimer at Oracle Corporation for his support during the preparation of the manuscript for this paper.


    [Booch94] Grady Booch. Object-Oriented Analysis and Design with Applications, Addison-Wesley, 1994. (You probably want the latest edition of this book)

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