Oracle8i Application Developer's Guide - Object-Relational Features
Release 2 (8.1.6)

Part Number A76976-01





Go to previous page Go to next page

A Sample Application using Object-Relational Features

This chapter has an extended example of how to use user-defined datatypes (Oracle objects). The example shows how a relational model might be transformed into an object-relational model that better represents the real-world entities that are managed by an application.

This chapter contains the following sections:


User-defined types are schema objects in which users formalize the data structures and operations that appear in their applications.

The example in this chapter illustrates the most important aspects of defining and using user-defined types. One important aspect of using user-defined types is creating methods that perform operations on objects. In the example, definitions of object type methods use the PL/SQL language. Other aspects of using user-defined types, such as defining a type, use SQL.

PL/SQL and Java provide additional capabilities beyond those illustrated in this chapter, especially in the area of accessing and manipulating the elements of collections.

Client applications that use the Oracle Call Interface (OCI), Pro*C/C++, or Oracle Objects for OLE (OO4O) can take advantage of its extensive facilities for accessing objects and collections, and manipulating them on clients.

See Also:


A Purchase Order Example

This example is based on a typical business activity: managing customer orders. We demonstrate how the application might evolve from relational to object-relational, and how you could write it from scratch using a pure object-oriented approach.

Implementing the Application Under The Relational Model

Figure 8-1 Entity-Relationship Diagram for Purchase Order Application

Text description of adg81066.gif follows.

Text description of the illustration adg81066.gif.

Entities and Relationships

The basic entities in this example are:

As you can see from Figure 8-1, a customer has contact information, so that the address and set of telephone numbers is exclusive to that customer. The application does not allow different customers to be associated with the same address or telephone numbers. If a customer changes her address, the previous address ceases to exist. If someone ceases to be a customer, the associated address disappears.

A customer has a one-to-many relationship with a purchase order: a customer can place many orders, but a given purchase order is placed by one customer. Because a customer can be defined before they place an order, the relationship is optional rather than mandatory.

Similarly, a purchase order has a many-to-many relationship with a stock item. Because this relationship does not show which stock items appear on which purchase orders, the entity-relationship has the notion of a line item. A purchase order must contain one or more line items. Each line item is associated only with one purchase order.

The relationship between line item and stock item is that a stock item can appear on zero, one, or many line items, but each line item refers to exactly one stock item.

Creating Tables Under the Relational Model

The relational approach normalizes everything into tables. The table names are Customer_reltab, PurchaseOrder_reltab, and Stock_reltab.

Each part of an address becomes a column in the Customer_reltab table.

Structuring telephone numbers as columns sets an arbitrary limit on the number of telephone numbers a customer can have.

The relational approach separates line items from their purchase orders and puts each into its own table, named PurchaseOrder_reltab and LineItems_reltab. As depicted in Figure 8-1, a line item has a relationship to both a purchase order and a stock item. These are implemented as columns in LineItems_reltab table with foreign keys to PurchaseOrder_reltab and Stock_reltab.


We have adopted a convention in this section of adding the suffix _reltab to the names of relational tables. Such a self-describing notation can make your code easier to maintain.

You may find it useful to make distinctions between tables (_tab) and types (_typ). But you can choose any names you want; one of the main advantages of object-relational methods is that the names of software entities can closely model real-world objects. 

The relational approach results in the following tables:


The Customer_reltab table has the following definition:

CREATE TABLE Customer_reltab (
  CustNo                NUMBER NOT NULL,
  CustName              VARCHAR2(200) NOT NULL,
  Street                VARCHAR2(200) NOT NULL,
  City                  VARCHAR2(200) NOT NULL,
  State                 CHAR(2) NOT NULL,
  Zip                   VARCHAR2(20) NOT NULL,
  Phone1                VARCHAR2(20),
  Phone2                VARCHAR2(20),
  Phone3                VARCHAR2(20),
  ) ;

This table, Customer_reltab, stores all the information about customers, which means that it fully contains information that is intrinsic to the customer (defined with the NOT NULL constraint) and information that is not as essential. According to this definition of the table, the application requires that every customer have a shipping address.

Our Entity-Relationship (E-R) diagram showed a customer placing an order, but the table does not make allowance for any relationship between the customer and the purchase order. This suggests that the relationship must be managed by the purchase order.


The PurchaseOrder_reltab table has the following definition:

CREATE TABLE PurchaseOrder_reltab (    
   PONo        NUMBER, /* purchase order no */  
   Custno      NUMBER references Customer_reltab, /*  Foreign KEY referencing 
                                                      customer */
   OrderDate   DATE, /*  date of order */  
   ShipDate    DATE, /* date to be shipped */    
   ToStreet    VARCHAR2(200), /* shipto address */    
   ToCity      VARCHAR2(200),    
   ToState     CHAR(2),    
   ToZip       VARCHAR2(20),    
   ) ;   

As expected, PurchaseOrder_reltab manages the relationship between the customer and the purchase order by means of the foreign key (FK) column CustNo, which references the CustNo key of the PurchaseOrder_reltab. Because the table makes no allowance for the relationship between the purchase order and its line items, the list of line items must handle this.


The LineItems_reltab table has the following definition:

CREATE TABLE LineItems_reltab (
  LineItemNo           NUMBER,
  PONo                 NUMBER REFERENCES PurchaseOrder_reltab,
  StockNo              NUMBER REFERENCES Stock_reltab,
  Quantity             NUMBER,
  Discount             NUMBER,
  PRIMARY KEY (PONo, LineItemNo)
  ) ;


