Chapter 33: Advanced Object-Oriented Concepts s sThe object table itself is not mentioned in the pps

Chapter 33: Advanced Object-Oriented Concepts 615

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:615

■ The object table itself is not mentioned in the query. The only table listed in the query is

KEEPER. You do not need to know the name of the object table to DEREF its values.

■ The entire referenced row object was returned, not just part of the row.

These are significant differences that separate object queries from relational queries. Thus,

when querying your tables, you need to know the way in which their relationships are established.

Are the relationships based on foreign keys and primary keys, or on object tables and REF

datatypes? To help smooth the transition between relational and object-oriented approaches,

Oracle allows you to create object views that contain REFs superimposed on existing relational

tables. See “Object Views with REFs,” later in this chapter.

The VALUE Function

The DEREF function was applied to the relational table—the KEEPER table, in this case. The DEREF

function returns the value of the reference that goes from the relational table to the object table.

What about querying from the object table? Can you select from ANIMAL?

select * from ANIMAL;

BREED NAME BIRTHDATE

------------------------- ------------------------- ---------

MULE FRANCES 01-APR-02

DOG BENJI 03-SEP-01

Even though ANIMAL is an object table, you can select from it as if it were a relational table.

This is consistent with the examples of inserts and selects shown earlier in this chapter. However,

that is not what was shown via the DEREF. The DEREF showed the full structure of the abstract

datatype used by the ANIMAL object table:

select DEREF(K.AnimalKept)

from KEEPER K

where KeeperName = 'CATHERINE WEILZ';

DEREF(K.ANIMALKEPT)(BREED, NAME, BIRTHDATE)

---------------------------------------------------

ANIMAL_TY('DOG', 'BENJI', '03-SEP-01')

To see the same structures from a query of the ANIMAL object table, use the VALUE function.

As shown in the following listing, VALUE shows you the data in the same format that DEREF will

use. The parameter for the VALUE function is the table alias.

select VALUE(A)

from ANIMAL A

where Name = 'BENJI';

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:26 PM

Color profile: Generic CMYK printer profile

Composite Default screen

616 Part IV: Object-Relational Databases

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:616

VALUE(A)(BREED, NAME, BIRTHDATE)

-----------------------------------------

ANIMAL_TY('DOG', 'BENJI', '03-SEP-01')

ANIMAL_TY('DOG', 'BENJI', '03-SEP-01')

The VALUE function is useful when debugging references and within PL/SQL, as shown in

“Object PL/SQL,” later in this chapter. Since it allows you to query the formatted values directly

from the object table, you can select those values without using the DEREF query of KEEPER’s

AnimalKept column.

Invalid References

You can delete the object to which a reference points. For example, you can delete a row from

the ANIMAL object table to which a KEEPER record points:

delete from ANIMAL

where Name = 'BENJI';

The record in KEEPER that references this ANIMAL record will now have what is called a

dangling REF. If you insert a new ANIMAL row for the animal named BENJI, it won’t be recognized

as being part of the same reference established earlier. The reason is that the first time you inserted

a BENJI row, Oracle generated an OID for the row object, and that is what the KEEPER column

referenced. When you deleted the row object, the OID went away—and Oracle does not reuse

OID numbers. Therefore, when the new BENJI record is entered, it is given a new OID value—

and the KEEPER record still points to the old value.

This is a critical difference between relational and OOP systems. In a relational system, the

join between two tables is dependent only on the current data. In an OOP system, the join is

between objects—and just because two objects have the same data, that doesn’t mean they are

the same.

Object Views with REFs

You can use object views to superimpose OOP structures on existing relational tables (refer to

Chapter 30). For example, you can create abstract datatypes and use them within the object view

of an existing table. Using object views allows you to access the table via either relational command

syntax or abstract datatype syntax. As a result, object views provide an important technological

bridge from existing relational applications to object-relational applications.

A Quick Review of Object Views

