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:
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:
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
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.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 );
Using this object type, here are some code fragments illustrating different applications of the type.CREATE TYPE BODY Pet_t AS MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
IS the_pet Pet_t := SELF; -- initialize to "current" object BEGIN the_pet.tag_no := new_tag_no; RETURN the_pet; END; END;
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.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.
Since Oracle8 is an "object-relational" DBMS, it is also possible to perform the same insert using the familiar syntax,INSERT INTO pets VALUES (Pet_t(23052, 'Mambo'));
INSERT INTO pets VALUES (23052, 'Mambo');
CREATE TABLE families ( surname VARCHAR2(50), favorite_pet Pet_t, address Address_t);
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.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;
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|
Recommendation: Avoid if possible, for the same reasons you should avoid
excessive use of globals in your applications.
Recommendation: Avoid if possible on the basis that itís only a half-hearted
Recommendation: Worth a try, but watch out for schema evolution issues!
Recommendation: A good (but not great) strategy. However, there are
no great strategies.
Recommendation: Wait and see
To summarize the three types of collection that are available in Oracle8:
Now we can use it in the table definition:CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;
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:CREATE TABLE employees ( id NUMBER, name VARCHAR2(50), dependents_ages Dependent_birthdate_t );
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.INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021'));
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|
|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|
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.
Using the CAST and MULTISET operators, you can realize these benefits even with
Our legacy system has one table for image metadata:
...and one table for the keywords associated with the images: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));
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.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.
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.CREATE TYPE Keyword_tab_t AS TABLE OF VARCHAR2(45);
Here is the body: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) );
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.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:
(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].)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
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.
[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.)