The Stock_reltab table, describe in "Stock_reltab", must be created before the LineItems_reltab table. 

The table name is in the plural form LineItems_reltab to emphasize to someone reading the code that the table holds a collection of line items.

As shown in the E-R diagram, the list of line items has relationships with both the purchase order and the stock item. These relationships are managed by LineItems_reltab by means of two foreign key columns:


The Stock_reltab table has the following definition:

CREATE TABLE Stock_reltab (
  Price        NUMBER,
  TaxRate      NUMBER
  ) ;

Inserting Values Under the Relational Model

In our application, statements like these insert data into the tables:

Establish Inventory

INSERT INTO Stock_reltab VALUES(1004, 6750.00, 2) ;
INSERT INTO Stock_reltab VALUES(1011, 4500.23, 2) ;
INSERT INTO Stock_reltab VALUES(1534, 2234.00, 2) ;
INSERT INTO Stock_reltab VALUES(1535, 3456.23, 2) ;

Register Customers

INSERT INTO Customer_reltab
  VALUES (1, 'Jean Nance', '2 Avocet Drive',
         'Redwood Shores', 'CA', '95054',
         '415-555-1212', NULL, NULL) ;

INSERT INTO Customer_reltab
  VALUES (2, 'John Nike', '323 College Drive',
         'Edison', 'NJ', '08820',
         '609-555-1212', '201-555-1212', NULL) ;

Place Orders

INSERT INTO PurchaseOrder_reltab
  VALUES (1001, 1, SYSDATE, '10-MAY-1997',
          NULL, NULL, NULL, NULL) ;

INSERT INTO PurchaseOrder_reltab
  VALUES (2001, 2, SYSDATE, '20-MAY-1997',
         '55 Madison Ave', 'Madison', 'WI', '53715') ;

Detail Line Items

INSERT INTO LineItems_reltab VALUES(01, 1001, 1534, 12,  0) ;
INSERT INTO LineItems_reltab VALUES(02, 1001, 1535, 10, 10) ;
INSERT INTO LineItems_reltab VALUES(01, 2001, 1004,  1,  0) ;
INSERT INTO LineItems_reltab VALUES(02, 2001, 1011,  2,  1) ;

Querying Data Under The Relational Model

The application can execute queries like these:

Get Customer and Line Item Data for a Specific Purchase Order

SELECT   C.CustNo, C.CustName, C.Street, C.City, C.State,
         C.Zip, C.phone1, C.phone2, C.phone3,
         P.PONo, P.OrderDate,
         L.StockNo, L.LineItemNo, L.Quantity, L.Discount
 FROM    Customer_reltab C,
         PurchaseOrder_reltab P,
         LineItems_reltab L
 WHERE   C.CustNo = P.CustNo
  AND    P.PONo = L.PONo
  AND    P.PONo = 1001 ;

Get the Total Value of Purchase Orders

SELECT     P.PONo, SUM(S.Price * L.Quantity)
 FROM      PurchaseOrder_reltab P,
           LineItems_reltab L,
           Stock_reltab S
 WHERE     P.PONo = L.PONo
  AND      L.StockNo = S.StockNo

Get the Purchase Order and Line Item Data for those LineItems that Use a Stock Item Identified by a Specific Stock Number

SELECT    P.PONo, P.CustNo,
          L.StockNo, L.LineItemNo, L.Quantity, L.Discount
 FROM     PurchaseOrder_reltab P,
          LineItems_reltab     L
   AND    L.StockNo = 1004 ;

Updating Data Under The Relational Model

The application can execute statements like these to update the data:

Update the Quantity for Purchase Order 1001 and Stock Item 1534

UPDATE LineItems_reltab
   SET      Quantity = 20
   WHERE    PONo     = 1001
   AND      StockNo  = 1534 ;

Deleting Data Under The Relational Model

The application can execute statements like these to delete data:

Delete Purchase Order 1001

   FROM   LineItems_reltab
   WHERE  PONo = 1001 ;

   FROM   PurchaseOrder_reltab
   WHERE  PONo = 1001 ;

Limitations of a Purely Relational Model

The Relational Database Management System (RDBMS) is a very powerful and efficient form of information management. Why then should you even consider another approach? If you examine the application as developed under the relational model in comparison to the real world of the application domain, then certain shortcomings become evident.

Limitation in Encapsulating Data (Structure) with Operations (Behavior)

Database tables are excellent for modeling a structure of relationships, but they fail to capture the way that objects in the real world are naturally bundled with operations on the data. For example, when you operate on a purchase order in the real world, you expect to be able to sum the line items to find the total cost to the customer. Similarly, you expect that you should be able to retrieve information about the customer who placed the order -- such as name, reference number, address, and so on. More complexly, you may want to determine the customer's buying history and payment pattern.

An RDBMS provides very sophisticated structures for storing and retrieving data, but each application developer must craft the operations needed for each application. This means that you must recode operations often, even though they may be very similar to operations already coded for applications within the same enterprise.

Limitation in Dealing with Composition

Relational tables do not capture compositions. For example, an address may be a composite of number, street, city, state, and zip code, but in a relational table, the notion of an address as a structure composed of the individual columns is not captured.

Limitation in Dealing with Aggregation

Relational tables have difficulty dealing with complex part-whole relationships. A piston and an engine have the same status as columns in the Stock_reltab, but there is no easy way to describe the fact that pistons are part of engines, except by creating multiple tables with primary key-foreign key relationships. Similarly, there is no easy way to implement the complex interrelationships between collections.