This example from Chapter 30 will serve as part of the basis for the advanced object views in this

chapter. First, a CUSTOMER table is created, with the Customer_ID column as its primary key:

create table CUSTOMER

(Customer_ID NUMBER constraint CUSTOMER_PK primary key,

Name VARCHAR2(25),

Street VARCHAR2(50),

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:27 PM

Color profile: Generic CMYK printer profile

Composite Default screen

City VARCHAR2(25),

State CHAR(2),

Zip NUMBER);

Next, two abstract datatypes are created. The first, ADDRESS_TY, contains attributes for

addresses: Street, City, State, and Zip. The second, PERSON_TY, contains a Name attribute plus

an Address attribute that uses the ADDRESS_TY datatype, as shown in the following listing:

create or replace type ADDRESS_TY as object

(Street VARCHAR2(50),

City VARCHAR2(25),

State CHAR(2),

Zip NUMBER);

create or replace type PERSON_TY as object

(Name VARCHAR2(25),

Address ADDRESS_TY);

Since the CUSTOMER table was created without using the ADDRESS_TY and PERSON_TY

datatypes, you need to use object views in order to access CUSTOMER data via object-based

accesses (such as methods). You can create an object view that specifies the abstract datatypes that

apply to the CUSTOMER table. In the following listing, the CUSTOMER_OV object view is created:

create view CUSTOMER_OV (Customer_ID, Person) as

select Customer_ID,

PERSON_TY(Name,

ADDRESS_TY(Street, City, State, Zip))

from CUSTOMER;

In the creation of the CUSTOMER_OV object view, the constructor methods for the two abstract

datatypes (ADDRESS_TY and PERSON_TY) are specified. You can now access the CUSTOMER table

directly (as a relational table) or via the constructor methods for the abstract datatypes.

The CUSTOMER table will be used in the next set of examples in this chapter.

Object Views Involving References

If the CUSTOMER table shown in the previous section is related to another table, you can use

object views to create a reference between the tables. That is, Oracle will use the existing primary

key/foreign key relationships to simulate OIDs for use by REFs between the tables. You will thus

be able to access the tables either as relational tables or as objects. When you treat the tables as

objects, you will be able to use the REFs to automatically perform joins of the tables (refer to

“Using the DEREF Function,” earlier in this chapter, for examples).

The CUSTOMER table has a primary key of Customer_ID. Let’s create a small table that will

contain a foreign key reference to the Customer_ID column. In the following listing, the CUSTOMER_

CALL table is created. The primary key of the CUSTOMER_CALL table is the combination

of Customer_ID and Call_Number. The Customer_ID column of CUSTOMER_CALL is a foreign

Chapter 33: Advanced Object-Oriented Concepts 617

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:617

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:27 PM

Color profile: Generic CMYK printer profile

Composite Default screen

key back to CUSTOMER—you cannot record a call for a customer who does not already have a

record in CUSTOMER. A single nonkey attribute, Call_Date, is created within the CUSTOMER_

CALL table.

create table CUSTOMER_CALL

(Customer_ID NUMBER,

Call_Number NUMBER,

Call_Date DATE,

constraint CUSTOMER_CALL_PK

primary key (Customer_ID, Call_Number),

constraint CUSTOMER_CALL_FK foreign key (Customer_ID)

references CUSTOMER(Customer_ID));

For example, you could have the following CUSTOMER and CUSTOMER_CALL entries:

insert into CUSTOMER values

(123,'SIGMUND','47 HAFFNER RD','LEWISTON','NJ',22222);

insert into CUSTOMER values

(234,'EVELYN','555 HIGH ST','LOWLANDS PARK','NE',33333);

insert into CUSTOMER_CALL values

(123,1,TRUNC(SysDate)-1);

insert into CUSTOMER_CALL values

(123,2,TRUNC(SysDate));

