18.4 Manipulating Objects in PLSQL and SQL

Chapter 18
Object Types
 

18.4 Manipulating Objects in PL/SQL and SQL

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

18.4.1 The Need to Initialize

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.

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 OID, VALUE, REF, and DEREF

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.


Table 18.3: Schemes for Referring to Persistent Objects

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.

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:

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.

Hidden Columns Exposed

The name of the column where Oracle8.0.3 stores object identifiers is SYS_NC_OID$. This column is "hidden" in that you won't see it when you "describe" the table in SQL*Plus, but it exists for every object instance (row) in an object table. It contains a 16-byte binary value; although this value is selectable from SQL*Plus, it should never be stored or manipulated in your programs. Oracle has hidden the OID to prevent hardcoding memory addresses into programs -- a dangerous practice in any environment. Moreover, the OID structure could change in future Oracle versions.

There's another hidden column, SYS_NC_ROWINFO$, which provides a representation of the constructor for the row object. The same caution applies -- it's interesting to look at, but do not rely on it in any applications.

Just to get an idea of what these columns look like, let's say we had a type foo_t which includes two attributes: a number and a collection of type bar_t:

CREATE TYPE bar_t AS VARRAY(5) OF VARCHAR2(10) / CREATE TYPE foo_t AS OBJECT (    id NUMBER,    bars bar_t) / CREATE TABLE foos OF foo_t; INSERT INTO foos VALUES (1, bar_t('apple','banana','cherry')); SELECT SYS_NC_OID$ FROM foos; SYS_NC_OID$ -------------------------------- 5661E312079811D19F35006097646884 SELECT SYS_NC_ROWINFO$ FROM foos; SYS_NC_ROWINFO$(ID, BARS) --------------------------------------------- FOO_T(1, BAR_T('apple', 'banana', 'cherry'))

Oracle provides constructs such as REF( ) and VALUE( ) so you don't need direct access to these hidden columns. In fact, you can get the constructor out of SYS_NC_ROWINFO$ in an Oracle-supported manner as follows:

SELECT VALUE(f) FROM foos f; VALUE(F)(ID, BARS) --------------------------------------------- FOO_T(1, BAR_T('apple', 'banana', 'cherry'))

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.2.1 REF as operator

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.

18.4.2.2.2 REF as type modifier

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

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

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


18.3 Syntax for Creating Object Types18.5 Modifying Persistent Objects

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.



Oracle PL/SQL Programming
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
ISBN: 0596514468
EAN: 2147483647
Year: 2004
Pages: 234
Authors: Steven Feuerstein, Bill Pribyl
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net