Limitation in Dealing with Generalization-Specialization

There is no easy way to capture the relationship of generalization-specification (inheritance). If we abstract the base requirements of a purchase order and write code to capture the relationships, then there is no way to develop purchase orders that use this code and then further specialize it for different domains. Instead, we will have duplicated the code in every implementation of a purchase order.

The Evolution of the Object-Relational Database System

So why not create applications using a third-generation language (3GL)?

First, an RDBMS provides functionality that would take millions of person-hours to replicate.

Second, one of the problems of information management using 3GLs is that they are not persistent; or, if they are persistent, then they sacrifice security to obtain the necessary performance by way of locating the application logic and the data logic in the same address space. Neither trade-off is acceptable to users of an RDBMS, for whom both persistence and security are basic requirements.

This leaves the application developer working under the relational model with the problem of simulating complex types by some form of mapping into SQL. Apart from the many person-hours required, this approach involves serious problems of implementation. You must:

Obviously, there is heavy traffic back and forth between the client address space and that of the server, with the accompanying decrement in performance. And, if client and server are on different machines, then the toll on performance from network roundtrips may be considerable.

Object-relational (O-R) technology solves these problems. This chapter and the following chapter present examples that implement this new functionality.

Implementing the Application Under The Object-Relational Model

Figure 8-2 Class Diagram for Purchase Order Application

Text description of adg81068.gif follows.

Text description of the illustration adg81068.gif.

The object-relational (O-R) approach begins with the same entity relationships as in "Entities and Relationships". Viewing these from the object-oriented perspective, as in the class diagram above, allows us to translate more of the real-world structure into the database schema.

Rather than breaking up addresses or multiple phone numbers into unrelated columns in relational tables, the O-R approach defines types to represent them. Rather than breaking line items out into a separate table, the O-R approach allows them to stay with their respective purchase orders as nested tables.

The main entities -- customers, stock, and purchase orders -- become objects. Object references express the relationships between them. Collection types model their multi-valued attributes.

There are two approaches to an object-relational implementation:

The remainder of this chapter develops the O-R schema and shows how to implement it with object tables. Chapter 4, "Applying an Object Model to Relational Data" implements the same schema with object views.

Defining Types

The following statements set the stage by defining incomplete object types:

CREATE TYPE StockItem_objtyp;
CREATE TYPE LineItem_objtyp;
CREATE TYPE PurchaseOrder_objtyp;

The incomplete definitions notify Oracle that full definitions are coming later. Oracle can compile other types that refer to these incomplete types. Incomplete type declarations are like forward declarations in C and other programming languages.

The following statement defines an array type:

CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20);

Figure 8-3 Object Relational Representation of PhoneList_vartyp Type

Text description of adg81069.gif follows.

Text description of the illustration adg81069.gif.

The preceding statement defines the type PhoneList_vartyp. Any data unit of type PhoneList_vartyp is a varray of up to 10 telephone numbers, each represented by a data item of type VARCHAR2.

A list of phone numbers could occupy a varray or a nested table. In this case, the list is the set of contact phone numbers for a single customer. A varray is a better choice than a nested table for the following reasons:

In general, if ordering and bounds are not important design considerations, then designers can use the following rule of thumb for deciding between varrays and nested tables: If you need to query the collection, then use nested tables; if you intend to retrieve the collection as a whole, then use varrays.

See Also:

Chapter 5, "Design Considerations for Oracle Objects" for more information about the design considerations for varrays and nested tables. 

The following statement defines the object type Address_objtyp to represent addresses:

CREATE TYPE Address_objtyp AS OBJECT (
  Street         VARCHAR2(200),
  City           VARCHAR2(200),
  State          CHAR(2),
  Zip            VARCHAR2(20)

Figure 8-4 Object Relational Representation of Address_objtyp Type

Text description of adg81070.gif follows.

Text description of the illustration adg81070.gif.

All of the attributes of an address are character strings, representing the usual parts of a simplified mailing address.

The following statement defines the object type Customer_objtyp, which uses other user-defined types as building blocks.

CREATE TYPE Customer_objtyp AS OBJECT (
  CustNo           NUMBER,
  CustName         VARCHAR2(200),
  Address_obj      Address_objtyp,
  PhoneList_var    PhoneList_vartyp,

    compareCustOrders(x IN Customer_objtyp) RETURN INTEGER

Instances of the type Customer_objtyp are objects that represent blocks of information about specific customers. The attributes of a Customer_objtyp object are a number, a character string, an Address_objtyp object, and a varray of type PhoneList_vartyp.

Every Customer_objtyp object also has an associated order method, one of the two types of comparison methods. Whenever Oracle needs to compare two Customer_objtyp objects, it invokes the compareCustOrders method to do so.


The PL/SQL to implement the comparison method appears in "The compareCustOrders Method"

The two types of comparison methods are map methods and order methods. This application uses one of each for purposes of illustration.

An ORDER method must be called for every two objects being compared, whereas a MAP method is called once per object. In general, when sorting a set of objects, the number of times an ORDER method is called is more than the number of times a MAP method would be called.

See Also:  

The following statement completes the definition of the incomplete object type LineItem_objtyp declared at the beginning of this section.

  LineItemNo   NUMBER,
  Stock_ref    REF StockItem_objtyp,
  Quantity     NUMBER,
  Discount     NUMBER

Figure 8-5 Object Relational Representation of LineItem_objtyp Type

Text description of adg81071.gif follows.

Text description of the illustration adg81071.gif.

Instances of type LineItem_objtyp are objects that represent line items. They have three numeric attributes and one REF attribute. The LineItem_objtyp models the line item entity and includes an object reference to the corresponding stock object.

The following statement defines the nested table type LineItemList_ntabtyp, which will represent an arbitrary set of line items inside a purchase order:

CREATE TYPE LineItemList_ntabtyp AS TABLE OF LineItem_objtyp 

A data unit of this type is a nested table, each row of which contains an object of type LineItem_objtyp. A nested table of line items is a better choice to represent the multivalued line item list than a varray of LineItem_objtyp objects, because:

The following statement completes the definition of the incomplete object type PurchaseOrder_objtyp declared at the beginning of this section:

  PONo                 NUMBER,
  Cust_ref             REF Customer_objtyp,
  OrderDate            DATE,
  ShipDate             DATE,
  LineItemList_ntab    LineItemList_ntabtyp,
  ShipToAddr_obj       Address_objtyp,


    sumLineItems RETURN NUMBER

Figure 8-6 Object Relational Representation of the PuchaseOrder_objtyp

Text description of adg81072.gif follows.

Text description of the illustration adg81072.gif.

The preceding statement defines the object type PurchaseOrder_objtyp. Instances of this type are objects representing purchase orders. They have six attributes, including a REF to Customer_objtyp, an Address_objtyp object, and a nested table of type LineItemList_ntabtyp, which is based on type LineItem_objtyp.

Objects of type PurchaseOrder_objtyp have two methods: getPONo and sumLineItems. One, getPONo, is a MAP method, one of the two kinds of comparison methods. A MAP method returns the relative position of a given record within the order of records within the object. So, whenever Oracle needs to compare two PurchaseOrder_objtyp objects, it implicitly calls the getPONo method to do so.

The two pragma declarations provide information to PL/SQL about what sort of access the two methods need to the database.

The statement does not include the actual PL/SQL programs implementing the methods getPONo and sumLineItems. That appears in "Method Definitions".

The following statement completes the definition of StockItem_objtyp, the last of the three incomplete object types declared at the beginning of this section.

CREATE TYPE StockItem_objtyp AS OBJECT (
  StockNo    NUMBER,
  Price      NUMBER,
  TaxRate    NUMBER

Figure 8-7 Object Relational Representation of the StockItem_objtyp

Text description of adg81073.gif follows.

Text description of the illustration adg81073.gif.

Instances of type StockItem_objtyp are objects representing the stock items that customers order. They have three numeric attributes.

Method Definitions

This section shows how to specify the methods of the PurchaseOrder_objtyp and Customer_objtyp object types. The following statement defines the body of the PurchaseOrder_objtyp object type (the PL/SQL programs that implement its methods):


      RETURN PONo;   
      i             INTEGER;  
      StockVal      StockItem_objtyp;  
      Total         NUMBER := 0;  
      FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP  
         Total := Total + SELF.LineItemList_ntab(i).Quantity * StockVal.Price;  
      END LOOP;  
      RETURN Total;  

The getPONo Method

The getPONo method is simple; use it to return the purchase order number of its associated PurchaseOrder_objtyp object. Such "get" methods allow you to avoid reworking code that uses the object if its internal representation changes.

The sumLineItems Method

The sumLineItems method uses a number of object-relational features:

The remainder of the method program is a loop that sums the extended values of the line items, and the method returns the total as its value.

The compareCustOrders Method

The following statement defines the compareCustOrders method of the Customer_objtyp object type.

  compareCustOrders (x IN Customer_objtyp) RETURN INTEGER IS
    RETURN CustNo - x.CustNo;

As mentioned earlier, the order method compareCustOrders operation compares information about two customer orders. It takes another Customer_objtyp object as an input argument and returns the difference of the two CustNo numbers. The return value is:

Whether the return value is positive, negative, or zero signifies the relative order of the customer numbers. For example, perhaps lower numbers are created earlier in time than higher numbers. If either of the input arguments (SELF and explicit) to an ORDER method is NULL, Oracle does not call the ORDER method and simply treats the result as NULL.

This completes the definition of the user-defined types used in the purchase order application. None of the declarations creates tables or reserves data storage space.

Creating Object Tables

To this point, the example is the same whether you plan to create and populate object tables or implement the application with object views on top of the relational tables that appear in "Implementing the Application Under The Relational Model". The remainder of this chapter continues the example using object tables. Chapter 4, "Applying an Object Model to Relational Data", picks up from this point and continues the example with object views.

Generally, you can think of the relationship between the "objects" and "object tables" in the following way:

Viewed in this way, each object table is an implicit type whose objects (specific rows) each have the same attributes (column values). The creation of explicit user-defined datatypes and object tables introduces a new level of functionality.

The Object Table Customer_objtab

The following statement defines an object table Customer_objtab to hold objects of type Customer_objtyp:

CREATE TABLE Customer_objtab OF Customer_objtyp (CustNo PRIMARY KEY) 

As you can see, the term "OF" makes the create statement different for object tables as opposed to relational tables. We earlier defined the attributes of Customer_objtyp objects as:

CustNo           NUMBER
CustName         VARCHAR2(200)
Address_obj      Address_objtyp
PhoneList_var    PhoneList_vartyp

This means that the object table Customer_objtab has columns of CustNo, CustName, Address_obj, and PhoneList_var, and that each row is an object of type Customer_objtyp. As you will see, this notion of row object offers a significant advance in functionality.

Figure 8-8 Object Relational Representation of Table Customer_objtab

Text description of adg81074.gif follows.

Text description of the illustration adg81074.gif.

Object Datatypes as a Template for Object Tables

Because there is a type Customer_objtyp, you could create numerous object tables of the same type. For example, you could create an object table Customer_objtab2 also of type Customer_objtyp. Without this ability, you would need to define each table individually.

You can introduce variations when creating multiple tables. The statement that created Customer_objtab defined a primary key constraint on the CustNo column. This constraint applies only to this object table. Another object table of the same type might not have this constraint.

Object Identifiers and References

Customer_objtab contains customer objects, represented as row objects. Oracle allows row objects to be referenceable, meaning that other row objects or relational rows may reference a row object using its object identifier (OID). For example, a purchase order row object may reference a customer row object using its object reference. The object reference is an opaque system-generated value represented by the type REF and is composed of the row object's unique OID.

Oracle requires every row object to have a unique OID. You may specify the unique OID value to be system-generated or specify the row object's primary key to serve as its unique OID. You indicate this when you execute the CREATE TABLE statement by specifying OBJECT ID PRIMARY KEY or OBJECT ID SYSTEM GENERATED, the latter serving as the default. The choice of primary key as the object identifier may be more efficient in cases where the primary key value is smaller than the default 16 byte system-generated identifier. For our example, the choice of primary key as the row object identifier has been made.

Object Tables with Embedded Objects

Examining the definition of Customer_objtab, you can see that the Address_obj column contains Address_objtyp objects. In other words, an object type may have attributes that are themselves object types. These embedded objects represent composite or structured values, and are also referred to as column objects. They differ from row objects because they are not referenceable and can be NULL.

Address_objtyp objects have attributes of built-in types, which means that they are leaf-level scalar attributes of Customer_objtyp. Oracle creates columns for Address_objtyp objects and their attributes in the object table Customer_objtab. You can refer to these columns using the dot notation. For example, if you want to build an index on the Zip column, then you can refer to it as Address.Zip.

The PhoneList column contains varrays of type PhoneList_vartyp. We defined each object of type PhoneList_vartyp as a varray of up to 10 telephone numbers, each represented by a data item of type VARCHAR2:

CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20)

Because each varray of type PhoneList_vartyp can contain no more than 200 characters (10 x 20), plus a small amount of overhead, Oracle stores the varray as a single data unit in the PhoneList_var column. Oracle stores varrays that exceed 4000 bytes in "inline" BLOBs, which means that a portion of the varray value could potentially be stored outside the table.

The Object Table Stock_objtab

The next statement creates an object table for StockItem_objtyp objects:

CREATE TABLE Stock_objtab OF StockItem_objtyp (StockNo PRIMARY KEY)

Each row of the table is a StockItem_objtyp object having three numeric attributes:

StockNo    NUMBER
Price      NUMBER
TaxRate    NUMBER

Oracle assigns a column for each attribute, and the CREATE TABLE statement places a primary key constraint on the StockNo column, and specifies that the primary key be used as the row object's identifier.

The Object Table PurchaseOrder_objtab

The next statement defines an object table for PurchaseOrder_objtyp objects:

CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (  /* Line 1 */
   PRIMARY KEY (PONo),                                       /* Line 2  */
   FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)        /* Line 3 */
   OBJECT ID PRIMARY KEY                                     /* Line 4 */
   NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (     /* Line 5 */
     (PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))              /* Line 6 */
     ORGANIZATION INDEX COMPRESS)                            /* Line 7 */
   RETURN AS LOCATOR                                         /* Line 8 */

The preceding CREATE TABLE statement creates the PurchaseOrder_objtab object table. The significance of each line is as follows:

Line 1:

CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp (

This line indicates that each row of the table is a PurchaseOrder_objtyp object. Attributes of PurchaseOrder_objtyp objects are:

  PONo                 NUMBER
  Cust_ref             REF Customer_objtyp
  OrderDate            DATE
  ShipDate             DATE
  LineItemList_ntab    LineItemList_ntabtyp
  ShipToAddr_obj       Address_objtyp

Figure 8-9 Object Relational Representation of Table PurchaseOrder_objtab

Text description of adg81076.gif follows.

Text description of the illustration adg81076.gif.

Line 2:


This line specifies that the PONo attribute is the primary key for the table.

Line 3:

FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)

This line specifies a referential constraint on the Cust_ref column. This referential constraint is similar to those specified for relational tables. When there is no constraint, the REF column allows you to reference any row object. However, in this case, the Cust_ref REFs can refer only to row objects in the Customer_objtab object table.

Line 4:


This line indicates that the primary key of the PurchaseOrder_objtab object table be used as the row's OID.

Line 5 - 8:

NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab (

These lines pertain to the storage specification and properties of the nested table column, LineItemList_ntab. The rows of a nested table are stored in a separate storage table. This storage table is not directly queryable by the user but can be referenced in DDL statements for maintenance purposes. A hidden column in the storage table, called the NESTED_TABLE_ID, matches the rows with their corresponding parent row. All the elements in the nested table belonging to a particular parent have the same NESTED_TABLE_ID value. For example, all the elements of the nested table of a given row of PurchaseOrder_objtab have the same value of NESTED_TABLE_ID. The nested table elements that belong to a different row of PurchaseOrder_objtab have a different value of NESTED_TABLE_ID.

In the CREATE TABLE example above, Line 5 indicates that the rows of LineItemList_ntab nested table are to be stored in a separate table (referred to as the storage table) named PoLine_ntab. The STORE AS clause also allows you to specify the constraint and storage specification for the storage table. In this example, Line 7 indicates that the storage table is an index-organized table (IOT). In general, storing nested table rows in an IOT is beneficial, because it provides clustering of rows belonging to the same parent. The specification of COMPRESS on the IOT saves storage space because, if you do not specify COMPRESS, the NESTED_TABLE_ID part of the IOT's key is repeated for every row of a parent row object. If, however, you specify COMPRESS, the NESTED_TABLE_ID is stored only once for each row of a parent row object.

The SCOPE FOR constraint on a REF is not allowed in a CREATE TABLE statement. Therefore, to specify that Stock_ref can reference only the object table Stock_objtab, issue the following ALTER TABLE statement on the PoLine_ntab storage table:

   ADD (SCOPE FOR (Stock_ref) IS stock_objtab) ;

Note that this statement is executed on the storage table, not the parent table.

See Also:

"Nested Table Storage" for information about the benefits of organizing a nested table as and IOT and specifying nested table compression, and for more information about nested table storage. 

In Line 6, the specification of NESTED_TABLE_ID and LineItemNo attribute as the primary key for the storage table serves two purposes: first, it serves as the key for the IOT; second, it enforces uniqueness of a column (LineItemNo) of a nested table within each row of the parent table. By including the LineItemNo column in the key, the statement ensures that the LineItemNo column contains distinct values within each purchase order.

Line 8 indicates that the nested table, LineItemList_ntab, is returned in the locator form when retrieved. If you do not specify LOCATOR, the default is VALUE, which indicates that the entire nested table is returned instead of just a locator to the nested table. When the nested table collection contains many elements, it may not be very efficient to return the entire nested table whenever the containing row object or the column is selected.

Specifying that the nested table's locator is returned enables Oracle to send to the client only a locator to the actual collection value. An application can find whether a fetched nested table is in the locator or value form by calling the OCICollIsLocator or UTL_COLL.IS_LOCATOR interfaces. Once you know that the locator has been returned, the application can query using the locator to fetch only the desired subset of row elements in the nested table. This locator-based retrieval of the nested table rows is based on the original statement's snapshot, to preserve the value or copy semantics of the nested table. That is, when the locator is used to fetch a subset of row elements in the nested table, the nested table snapshot reflects the nested table when the locator was first retrieved.

Recall the implementation of the sumLineItems method of PurchaseOrder_objtyp in "Method Definitions". That implementation assumed that the LineItemList_ntab nested table would be returned as a VALUE. In order to handle large nested tables more efficiently, and to take advantage of the fact that the nested table in the PurchaseOrder_objtab is returned as a locator, the sumLineItems method must be rewritten as follows:


         RETURN PONo;   
      i          INTEGER;  
      StockVal   StockItem_objtyp;  
      Total      NUMBER := 0;
      IF (UTL_COLL.IS_LOCATOR(LineItemList_ntab)) -- check for locator
            SELECT SUM(L.Quantity * L.Stock_ref.Price) INTO Total
            FROM   TABLE(CAST(LineItemList_ntab AS LineItemList_ntabtyp)) L;
         FOR i in 1..SELF.LineItemList_ntab.COUNT LOOP  
            Total := Total + SELF.LineItemList_ntab(i).Quantity * 
         END LOOP;  
      END IF;  
   RETURN Total;  

The rewritten sumLineItems method checks whether the nested table attribute, LineItemList_ntab, is returned as a locator using the UTL_COLL.IS_LOCATOR function. When the condition evaluates to TRUE, the nested table locator is queried using the TABLE expression.


The CAST expression is currently required in such TABLE expressions to communicate to the SQL compilation engine the actual type of the collection attribute (or parameter or variable) so that it can successfully compile the query. 

The querying of the nested table locator results in a more efficient processing of the large line item list of a purchase order. The previous code that iterates over the LineItemList_ntab is kept to deal with the case where the nested table is returned as a VALUE.

After the table is created, the following ALTER TABLE statement is issued:

   ADD (SCOPE FOR (Stock_ref) IS stock_objtab);

This statement specifies that the Stock_ref column of the nested table is scoped to Stock_objtab. This indicates that the values stored in this column must be references to row objects in Stock_objtab. The SCOPE constraint is different from the referential constraint, because the SCOPE constraint has no implication on the referenced object. For example, any referenced row object in Stock_objtab may be deleted, even if it is referenced in the Stock_ref column of the nested table. Such a deletion renders the corresponding reference in the nested table a DANGLING REF.

Figure 8-10 Object Relational Representation of Nested Table LineItemList_ntab

Text description of adg81077.gif follows.

Text description of the illustration adg81077.gif.

Oracle does not support referential constraint specification for storage tables. In this situation, specifying the SCOPE clause for a REF column is useful. In general, specifying scope or referential constraints for REF columns has a few benefits:

At this point, all of the tables for the purchase order application are in place. The next section shows how to operate on these tables.

Figure 8-11 Object Relational Representation of Table PurchaseOrder_objtab

Text description of adg81078.gif follows.

Text description of the illustration adg81078.gif.

Inserting Values

Here is how to insert the same data into the object tables as we did earlier for relational tables. Notice how some of the values are actually calls to the constructors for object types.

INSERT INTO Stock_objtab VALUES(1004, 6750.00, 2) ;
INSERT INTO Stock_objtab VALUES(1011, 4500.23, 2) ;
INSERT INTO Stock_objtab VALUES(1534, 2234.00, 2) ;
INSERT INTO Stock_objtab VALUES(1535, 3456.23, 2) ;
INSERT INTO Customer_objtab
    1, 'Jean Nance',
    Address_objtyp('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'),
    ) ;

INSERT INTO Customer_objtab
    2, 'John Nike',
    Address_objtyp('323 College Drive', 'Edison', 'NJ', '08820'),
    ) ;