The foreign key from CUSTOMER_CALL to CUSTOMER defines the relationship between the

tables. From an OOP point of view, the records in CUSTOMER_CALL reference the records in

CUSTOMER. Therefore, we must find a way to assign OID values to the records in CUSTOMER

and generate references in CUSTOMER_CALL.

How to Generate OIDs

First, use an object view to assign OIDs to the records in CUSTOMER. Remember that OIDs

are assigned to records in an object table—and an object table, in turn, is based on an abstract

datatype. Therefore, we first need to create an abstract datatype that has the same structure as the

CUSTOMER table:

create or replace type CUSTOMER_TY as object

(Customer_ID NUMBER,

Name VARCHAR2(25),

Street VARCHAR2(50),

City VARCHAR2(25),

State CHAR(2),

Zip NUMBER);

Now, create an object view based on the CUSTOMER_TY type, while assigning OID values

to the records in CUSTOMER:

create or replace view CUSTOMER_OV of CUSTOMER_TY

with object identifier (Customer_ID) as

618 Part IV: Object-Relational Databases

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:618

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:27 PM

Color profile: Generic CMYK printer profile

Composite Default screen

select Customer_ID, Name, Street, City, State, Zip

from CUSTOMER;

The first part of this create view command gives the view its name (CUSTOMER_OV) and tells

Oracle that the view’s structure is based on the CUSTOMER_TY datatype:

create or replace view CUSTOMER_OV of CUSTOMER_TY

The next part of the create view command tells the database how to construct OID values for

the rows in CUSTOMER. The with object identifier clause is followed by the column to use for the

OID—in this case, the Customer_ID value. This will allow you to address the rows within the

CUSTOMER table as if they were referenceable row objects within an object table.

with object identifier (Customer_ID) as

NOTE

The with object identifier clause replaces the with object OID clause

used in earlier versions of Oracle. The with object OID syntax is still

supported for backward compatibility.

The final part of the create view command gives the query on which the view’s data access

will be based. The columns in the query must match the columns in the view’s base datatype.

select Customer_ID, Name, Street, City, State, Zip

from CUSTOMER;

The rows of CUSTOMER are now accessible as row objects via the CUSTOMER_OV view.

The OID values generated for the CUSTOMER_OV rows are called pkOIDs, because they are

based on CUSTOMER’s primary key values. Relational tables can be accessed as row objects if

you create object views for them.

How to Generate References

The rows of CUSTOMER_CALL reference rows in CUSTOMER. From a relational perspective, the

relationship is determined by the foreign key pointing from the CUSTOMER_CALL.Customer_ID

column to the CUSTOMER.Customer_ID column. Now that the CUSTOMER_OV object view has

been created, and the rows in CUSTOMER can be accessed via OIDs, you need to create reference

values in CUSTOMER_CALL that reference CUSTOMER. Once the REFs are in place, you will be

able to use the DEREF function (shown earlier in this chapter) to access the CUSTOMER data from

within CUSTOMER_CALL.

The create view command for the object view of CUSTOMER_CALL is shown in the following

listing. It uses a new function, MAKE_REF, which is described following the listing.

create view CUSTOMER_CALL_OV as

select MAKE_REF(CUSTOMER_OV, Customer_ID) Customer_ID,

Call_Number,

Call_Date

from CUSTOMER_CALL;

Chapter 33: Advanced Object-Oriented Concepts 619

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:619

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:28 PM

Color profile: Generic CMYK printer profile

Composite Default screen

620 Part IV: Object-Relational Databases

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:620

With the exception of the MAKE_REF operation, this create view command looks like a normal

create view command. The MAKE_REF operation is shown in this line:

select MAKE_REF(CUSTOMER_OV, Customer_ID) Customer_ID,

The MAKE_REF function takes as arguments the name of the object view being referenced and

the name of the column (or columns) that form the foreign key in the local table. In this case, the

