18.4.1.1 Constructors

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');

18.4.1.2 Direct assignment

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.

18.4.1.3 Assignment via FETCH (with SELECT)

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;


... 

18.4.1.4 ACCESS_INTO_NULL exception

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!

18.4.2.1 Object identifiers (OIDs)

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 2¹²⁸ 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.

18.4.2.2 REFs

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

18.4.2.3 VALUE

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);   --  invalid

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

18.4.2.4 DEREF

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