INSERT INTO PurchaseOrder_objtab
  SELECT  1001, REF(C),
          SYSDATE, '10-MAY-1999',
   FROM   Customer_objtab C
   WHERE  C.CustNo = 1 ;

The preceding statement constructs a PurchaseOrder_objtyp object with the following attributes:

  PONo                1001
  Cust_ref            REF to customer number 1
  OrderDate           SYSDATE
  ShipDate            10-MAY-1999
  LineItemList_ntab   an empty LineItem_ntabtyp
  ShipToAddr_obj      NULL

The statement uses a query to construct a REF to the row object in the Customer_objtab object table that has a CustNo value of 1.

The following statement uses a TABLE expression to identify the nested table as the target for the insertion, namely the nested table in the LineItemList_ntab column of the row object in the PurchaseOrder_objtab table that has a PONo value of 1001.


Oracle release 8.0 supports the "flattened subquery" or "THE (subquery)" expression to identify the nested table. This construct is deprecated in release 8.1 in favor of the TABLE expression illustrated below. 

  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 1001
  SELECT  01, REF(S), 12, 0
   FROM   Stock_objtab S
   WHERE  S.StockNo = 1534 ;

The preceding statement inserts a line item into the nested table identified by the TABLE expression. The inserted line item contains a REF to the row object with a StockNo value of 1534 in the object table Stock_objtab.