Customer_ID column of the CUSTOMER_CALL table references the column that is used as the basis

of OID generation in the CUSTOMER_OV object view. Therefore, two parameters are passed to

MAKE_REF: CUSTOMER_OV and Customer_ID. The result of the MAKE_REF operation is given the

column alias Customer_ID. Since this command creates a view, the result of an operation must be

given a column alias.

What does MAKE_REF do? It creates references (called pkREFs, since they are based on primary

keys) from the CUSTOMER_CALL_OV view to the CUSTOMER_OV view. You can now query the

two views as if CUSTOMER_OV were an object table and CUSTOMER_CALL_OV were a table

that contains a REF datatype that references CUSTOMER_OV.

Querying the Object Views

The queries of the object views with REFs mirror the structure of the queries of table REFs. You

use the DEREF function to select the value of the referenced data, as shown earlier in this chapter.

Applied to the object views, the query will be

select DEREF(CCOV.Customer_ID)

from CUSTOMER_CALL_OV CCOV

where Call_Date = TRUNC(SysDate);

DEREF(CCOV.CUSTOMER_ID)(CUSTOMER_ID, NAME, STREET, CITY, STATE,ZIP)

-------------------------------------------------------------------

CUSTOMER_TY(123, 'SIGMUND', '47 HAFFNER RD', 'LEWISTON','NJ',22222)

The query found the record in CUSTOMER_CALL for which the Call_Date value was the

current system date. It then took the Customer_ID value from that record and evaluated its

reference. That Customer_ID value, from the MAKE_REF function, pointed to a pkOID value in

the CUSTOMER_OV object view. The CUSTOMER_OV object view returned the record whose

pkOID matched the referenced value. The DEREF function then returned the value of the

referenced row. The query thus returned rows from CUSTOMER even though the user only

queried CUSTOMER_CALL.

Object views of column objects enable you to work with tables as if they were both relational

tables and object-relational tables. When extended to row objects, object views enable you to

generate OID values based on established foreign key/primary key relationships. Object views

allow you to continue to use the existing constraints and standard insert, update, delete, and

select commands. They also allow you to use OOP features such as references against the object

tables. Object views thus provide an important technological bridge for migrating to an OOP

database architecture.

As described earlier in this chapter, Oracle performs joins that resolve the references defined

in the database. When the referenced data is retrieved, it brings back the entire row object that

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:28 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:621

was referenced. To reference the data, you need to establish pkOIDs in the table that is the

“primary key” table in the relationship, and use MAKE_REF to generate references in the table

that is the “foreign key” table in the relationship. You can then work with the data as if it were

stored in object tables.

Object PL/SQL

PL/SQL programs can use the abstract datatypes you have created. Whereas earlier versions of PL/SQL

could only use the Oracle-provided datatypes (such as DATE, NUMBER, and VARCHAR2), you can

now use your own user-defined datatypes as well. The result is the merging of SQL, procedural logic,

and OOP extensions—a combination referred to as object PL/SQL.

The following anonymous PL/SQL block uses object PL/SQL concepts. The CUSTOMER_TY

abstract datatype is used as the datatype for the Cust1 variable, and its value is populated by a

query of the CUSTOMER_OV object view.

set serveroutput on

declare

Cust1 CUSTOMER_TY;

begin

select VALUE(COV) into Cust1

from CUSTOMER_OV COV

where Customer_ID = 123;

DBMS_OUTPUT.PUT_LINE(Cust1.Name);

DBMS_OUTPUT.PUT_LINE(Cust1.Street);

end;

The output of this PL/SQL block is

SIGMUND

47 HAFFNER RD

In the first part of the PL/SQL block, a variable is declared using the CUSTOMER_TY datatype:

declare

Cust1 CUSTOMER_TY;

The Cust1 variable value is selected from the CUSTOMER_OV object view (created in the

previous section of this chapter). The VALUE function is used to retrieve the data in the structure

