 | Chapter 18 Object Types |  |
In this section we look more deeply into the constructs and concepts you will need in order to master to use objects in your applications. There are three different ways you can initialize an object:
Use the default constructor
Make a direct assignment
SELECT INTO or FETCH INTO
In addition, after an object is initialized, it can be stored in the database, and you can then locate and use that object using several new language constructs:
REF
VALUE
DEREF
The designers of the PL/SQL language have established a general convention that uninitialized variables are null.[12] Object variables are no exception; the term for this uninitialized object condition is "atomically null." Not only is the object null, but so are its individual attributes. To illustrate, let's take a trip back to the pet shop.
[12] One significant exception is the Version 2 table datatype, known as index-by tables in Version 3, which are non-null but empty when first declared. In PL/SQL8, uninitialized nested tables and uninitialized VARRAYs are, in fact, null
Since all pets need a home, we might want to create an address object type:
CREATE TYPE Address_t AS OBJECT( street VARCHAR2(40), city VARCHAR2(20), state VARCHAR2(10), country VARCHAR2(3) );
In the example below, notice that the object itself is null, as well as the object's attributes:
DECLARE cerberus_house Address_t; -- cerberus_house is not initialized here BEGIN IF cerberus_house IS NULL ... -- will evaluate to TRUE IF cerberus_house.street IS NULL... -- also TRUE
The nullity of the elements in PL/SQL follows somewhat unpredictable rules; uninitialized RECORD variables have null elements (as with objects), but uninitialized collections have elements whose nullity is not defined. As with collections, when an object is null, you cannot simply assign values to its attributes; if you do, PL/SQL will raise an exception. Before assigning values to the attributes, you must initialize the entire object.
Let's turn now to the three different ways a PL/SQL program can initialize an object.
A constructor is a special method that allows the creation of an object from an object type. Invoking a constructor is a way to instantiate (create) an object. In Oracle 8.0, each object has a single default constructor that the programmer cannot alter or supplement.
The default constructor:
Has the same name as the object type
Is a function rather than a procedure
Accepts attributes in named or positional notation
Returns an object
Must be called with a value, or the non-value NULL, for every attribute; there is no DEFAULT clause for object attributes
Notice how the name of the constructor matches the name of the object type, which may look odd at first glance (unless you're already an object-oriented programmer). The following declaration assigns an initial value to the cerberus_house object:
DECLARE
cerberus_house Address_t := Address_t('123 Main', 'AnyTown', 'TX', 'USA');When assigning one object to another, you create a new object that starts life as a copy of the original. In the following example, minotaurs_labyrinth gets initialized using direct assignment.
DECLARE
cerberus_house Address_t := Address_t('123 Main', 'AnyTown', 'TX', 'USA');
minotaurs_labyrinth Address_t;
BEGIN
minotaurs_labyrinth := cerberus_house;
END;The attributes of the two addresses start out identical, but subsequent modifications to one do not automatically apply to the other.
Assuming that there is a "houses" table of Address_t objects, we can use a SELECT statement to retrieve from the database into a PL/SQL object. PL/SQL provides the VALUE keyword (described below) to retrieve the contents of the entire object:
DECLARE
troubles_house Address_t;
CURSOR h_cur IS
SELECT VALUE(h)
FROM houses h
WHERE resident_cat = 'TROUBLE';
BEGIN
OPEN h_cur;
FETCH h_cur INTO troubles_house;
... If your program attempts to assign a value to an attribute of an uninitialized object, PL/SQL will raise the predefined exception ACCESS_INTO_NULL:
DECLARE our_house Address_t; -- not initialized BEGIN our_house.street := '123 Main'; -- raises ACCESS_INTO_NULL END;
While seeming quite reasonable, this kind of an assignment will clearly not achieve the desired result. It bears repeating: always initialize your objects!
The Oracle objects option provides an initially bewildering set of constructs for locating and referring to persistent objects. Getting to know them may take some time, but understanding them will be essential to "doing objects right." Table 18.3 summarizes these schemes and the following sections look at them in more detail.
| Scheme | Description | Applications |
|---|---|---|
| Object identifier (OID) | An opaque, globally unique handle, produced when the object is stored in the database as a table (row) object. | This is the persistent object's handle; it's what REFs point to. Your program never uses it directly. |
| VALUE | An operator. In SQL it acts on an object in an object table and returns the object's "contents." Do not confuse this keyword with the VALUES keyword that appears in the INSERT statement. | Used when fetching a table (row) object into a variable, or when you need to refer to an object table as an object instead of a list of columns. |
| REF | A pointer to an object. May be used within a SQL statement as an operator, or in a declaration as a type modifier. | Allows quasi-"normalizing" of object-relational databases and "joining" of object tables using "dot navigation." In PL/SQL, REFs serve as input/output variables. |
| DEREF | Reverse pointer lookup for REFs. | Helpful for retrieving the contents of an object when all you know is its REF. |
Have you ever used an arbitrary number (maybe an Oracle sequence) as a table's primary key? The benefits are many -- chief among them that you can often hide it from the users and never have to worry about them wanting to change the key value! Object identifiers are a lot like your arbitrary numbers, except that they are assigned by Oracle. When you create a table of objects, Oracle adds a hidden field that will hold the object identifier for each object. Oracle also automatically creates a unique index on this column. When you insert an object into the table, Oracle automatically assigns the object a rather large but hidden object identifier (OID). The OID is:
Opaque. Although your programs can indirectly use the OID, you don't typically see its value.
Potentially globally unique across databases. The OID space makes provisions for up to 2128 objects (definitely "many" by the reckoning of the Hottentots).[13]
[13] Oracle could one day use an OS-dependent function to make OIDs globally unique; that way, no two OIDs could have the same value, even on different machines that are configured identically. Perhaps object navigation will be possible without database links. (In case you're wondering, the Hottentots had a four-valued counting system: 1, 2, 3, and "many.")
Capable of being synthesized from a primary key so that objects can be retrofitted onto relational schema using object views.
Preserved after export/import by the owner, unlike ROWIDs. This could allow objects to be distributable across databases in the future (contrast with ROWIDs, which are tied to a particular database)..
In addition, unless you are using primary key-based OIDs in object views, OIDs are immutable. That is, even if you want to change the binary value of an OID, you can't do it unless you delete and recreate the object, at which point Oracle will assign a new OID.
Not all objects have an object identifier. In particular, objects stored in PL/SQL variables lack a referenceable OID, as do column objects. A column object only "makes sense" within the context of its row, and the row will have other means of unique identification. Implementors must sometimes choose between embedding an object and making it referenceable.[14]
[14] This approach is 180 degrees off from relational industry experts who assert that OIDs should not be used for row identification, and that only column objects should have OIDs. See Hugh Darwen and C. J. Date, "The Third Manifesto," SIGMOD Record, Volume 24 Number 1, March 1995.
Oracle8 "reference" datatypes are destined to cause more than a few knitted brows in the Oracle user community. The confusion starts with the fact that REF has two different yet related meanings, depending on context. Toss in the fact that some objects have REFs and some don't. It's best to invest a little extra time early on to understand REFs if you want to avoid increasing your gray hair count (or, in my case, the size of my forehead).
The main reason that the reference concept is so critical is that REFs are the best way of uniquely referring to object instances. REFs are the way that we "see" object identifiers. REFs are the basis of object relationships and object "joins."
In a SQL statement, when you need to retrieve a table object's unique identifier, you will use REF. In this case, REF operates on a row object, accepting as its argument a table alias (also known as a correlation variable ). As hinted earlier, REF cannot operate on column objects or otherwise nested objects, because such objects do not have an OID. REFs are constructed from (but are not identical to) OIDs; only objects with OIDs get to have REFs pointing to them.
Syntactically, to retrieve a pointer from a table of objects, you will use:
REF(table_alias_name)
as in
SELECT REF(p) FROM pets p -- uses table alias "p" WHERE ...
While you can choose any unambiguous SQL identifier for the table alias, a short alias is generally more readable. In most cases in this book, we use a single letter.
But retrieving a REF is not terribly useful in and of itself unless you happen to like looking at long hex strings. More typically, REFs are used like a foreign key. To assign a value to a REF field, we must first retrieve the value from the object table:
DECLARE
person_ref REF Person_t;
CURSOR pref_cur IS
SELECT REF(p)
FROM persons p
WHERE last_name = 'RADCLIFF';
BEGIN
OPEN pref_cur;
FETCH pref_cur INTO person_ref;
CLOSE pref_cur;
INSERT INTO pets VALUES (Pet_t(10234, 'Wally', 'Blue whale',
'M', null, null, person_ref));
END;Or, more concisely:
INSERT INTO pets
SELECT Pet_t(10234, 'Wally', 'Blue whale',
'M', null, null, REF(per))
FROM persons per
WHERE last_name = 'RADCLIFF';Then, after your data is loaded, you could retrieve an attribute or member function of the referenced object via a join.
SELECT p.tag_no, per.full_name()
FROM pets p,
persons per
WHERE p.owner_ref = REF(per);But wouldn't you be happier using Oracle's ability to traverse REFs automatically?
SELECT tag_no, p.owner_ref.full_name() -- cool! FROM pets p;
This illustration (which does work, by the way) shows how Oracle SQL elegantly supports object navigation across REFs, something not directly allowed in PL/SQL. This is some of "the neat stuff" that the object extensions provide. Most people will find this chained dot nomenclature much more intuitive and easier to maintain over the long run than the equivalent explicit join.
By the way, the two versions of this "join" are not exactly identical. The first, with the explicit join, performs an "equi-join," which means that if the owner_ref column is null or dangling, the record (object) will not appear in the result set. However, the second, with dot navigation, performs an "outer join," meaning that a null or dangling owner_ref will simply cause the full_name field to show up null.
NOTE: REFs are not foreign keys. As previously discussed and as illustrated in Table 18.2, the differences between REFs and foreign keys are significant. You will need to give some thought to how you are going to prevent dangling REFs.
To hold a REF in a local variable, declare the variable of type REF object_name, and assign it via fetch or assignment from another REF that points to the same type. This example of REF as a "type modifier" shows that you can assign REFs using fetches and direct assignment, as you would expect.
DECLARE
pet_ref REF Pet_t;
hold_pet_ref REF Pet_t;
BEGIN
-- example of assignment via fetch
SELECT REF(p) INTO pet_ref
FROM pets p
WHERE...
-- example of direct assignment
hold_pet_ref := pet_ref;What about local object type variables? At first blush, it might seem that you should be able to do something like the following:
DECLARE
our_house Address_t := Address_t('123 Main','AnyTown','TX','USA');
house_ref REF Address_t;
BEGIN
house_ref := REF(our_house); -- invalidYou can't get the REF to an object variable which exists only in a PL/SQL program. REFs are constructed from an object's OID, and transient objects don't have such a pointer.
If they are so much trouble, what good are REFs? As mentioned earlier, REFs are the only supported way of getting at OIDs. And despite the dangling REF problem, if you want to "normalize" an object-oriented design so that objects can be shared, you will have to use REFs. In addition, a REF is an efficient and lightweight means of passing object information as a parameter. That is, if you pass only the pointer, you avoid the overhead of allocating memory for a copy of the object contents. Be aware that passing a REF can allow the called program to change the object's contents, something you may or may not intend.
Like REF, the VALUE operator also accepts a table alias as its argument. However, VALUE retrieves the value of an object (for example, to create a copy of it) via SQL.
To understand what VALUE does, first consider what happens if you apply pre-Oracle8 techniques to an object table:
DECLARE
CURSOR h_cur IS
SELECT *
FROM houses; -- houses is an object table
their_house h_cur%ROWTYPE;
BEGIN
OPEN h_cur;
FETCH h_cur INTO their_house;These non-object calls work fine even though "houses" is an object table. This is one demonstration of the relational side of an "object-relational database." But their_house is a record variable, not an object.[15] If you later wanted to take advantage of objects in PL/SQL, your code would be ill-prepared.
[15] If you wanted an object variable built from the their_house record variable, you could declare the variable of type Address_t, and initialize it, using the Address_t constructor, from the elements in their_house.
To use a local variable that has been typed as an object, you must declare it to be of the same datatype on which you have defined the table object, and you must use the VALUE operator:
DECLARE
some_house Address_t;
CURSOR h_cur IS
SELECT VALUE(h)
FROM houses h;
BEGIN
OPEN h_cur;
FETCH h_cur INTO some_house;
-- Attributes are available using dot notation
IF some_house.city IS NULL THEN ...This code begs the question: What is the difference between the "value" of an object and the object itself? Why is VALUE necessary at all?
Without VALUE, the retrieval of data in object tables would be ambiguous. You therefore have to tell Oracle whether you want the attributes or the whole object. SELECTing a table object without the VALUE operator retrieves the attributes of the object, while using the VALUE retrieves the entire object as an object.
Omitting VALUE fails if we try to fetch columns directly into an object variable:
DECLARE
some_house Address_t;
CURSOR h_cur IS
SELECT *
FROM houses;
BEGIN
OPEN h_cur;
FETCH h_cur INTO some_house; --invalid; type mismatch
...It's worth pointing out that even if we fetch an object as an object from the database, we still can't get to the REF from the local object variable. This is unfortunate. In other words, I would like the following to be possible:
DECLARE
some_house Address_t;
some_house_ref REF Address_t;
CURSOR h_cur IS
SELECT VALUE(h)
FROM houses h;
BEGIN
OPEN h_cur;
FETCH h_cur INTO some_house;
some_house_ref := REF(some_house); -- invalidPerhaps Oracle will consider adding this functionality to a future release. Until then, the workaround is simple enough:
DECLARE
some_house Address_t;
some_house_ref REF Address_t;
CURSOR h_cur IS
SELECT VALUE(h), REF(h)
FROM houses h;
BEGIN
OPEN h_cur;
FETCH h_cur INTO some_house, some_house_ref;
CLOSE h_cur;
END;NOTE: VALUE does not apply to column objects, since retrieving a column object unambiguously retrieves an object value.
DEREF is the "dereference" operator. Like VALUE, it returns the value of an object; unlike VALUE, DEREF's input is a REF to an object. That is, if you have a REF column in a table and you want to retrieve the target instead of the pointer, you use DEREF. It "un-does" a REF. Consider the following example which, as we noted earlier, fails to compile:
DECLARE
the_dalmatian Pet_t;
BEGIN
SELECT VALUE(p) INTO the_dalmatian
FROM pets p
WHERE name = 'Cerberus';
IF the_dalmatian.owner_ref.first_name = 'Persephone' -- invalid
THEN...This can be "fixed" using DEREF as follows:
DECLARE
the_owner Person_t;
BEGIN
SELECT DEREF(owner_ref) INTO the_owner
FROM pets
WHERE name = 'Cerberus';
IF the_owner.first_name = 'Persephone'
THEN... |  | |
| 18.3 Syntax for Creating Object Types |  | 18.5 Modifying Persistent Objects |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.