The following statements follow the same pattern as the previous ones:

INSERT INTO PurchaseOrder_objtab
  SELECT  2001, REF(C),
          SYSDATE, '20-MAY-1997',
          Address_objtyp('55 Madison Ave','Madison','WI','53715')
   FROM   Customer_objtab C
   WHERE  C.CustNo = 2 ;

  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 1001
  SELECT  02, REF(S), 10, 10
   FROM   Stock_objtab S
   WHERE  S.StockNo = 1535 ;

  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 2001
  SELECT  10, REF(S), 1, 0
   FROM   Stock_objtab S
   WHERE  S.StockNo = 1004 ;

  SELECT  P.LineItemList_ntab
   FROM   PurchaseOrder_objtab P
   WHERE  P.PONo = 2001
    FROM  Stock_objtab S
    WHERE S.StockNo = 1011), 2, 1) ;


The following query statement implicitly invokes a comparison method. It shows how Oracle orders objects of type PurchaseOrder_objtyp using that type's comparison method:

 FROM   PurchaseOrder_objtab p

Oracle invokes the map method getPONo for each PurchaseOrder_objtyp object in the selection. Because that method returns the object's PONo attribute, the selection produces a list of purchase order numbers in ascending numerical order.

The following queries correspond to the queries executed under the relational model.

