Creating Object Types

A relational database is based on entities and the relationships between those entities. Manipulation of the data in a relational database must comply with any constraints defined within the database, but there is no information contained within the structure of the database to define what functions or operations will be performed with the data.

Oracle8 is an object-relational database management system (ORDBMS). The object-relational model enables you to define object types. An object type defines the entity and the operations that will be applied to the data. These operations are called methods. Object types can build upon one another, so an object type might be made up in whole or in part of other object types. Finally, the object types are used to define the objects stored in an object table.

Figure 21.23 shows the creation of an object type called person. The attributes are added one at a time by clicking on the Add button. In the example, three attributes have already been added, and a fourth, home_telephone, will be added as soon as the OK button is selected on the Create Attribute window.

Figure 21.23.  Creating an object type.

You can see in Figure 21.24 that I’m creating another object type, this time called customer. There are two other user-defined object types included in this definition: the person object type defined in the previous example and a mailing address object type. Notice the SQL used to create the object type is displayed at the bottom of the window.

Figure 21.24.  Object types defined with other object types.

Creating a VARRAY

A VARRAY contains multiple occurrences of the same item, such as a list of phone numbers or a list of item numbers on an order. When this list is defined, it can be used in an object table or as an attribute for another object type.

Figure 21.25 shows a VARRAY being created. This example is an array of up to 50 customers. A nested table could be used to accomplish the same thing. Using a VARRAY in this case makes sense only if there is a limit on the number of customers that should be grouped together and if the list will not be queried regularly. Because the VARRAY sets an upper limit on the number of entries, it can store the list more efficiently than a nested table. If the data in the list is to be queried, instead of being selected as a whole, it might be better to store the data in a nested table, because the VARRAY would have to be unloaded into a nested table anyway, in order to perform the query.

Figure 21.25.  Creating a VARRAY of customers.

After you’ve created a VARRAY, it can be used in creating an object type. You could create an object type of sales_rep, for example, which uses the customer_list varray to track up to 50 customers for each sales rep.

Creating a Nested Table

As I mentioned in the previous section, a nested table can be used to store a list of related items instead of a VARRAY. If the items within the list need to be queried, or if there is no upper limit to the number of items in the list, it might make more sense to use a nested table instead of a VARRAY.

To create a nested table, right-click on Table Types on the Schema Manager window and then select the Create option. The Create Table Type window will appear, as shown in Figure 21.26. In the example, a list of items to be used on an order is placed in an order_line_items nested table.

Figure 21.26.  Creating a nested table.

As you can see in Figure 21.27, the nested table can now be used in defining additional object types.

Figure 21.27.  Defining an object type with a nested table.

Creating an Object Table

When I created user-defined datatypes in the previous examples, such as object types and VARRAYs, no storage was created, and no structure was created in which you could actually store data. In order to store one of these objects as a row of data, you must create an object table.

An object table is simply that: a table used to store data for an object type, with each row representing an occurrence of an object. Figure 21.28 shows the creation of an object table for the sales_rep object type defined earlier. You create an object table just like a normal table, except that you select the Object Table radio button on the General page of the Create Table window. This changes the General page and enables you to select the object type for which you wish to build a table.

Figure 21.28.  Creating an object table.