Extending Oracle8 with Objects

by Steve Bobrowski

The advantage of following an object-oriented approach to application development is that it reduces the overall effort needed to build applications. In part 1 of this article (see Extending Oracle8 with Objects in the September/October 1997 issue), I covered the basics of Oracle8's new object types and provided a simple example of how to use object types to create a user-defined datatype. In this issue, I continue the discussion of object types by demonstrating how to use them to create nested tables and complete object schemas in an Oracle8 database.

Creating Nested Tables In this example, you'll see how to nest one table inside another by using an object type. When you nest a child table inside its parent table, Oracle8 automatically manages the built-in relationships between each row of the parent table and the associated nested table rows. Creating nested tables removes the complexity of relational joins from applications and can simplify some areas of subsequent application development.

Nested tables are appropriate for master-detail relationships in which detail rows store unique information. For example, consider the typical ORDERS and ITEMS tables in a relational order-entry database. Each line item is a unique collection of data that corresponds to a particular order. This type of master-detail relationship is a perfect candidate for a nested table. The following example creates a simplified ITEM_TYPE and corresponding ITEM_LIST_TYPE, which you can then use to nest a typical ITEMS table inside a typical ORDERS table.

CREATE REPLACE TYPE sales.item_type AS OBJECT (
 item_id INTEGER,
 quantity INTEGER );

CREATE OR REPLACE TYPE sales.item_list
 AS TABLE OF
sales.Item_Type;

CREATE TABLE sales.orders (
 id INTEGER PRIMARY KEY,
 order_date DATE,
 ship_date DATE,
 line_items sales.Item_List )
 NESTED TABLE line_items STORE AS items;

Note: To simplify the example and focus on nested tables, the ITEM_TYPE does not contain an attribute for a part number nor does the ORDERS table contain a column for a customer ID. The examples later in this article show a more complete schema example for object tables that use references.

When you nest a table inside another table, Oracle stores the data within one physical data segment but creates two logical tables in the data dictionary. In the example above, Oracle creates the ORDERS table and the associated nested table ITEMS as one data segment but as two separate tables. As you might expect, manipulating nested tables requires some special data manipulation language (DML) syntax. For example, an application can insert a new order and corresponding items into the new ORDERS table, using a single INSERT statement with constructors to insert ITEMS into the nested table, as follows:

INSERT INTO sales.orders VALUES (
 1, SYSDATE, NULL,
 sales.Item_List(
 sales.Item_Type(1, 22),
 sales.Item_Type(2, 100) ) );

To work with individual rows in a nested table, the SQL statements must always access the master table by using a flattened subquery, which is denoted by the special SQL expression THE. If you are a relational expert, a THE expression might seem strange at first. But if you read SQL statements that contain flattened subqueries as they sound, the use of THE becomes much more intuitive. The following statement inserts another line item into an existing order, using a flattened subquery, and the pseudocode in the comment makes the use of the THE expression obvious:

-- insert a new line item into "the" line items for order #1 INSERT INTO THE(SELECT line_items FROM sales.orders WHERE id = 1) VALUES (3, 200); Additionally, to retrieve rows from a nested table, you must use a flattened subquery:

-- select the ID and quantity of "the" line items for order #1

SELECT item_id, quantity
 FROM THE(SELECT line_items FROM sales.orders WHERE id = 1)
 ORDER BY item_id;

ITEM_ID	QUANTITY
------- 	--------
	1	22
	2	100
	3	200

DELETE THE(SELECT line_items FROM sales.orders WHERE id = 1) o
 WHERE o.item_id = 3;

Creating Object Tables Up to this point, the examples demonstrate how to use object types simply to enhance the creation of relational database objects such as tables and stored procedures. When you want to go all the way to an object-oriented database design, you can use object types to create object tables. An object table is a database table you define by using only an object type and not such things as relational columns. When you create an object table, the columns correspond to the attributes of the object type you use to create the table.

The rows in an object table are objects of the table's type. Each object in an object table has a unique object identifier (OID), which Oracle automatically indexes and guarantees to be unique among all other OIDs. Oracle uses the OIDs of object tables to define the relationships among various object tables in a database. For example, a typical order-entry schema using object types and object tables would be implemented as shown in Listing 1.