Customer and Line Item Data for Purchase Order 1001
SELECT  DEREF(p.Cust_ref), p.ShipToAddr_obj, p.PONo, 
        p.OrderDate, LineItemList_ntab
 FROM   PurchaseOrder_objtab p
 WHERE  p.PONo = 1001 ;
Total Value of Each Purchase Order
SELECT   p.PONo, p.sumLineItems()
 FROM    PurchaseOrder_objtab p ;
Purchase Order and Line Item Data Involving Stock Item 1004
SELECT   po.PONo, po.Cust_ref.CustNo,
         CURSOR (
           SELECT  *
            FROM   TABLE (po.LineItemList_ntab) L
            WHERE  L.Stock_ref.StockNo = 1004
 FROM    PurchaseOrder_objtab po ; 

The above query returns a nested cursor for the set of LineItem_obj objects selected from the nested table. The application can fetch from the nested cursor to get the individual LineItem_obj objects. The above query can also be expressed by unnesting the nested set with respect to the outer result:

SELECT   po.PONo, po.Cust_ref.CustNo, L.*
 FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L
 WHERE   L.Stock_ref.StockNo = 1004 ;

The above query returns the result set as a "flattened" form (or First Normal Form). This type of query is useful when accessing Oracle collection columns from relational tools and APIs, such as ODBC. In the above unnesting example, only the rows of the PurchaseOrder_objtab object table that has any LineItemList_ntab rows are returned. To fetch all rows of the PurchaseOrder_objtab table, regardless of the presence of any rows in their corresponding LineItemList_ntab, then the (+) operator is required:

SELECT   po.PONo, po.Cust_ref.CustNo, L.*
 FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) (+) L
 WHERE   L.Stock_ref.StockNo = 1004 ;

Average Discount across all Line Items of all Purchase Orders

This request requires querying the rows of all nested tables, LineItemList_ntab, of all PurchaseOrder_objtab rows. Again, unnesting is required:

 FROM    PurchaseOrder_objtab po, TABLE (po.LineItemList_ntab) L ;


The following example has the same effect as the two deletions needed in the relational case (see "Deleting Data Under The Relational Model"). In this case, Oracle automatically deletes all line items belonging to the deleted purchase order. The relational case requires a separate step.