of the abstract datatype. Since the data will be selected in the format of the abstract datatype, you

need to use the CUSTOMER_OV object view created on the CUSTOMER table.

begin

select VALUE(COV) into Cust1

from CUSTOMER_OV COV

where Customer_ID = 123;

Chapter 33: Advanced Object-Oriented Concepts 621

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:29 PM

Color profile: Generic CMYK printer profile

Composite Default screen

622 Part IV: Object-Relational Databases

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:622

The Cust1.Name and Cust1.Street values are then retrieved from the attributes of the Cust1

variable and displayed. You cannot pass the entire Cust1 variable to the PUT_LINE procedure.

DBMS_OUTPUT.PUT_LINE(Cust1.Name);

DBMS_OUTPUT.PUT_LINE(Cust1.Street);

end;

This is a deliberately simple example, but it shows the power of object PL/SQL. You can use

object PL/SQL anywhere you use abstract datatypes. Your PL/SQL is thus no longer bound to the

Oracle-provided datatypes, and may more accurately reflect the objects in your database. In this

example, an object view was queried to illustrate that the queries can access either column objects

or row objects. You can then select the attributes of the abstract datatype and manipulate or display

them. If you have defined methods for the abstract datatype, you can apply them as well.

For example, you can call the datatype’s constructor methods within your PL/SQL blocks. In the

following example, a variable named NewCust is defined using the CUSTOMER_TY datatype. The

NewCust variable is then set equal to a set of values using the CUSTOMER_TY constructor method.

The NewCust variable’s set of values is then inserted via the CUSTOMER_OV object view.

declare

NewCust CUSTOMER_TY;

begin

NewCust :=

CUSTOMER_TY(345,'NewCust','StreetVal', 'City','ST',00000);

insert into CUSTOMER_OV

values (NewCust);

end;

You can see the result of the insert by querying CUSTOMER_OV:

select Customer_ID, Name from CUSTOMER_OV;

CUSTOMER_ID NAME

----------- -------------------------

123 SIGMUND

234 EVELYN

345 NewCust

In addition to calling constructor methods, you can call the methods you have created on

your abstract datatypes. If you will be comparing the values of variables that use the abstract

datatypes, you will need to define map or order methods for the datatypes. This capability allows

you to further extend object PL/SQL—you define the datatypes and the functions at the database

level, and they are callable within any of your PL/SQL programs. Object PL/SQL represents a

significant enhancement over traditional PL/SQL.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:29 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Objects in the Database

The features available in Oracle—column objects, row objects, and object extensions to PL/SQL—

enable you to implement objects in your database without sacrificing the investment you have

already made in analysis and design. You can continue to create systems based on relational

design techniques and tune them based on relational access methods. The tools that Oracle

provides allow you to create an OOP layer above your relational tables. Once you have that

layer in place, you can access the relational data as if it were stored in a fully OOP database.

Having an OOP layer allows you to realize some of the benefits of an OOP system, such

as abstraction and encapsulation. You can apply the methods for each abstract datatype

across a set of consistently implemented objects, and benefit from object reuse and standards

enforcement. At the same time, you can benefit from Oracle’s relational features. The ability

to use both relational and object technology within an application lets you use the proper tool

for the proper job within the database.

When implementing the object portion of an object-relational database, start by defining the

abstract datatypes that are the core components of your business. Every object-relational feature,

whether it relates to column objects or row objects, is based on an abstract datatype. The better

you have defined your datatypes and their methods, the better you will be able to implement

objects. If necessary, nest objects so that you can have multiple variations of the same core datatype.

The result will be a database that is properly designed to take advantage of the relational and

OOP features Oracle provides now, and will provide in versions to come.

Chapter 33: Advanced Object-Oriented Concepts 623

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:623

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:29 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 33

Blind Folio 33:624

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:14:30 PM

Color profile: Generic CMYK printer profile

Composite Default screen