Look closely at the definition of ITEM_TYPE and ORDER_TYPE, and you'll see that the examples introduce the concept of an object reference the PART attribute references objects of type PART_TYPE, and the CUSTOMER attribute references objects of type CUSTOMER_TYPE. Similar to a foreign key in a relational database table, an object reference is an attribute of one object that uses an OID to point to another object somewhere in the database.

Now let's see how to work with object tables by using standard SQL DML commands. First, the following statements demonstrate how to use the constructor methods to insert some data into the PARTS and CUSTOMERS tables:

INSERT INTO sales.parts
 VALUES (sales.Part_Type(1,'Pentium 200 CPU', 1000, 300));

INSERT INTO sales.customers
 VALUES (sales.Customer_Type(1,'Bazley','Marsha','Oracle',
 pub.Address_Type('500 Oracle Parkway', 'Mailstop 8BP1', 
'Redwood Shores','CA','94065','USA')));

INSERT INTO sales.orders
 SELECT 1, REF(c), SYSDATE, NULL, sales.Item_List()
 FROM sales.customers c
 WHERE id = 1;
To complete the order's line items, you must use INSERT statements 
with flattened subqueries, as the following examples demonstrate:


INSERT INTO THE(SELECT o.line_items FROM orders o WHERE o.id =1 )
 SELECT 1, REF(p), 20 FROM parts p
 WHERE id = 2;

INSERT INTO THE(SELECT o.line_items FROM orders o WHERE o.id = 1)
 SELECT 2, REF(p), 10 FROM parts p
 WHERE id = 11;

The PL/SQL block in Listing 2 demonstrates how a database application can program variables to reference OIDs within SELECT, INSERT, and UPDATE statements. The comments in the code show exactly what is happening.

The benefits of object tables, OIDs, and object references quickly become evident when you want to combine information from related object tables. Rather than build complicated join queries as you would with relational models, you can use extended dot notation for attributes of object tables that are object references. Oracle8 automatically navigates object references to make SQL coding more straightforward. The following object query returns related information from the ORDERS and CUSTOMERS object tables:

SELECT o.id, o.customer.company_name
 FROM sales.orders o;

ID   	CUSTOMER.COMPANY_NAME
---- 	---------------------
	1	Oracle Corporation

Beware of dependencies! The previous examples demonstrate how you can use object types to build database objects such as tables and stored procedures. When you do so, you establish a tree of dependencies among database objects and object types that must remain intact. In fact, Oracle8 keeps track of dependencies and will not let you pull the rug out from under anything. For example, you cannot drop an object type upon which a table is based. You can query the data dictionary at any time to display the dependencies of objects. For example, the following query, executed while connected as the user SALES, reveals the dependencies of the SALES.CUSTOMERS table on PUB.ADDRESS_TYPE and SALES.CUSTOMER_TYPE:

SELECT * FROM user_dependencies
	ORDER BY name, type;

NAME	TYPE	REFERENCED_OWNER	REFERENCED_NAME	REFERENCED_TYPE
----	  ----	----------------	---------------	---------------
CUSTOMERS	TABLE	PUB	ADDRESS_TYPE	TYPE
CUSTOMERS	TABLE	SALES	CUSTOMER_TYPE	TYPE

Dependencies are fine in cases in which you never need to change things. In the real world, however, changes are inevitable. That's when the dependencies established by object types can become a nightmare. Suppose you want to expand CUSTOMER_TYPE with a new attribute for PHOTO. Because the CUSTOMERS table depends on CUSTOMER_TYPE, you can't change the definition of CUSTOMER_TYPE without first dropping the CUSTOMERS table. You must carefully plan the object types for your database so that you get things right the first time in order to eliminate or minimize the need for changes. Then keep your fingers crossed and hope that things do not change once you have everything up and running.

Conclusion This two-part excerpt from my Oracle Press book, Oracle8 Architecture, provides you with what you need to get started using object types. If you are interested in learning more about Oracle8 objects, my book also discusses object methods and object views. Remember, the point is to utilize the benefits of object orientation while retaining the power of traditional relational databases.

Steve Bobrowski is the CEO of OraWorld (www.oraworld.com), an information hub on the Web dedicated to teaching Oracle professionals how to better use Oracle's products. Steve is the author of the award-winning book Mastering Oracle7 & Client/Server Computing (Sybex, 1996) and the new book Oracle8 Architecture (Oracle Press, 1997).

Copyright © 1994, 1995, 1996 & 1997 Oracle Corporation. All Rights Reserved.