Delete Purchase Order 1001
 FROM   PurchaseOrder_objtab
 WHERE  PONo = 1001 ;

Manipulating Objects Through Java

Using the schema that we have already defined for the purchase order example, we can manipulate objects within the database through the Java Database Connectivity (JDBC) API or by using embedded SQL with SQLJ. Although we use JDBC in this example, the coding for both is similar, and you can use either technique for object-oriented programs.

The first decision you do have to make is how closely you want to map the object types in the database to Java classes. The following sections show the two choices.

Using oracle.sql.* Classes (Weak Typing)

In this example:

This technique lets us essentially write a procedural Java program that can easily interact with a particular class, as long as the definition of that class stays the same.

import java.sql.*;
import oracle.sql.*;

public class DefaultMappingDemo
  public static void main(String[] args)
    System.out.println("*** JAVA OBJECTS DEMO ***");

  try {
   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

     Connection conn = DriverManager.getConnection
      "scott", "tiger") ;

   Statement stmt = conn.createStatement();

   ResultSet rs = stmt.executeQuery
        ("select value(c) from CUSTOMER_TAB c order by value(c)");

   while ( ())

   // retrieve the STRUCT
   oracle.sql.STRUCT cust_struct = (STRUCT)rs.getObject(1);

   oracle.sql.Datum cust_attrs[] = cust_struct.getOracleAttributes();
   oracle.sql.NUMBER num = (NUMBER)cust_attrs[0];

   // string attribute in Object
   oracle.sql.CHAR name = (CHAR) cust_attrs[1];

   // embedded object
   oracle.sql.STRUCT address_struct = (STRUCT)cust_attrs[2];
   oracle.sql.Datum address_attrs[] = address_struct.getOracleAttributes();
   oracle.sql.CHAR street = (CHAR) address_attrs[0];
   oracle.sql.CHAR city   = (CHAR) address_attrs[1];
   oracle.sql.CHAR state  = (CHAR) address_attrs[2];
   oracle.sql.CHAR zip    = (CHAR) address_attrs[3];

   System.out.println("Number: " + num.stringValue() + ", Name: " + name +
                      ", Address: " + street + ", " + city + ", " + state +
                      ", " + zip);
   //embedded array
   oracle.sql.ARRAY phone_list = (ARRAY)cust_attrs[3];
  catch (SQLException exn)
     System.out.println("SQLException: "+exn);

Using Strong Typing (SQLData or CustomDatum)

If you want to model the database object types using multiple Java classes, you can construct a strongly typed model. The classes all implement some common behavior to do the underlying database operations. Now, you have another choice: do you want to model the classes on the JDBC 2.0 API (the SQLData interface) or on Oracle's API (the CustomDatum interface)?

The SQLData interface is standards-based and potentially offers portability between different database systems. The CustomDatum interface is derived from JDBC, but offers additional enhancements; it can encapsulate REFs, collection types, and other object-oriented features not supported by JDBC.

You can generate wrapper classes for either interface by using JPublisher with different options.

Generating Wrapper Classes with JPublisher

In the strongly typed model, we need a Java class for each object type in the schema. The easiest way to get these classes is to let Oracle read the type definitions from the database and generate the Java code for us. To do this, we can use the following file as input to the JPublisher tool:


How to Use the Wrapper Classes

The wrapper classes all look much like the one below, JCustomer which corresponds to the CUSTOMER_INFO_T type in the database schema. For our example, we would also need the JAddress wrapper class because one of the attributes of JCustomer is a JAddress object.

You can read or write instances of this type using regular Java I/O streams. To implement additional member functions, you can subclass JCustomer, so that your code is preserved whenever that class is regenerated.

import java.sql.*;
import oracle.jdbc2.*;
import oracle.sql.*;

public class JCustomer implements SQLData
  private String sql_type;
  public int custNo;
  public String custName;
  public JAddress address;
  public Array phoneList;

  public String getSQLTypeName() throws SQLException { return sql_type; }

  public void readSQL (SQLInput stream, String typeName) throws SQLException
    sql_type = typeName;
    custNo   = stream.readInt();
    custName = stream.readString();
    address  = (JAddress) stream.readObject();
    phoneList= stream.readArray();
  public void writeSQL (SQLOutput stream) throws SQLException

In this example, we do not show member functions being derived from the method functions of the database type. Calling such member functions causes traffic as object data is passed back and forth to the database server, and you must follow certain conventions for input and output parameters. For information on this subject, see Oracle8i SQLJ Developer's Guide and Reference (Objects and Collections) and Oracle8i JDBC Developer's Guide and Reference (Working with Oracle Object Types).

Sample Program Using the SQLData Interface

In the following program:

Manipulating Objects with Oracle Objects for OLE

On Windows systems, you can use Oracle Objects for OLE (OO4O) to write object-oriented database programs in Visual Basic or other environments that support the COM protocol, such as Excel.

The following examples all begin with a similar header section that connects to the database, then each shows how to perform a different operation on object data.

Selecting Data

Here is an event handler for a button that performs a SELECT operation.

Inserting Data

Here is a program that retrieves a set of rows from the database, then adds a new row.

Updating Data

Here is a program that retrieves some rows from the database, then updates a specific one.

Calling a Method Function

Here is a program that retrieves a purchase order, and calls its member function TOTAL_VALUE to sum the cost of the line items that are part of the purchase order.

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.