In its support for the Oracle Server, this release provides you with access to Oracle user-defined objects. These include object tables, column objects in relational tables, and the special kind of column object known as a REF column.
In Oracle Database Server, an object table is based on a user's object type definition. This definition is itself a composite, each attribute or component of which is based on a familiar Oracle Built-in datatype. (An object type definition can be based on another object type definition, but ultimately the definitions rest on known Oracle Built-in datatypes.)
Because of this, each column in an object table is treated as an individual data item, in a manner similar to the way it treats the columns of a relational table.
A database programmer can also base a column in a relational table on an object type definition. This has the effect of embedding a composite object in the relational table.
The embedded column object is expanded into its composite parts, and each part is treated as a column in itself.
For example, if the Oracle Server held a 3-column relational table, and one of those columns was a column object, and that column object was based on an object definition having four parts or attributes, then the table would be treated as a 6-column table (the 2 relational columns plus the 4 columns that comprise the object).
In the event one of the individual attributes of the column object's definition was based on yet another object type, then that part is further expanded and treated as a set of individual columns.
A column in either an object or relational table can be defined as having a type of REF. A REF column does not hold data directly, but rather has pointers (references) to another object table that holds the actual data.
The reference can be resolved at runtime, so that the user sees the actual referenced data.
To create objects in the Oracle Server, a database programmer first defines an object type, and then creates a specific object based on that type definition. The resulting object can be either an object table or a column object in a relational table.
Consider the following definitions. The first creates an object type. The second creates a table of that type.
CREATE TYPE emp_type AS OBJECT
(name CHAR(30),
title CHAR(20),
idnum NUMBER);
CREATE TABLE emp OF emp_type;
In the above example, we create an object table named emp. It has three columns, and those columns have the names and datatypes that are established in the attributes of the emp_type object type definition.
The following is an example of a column object in a relational table:
CREATE TYPE address_type
AS OBJECT
(street VARCHAR2(40),
city VARCHAR2(25),
state VARCHAR2(2)
zip NUMBER);
CREATE TABLE customers
(cust_id NUMBER,
name VARCHAR(40),
address address_type);
In this example, we create a relational table named customers. The table has three columns. The first two columns (cust_id and name) are the usual relational columns. The third column (named address) is a column object. It is built on the object type definition named address_type. This address column has the structure and datatypes established by that object definition. The address column can be thought of as having four parts or components (street, city, state, and zip). Each of these components is of the datatype that was established in the address_type object type definition. (In this case, all are of VARCHAR2 except for zip, which is of datatype NUMBER).
In the following illustration, the third column of a three-column relational table is based on an object type that itself has three parts or components.
Note that an Oracle object is a composite, built from parts or components. Each of its parts has a name and is of a known Oracle datatype. If the object is a table (actually, a row in an object table), it has as many columns or parts as there are attributes in the object type definition. If a column in a relational table is based on an object, then each of the object's parts can be thought of an additional column in that table. Because each of these parts is based on an attribute in the object type definition, these parts are sometimes called attribute columns.
An object table is treated very much like a relational table. Each column is individually selectable. Although all columns may be selected, each is treated as a separate item within the application. The table itself does not have any particular identity as an object within the environment.
Similarly, a column object in a relational table is not treated as a single, homogeneous entity. Instead, it treats each of the column object's components as an individual, independent data item. Each component of the column object is selectable. Once selected, each is treated as an independent data item. Even if all of the column object's components are selected, the column object itself does not have any particular identity as an object within the development environment.
Data items selected from an object are given the default name ColumnObjectName_ItemName. Thus, if we select the component named zip from the column object named address in the above example, the data item will have the name address_zip within the application.