Release 8.1.5
A67781-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Concepts Release 8.1.5 A67781-01 |
|
The Beautiful arises from the perceived harmony of an object, whether sight or sound, with the inborn and constitutive rules of the judgment and imagination: and it is always intuitive.
Samuel Taylor Coleridge, Genial Criticism
Object types and other user-defined datatypes allow you to define datatypes that model the structure and behavior of the data in their applications.
This chapter contains the following major sections:
The features described in this chapter are available only if you have purchased Oracle8i Enterprise Edition. Wherever the term Oracle server appears in this chapter it refers to the Oracle8i Enterprise Edition. See Getting to Know Oracle8i for information about the features available with Oracle8i Enterprise Edition.
Attention:
Relational database management systems (RDBMSs) are the standard tool for managing business data. They provide fast, efficient, and completely reliable access to huge amounts of data for millions of businesses around the world every day.
Oracle8i is an object-relational database management system (ORDBMS), which means that users can define additional kinds of data--specifying both the structure of the data and the ways of operating on it--and use these types within the relational model. This approach adds value to the data stored in a database. User-defined datatypes make it easier for application developers to work with complex data like images, audio, and video. Object types store structured business data in its natural form and allow applications to retrieve it that way. For that reason they work efficiently with applications developed using object-oriented programming techniques.
The Oracle server allows you to define complex business models in SQL and make them part of your database schema. Applications that manage and share your data need only contain the application logic, not the data logic.
For example, your firm may use purchase orders to organize its purchasing, accounts payable, shipping, and accounts receivable functions.
A purchase order contains an associated supplier or customer and an indefinite number of line items. In addition, applications often need dynamically computed status information about purchase orders. For example, you may need the current value of the shipped or unshipped line items.
Later sections of this chapter show how you can define a schema object, called an object type, that serves as a template for all purchase order data in your applications. An object type specifies the elements, called attributes, that make up a structured data unit like a purchase order. Some attributes, such as the list of line items, may be other structured data units. The object type also specifies the operations, called methods, you can perform on the data unit, such as determining the total value of a purchase order.
You can create purchase orders that match the template and store them in table columns, just as you would numbers or dates.
You can also store purchase orders in object tables, where each row of the table corresponds to a single purchase order and the table columns are the purchase order's attributes.
Since the logic of the purchase order's structure and behavior is in your schema, your applications don't need to know the details and don't have to keep up with most changes.
Oracle uses schema information about object types to achieve substantial transmission efficiencies. A client-side application can request a purchase order from the server and receive all the relevant data in a single transmission. The application can then, without knowing storage locations or implementation details, navigate among related data items without further transmissions from the server.
Many efficiencies of database systems arise from their optimized management of basic datatypes like numbers, dates, and characters. Facilities exist for comparing values, determining their distributions, building efficient indexes, and performing other optimizations.
Text, video, sound, graphics, and spatial data are examples of important business entities that don't fit neatly into those basic types. Oracle8i Enterprise Edition supports modeling and implementation of these complex datatypes.
Chapter 12, "Built-In Datatypes" describes Oracle's built-in datatypes. There are two additional categories of user-defined datatypes:
User-defined datatypes use the built-in datatypes and other user-defined datatypes as the building blocks for datatypes that model the structure and behavior of data in applications.
User-defined types are schema objects. Their use is subject to the same kinds of administrative control as other schema objects (see Chapter 14, "Using User-Defined Datatypes").
Object types are abstractions of the real-world entities--for example, purchase orders--that application programs deal with. An object type is a schema object with three kinds of components:
An object type is a template. A structured data unit that matches the template is called an object.
Here is an example of how you might define object types called EXTERNAL_PERSON, LINEITEM, and PURCHASE_ORDER.
The object types EXTERNAL_PERSON and LINEITEM have attributes of built-in types. The object type PURCHASE_ORDER has a more complex structure, which closely matches the structure of real purchase orders.
The attributes of PURCHASE_ORDER are ID, CONTACT, and LINEITEMS. The attribute CONTACT is an object, and the attribute LINEITEMS is a nested table (see "Nested Tables").
CREATE TYPE external_person AS OBJECT ( name VARCHAR2(30), phone VARCHAR2(20) ); CREATE TYPE lineitem AS OBJECT ( item_name VARCHAR2(30), quantity NUMBER, unit_price NUMBER(12,2) ); CREATE TYPE lineitem_table AS TABLE OF lineitem; CREATE TYPE purchase_order AS OBJECT ( id NUMBER, contact external_person, lineitems lineitem_table, MEMBER FUNCTION get_value RETURN NUMBER );
This is a simplified example. It does not show how to specify the body of the method GET_VALUE. Nor does it show the full complexity of a real purchase order.
|
Additional Information:
See Oracle8i Application Developer's Guide - Fundamentals for a complete purchase order example. |
An object type is a template. Defining it doesn't result in storage allocation. You can use LINEITEM, EXTERNAL_PERSON, or PURCHASE_ORDER in SQL statements in most of the same places you can use types like NUMBER or VARCHAR2.
For example, you might define a relational table to keep track of your contacts:
CREATE TABLE contacts ( contact external_person date DATE );
The CONTACT table is a relational table with an object type defining one of its columns. Objects that occupy columns of relational tables are called column objects (see "Row Objects and Column Objects").
Methods of an object type model the behavior of objects. The methods of an object type broadly fall into three categories: Member, Static, and Comparison.
A Member method is a function or a procedure that always has an implicit SELF parameter as its first parameter, whose type is the containing object type. Such methods may be invoked in a 'selfish' style, as in OBJECT.METHOD(). Member methods are useful for writing observer or mutator methods.
A Static method is a function or a procedure that does not have an implicit SELF parameter. Such methods may be invoked by qualifying the method with the type name, as in TYPE_NAME.METHOD(). Static methods are useful for specifying user-defined constructors or cast methods.
Comparison methods are used for comparing instances of objects.
Oracle supports the choice of implementing type methods in PL/SQL, JAVA, and C.
In the example, PURCHASE_ORDER has a method named GET_VALUE. Each purchase order object has its own GET_VALUE method. For example, if X and Y are PL/SQL variables that hold purchase order objects and W and Z are variables that hold numbers, the following two statements can leave W and Z with different values:
w = x.get_value(); z = y.get_value();
After those statements, W has the value of the purchase order referred to by variable X; Z has the value of the purchase order referred to by variable Y.
The term X.GET_VALUE () is an invocation of the method GET_VALUE. Method definitions can include parameters, but GET_VALUE does not need them, because it finds all of its arguments among the attributes of the object to which its invocation is tied. That is, in the first of the sample statements, it computes its value using the attributes of purchase order X. In the second it computes its value using the attributes of purchase order Y. This is called the selfish style of method invocation.
Every object type also has one implicitly defined method that is not tied to specific objects, the object type's constructor method.
Every object type has a system-defined constructor method, that is, a method that makes a new object according to the object type's specification. The name of the constructor method is the name of the object type. Its parameters have the names and types of the object type's attributes. The constructor method is a function. It returns the new object as its value.
For example, the expression
purchase_order( 1000376, external_person ("John Smith","1-800-555-1212"), NULL )
represents a purchase order object with the following attributes:
id 1000376 contact external_person("John Smith","1-800-555-1212") lineitems NULL
The expression external_person ("John Smith", "1-800-555-1212") is an invocation of the constructor function for the object type EXTERNAL_PERSON. The object that it returns becomes the contact attribute of the purchase order.
See "Nulls" for a discussion of null objects and null attributes.
Methods play a role in comparing objects. Oracle has facilities for comparing two data items of a given built-in type (for example, two numbers), and determining whether one is greater than, equal to, or less than the other. Oracle cannot, however, compare two items of an arbitrary user-defined type without further guidance from the definer. Oracle provides two ways to define an order relationship among objects of a given object type: map methods and order methods.
Map methods use Oracle's ability to compare built-in types. Suppose, for example, that you have defined an object type called RECTANGLE, with attributes HEIGHT and WIDTH. You can define a map method area that returns a number, namely the product of the rectangle's HEIGHT and WIDTH attributes. Oracle can then compare two rectangles by comparing their areas.
Order methods are more general. An order method uses its own internal logic to compare two objects of a given object type. It returns a value that encodes the order relationship. For example, it may return -1 if the first is smaller, 0 if they are equal, and 1 if the first is larger.
Suppose, for example, that you have defined an object type called ADDRESS, with attributes STREET, CITY, STATE, and ZIP. The terms "greater than" and "less than" may have no meaning for addresses in your application, but you may need to perform complex computations to determine when two addresses are equal.
In defining an object type, you can specify either a map method or an order method for it, but not both. If an object type has no comparison method, Oracle cannot determine a greater than or less than relationship between two objects of that type. It can, however, attempt to determine whether two objects of the type are equal.
Oracle compares two objects of a type that lacks a comparison method by comparing corresponding attributes:
For examples of how to specify and use comparison methods, see Oracle8i Application Developer's Guide - Fundamentals.
Additional Information:
An object table is a special kind of table that holds objects and provides a relational view of the attributes of those objects.
For example, the following statement defines an object table for objects of the EXTERNAL_PERSON type defined earlier:
CREATE TABLE external_person_table OF external_person;
Oracle allows you to view this table in two ways:
For example, you can execute the following instructions:
INSERT INTO external_person_table VALUES ( "John Smith", "1-800-555-1212" ); SELECT VALUE(p) FROM external_person_table p WHERE p.name = "John Smith";
The first instruction inserts an EXTERNAL_PERSON object into EXTERNAL_PERSON_TABLE as a multi-column table. The second selects from EXTERNAL_PERSON_TABLE as a single column table.
Objects that appear in object tables are called row objects. Objects that appear in table columns or as attributes of other objects are called column objects.
Every row object in an object table has an associated logical object identifier (OID). Oracle assigns a unique system-generated identifier of length sixteen bytes as the OID for each row object by default. Oracle provides no documentation of or access to the internal structure of object identifiers. This structure can change at any time.
The OID column of an object table is a hidden column. While the OID value in itself is not very meaningful to an object-relational application, Oracle uses this value to construct object references to the row objects. Applications need to be concerned with only object references which, as discussed below, are used for fetching and navigating objects.
The purpose of the OID for a row object is to uniquely identify it in an object table. To do this Oracle implicitly creates and maintains an index on the OID column of an object table. The system-generated unique identifier has many advantages among which are the unambiguous identification of objects in a distributed and replicated environment.
For applications that do not require the functionality provided by globally unique system-generated identifiers, storing sixteen extra bytes with each object and maintaining an index on it may not be efficient. Oracle allows the option of specifying the primary key value of a row object as the object identifier for the row object.
Primary-key based identifiers also have the advantage of enabling a more efficient and easier loading of the object table. By contrast, system-generated object identifiers need to be remapped using some user-specified keys, especially when references to them are also stored persistently.
An object view (see Chapter 16, "Object Views") is a virtual object table. Its rows are row objects. Oracle materializes object identifiers, which it does not store persistently, from primary keys in the underlying table or view.
In the relational model, foreign keys express many-to-one relationships. Oracle object types provide a more efficient means of expressing many-to-one relationships when the "one" side of the relationship is a row object.
Oracle provides a built-in datatype called REF to encapsulate references to row objects of a specified object type. From a modeling perspective, REFs provide the ability to capture an association between two row objects. Oracle uses object identifiers to construct such REFs.
You can use a REF to examine or update the object it refers to. You can also use a REF to obtain a copy of the object it refers to. The only changes you can make to a REF are to replace its contents with a reference to a different object of the same object type or to assign it a null value.
In declaring a column type, collection element, or object type attribute to be a REF, you can constrain it to contain only references to a specified object table. Such a REF is called a scoped REF. Scoped REFs require less storage space and allow more efficient access than unscoped REFs.
It is possible for the object identified by a REF to become unavailable--through either deletion of the object or a change in privileges. Such a REF is called dangling. Oracle SQL provides a predicate (called IS DANGLING) to allow testing REFs for this condition.
Accessing the object referred to by a REF is called dereferencing the REF. Oracle provides the DEREF operator to do this. Dereferencing a dangling REF results in a null object.
Oracle provides implicit dereferencing of REFs. For example, consider the following:
CREATE TYPE person AS OBJECT ( name VARCHAR2(30), manager REF person );
If x represents an object of type PERSON, then the expression
x.manager.name
represents a string containing the NAME attribute of the PERSON object referred to by the MANAGER attribute of X. The above expression is a shortened form of:
y.name, where y = DEREF(x.manager)
You can obtain a REF to a row object by selecting the object from its object table and applying the REF operator. For example, you can obtain a REF to the purchase order with identification number 1000376 as follows:
DECLARE OrderRef REF to purchase_order; SELECT REF(po) INTO OrderRef FROM purchase_order_table po WHERE po.id = 1000376;
For more on storage of objects and REFs, see "Collections".
|
Additional Information:
For examples of how to use REFs, see Oracle8i Application Developer's Guide - Fundamentals. |
Each collection type describes a data unit made up of an indefinite number of elements, all of the same datatype. The collection types are array types and table types.
Array types and table types are schema objects. The corresponding data units are called VARRAYs and nested tables. When there is no danger of confusion, we often refer to the collection types as VARRAYs and nested tables.
Collection types have constructor methods. The name of the constructor method is the name of the type, and its argument is a comma-separated list of the new collection's elements. The constructor method is a function. It returns the new collection as its value.
An expression consisting of the type name followed by empty parentheses represents a call to the constructor method to create an empty collection of that type. An empty collection is different from a null collection.
An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.
The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called VARRAYs. You must specify a maximum size when you declare the array type.
For example, the following statement declares an array type:
CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2);
The VARRAYs of type PRICES have no more than ten elements, each of datatype NUMBER(12,2).
Creating an array type does not allocate space. It defines a datatype, which you can use as
A VARRAY is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, however, Oracle stores it as a BLOB (see "Import/Export of User-Defined Types").
|
Additional Information:
For more information on using VARRAYs, see Oracle8i Application Developer's Guide - Fundamentals. |
A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.
For example, in the purchase order example, the following statement declares the table type used for the nested tables of line items:
CREATE TYPE lineitem_table AS TABLE OF lineitem;
A table type definition does not allocate space. It defines a type, which you can use as
When a table type appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table (see "Nested Tables"). For example, the following statement defines an object table for the object type PURCHASE_ORDER:
CREATE TABLE purchase_order_table OF purchase_order NESTED TABLE lineitems STORE AS lineitems_table;
The second line specifies LINEITEMS_TABLE as the storage table for the LINEITEMS attributes of all of the PURCHASE_ORDER objects in PURCHASE_ORDER_TABLE.
A convenient way to access the elements of a nested table individually is to use a nested cursor.
|
Additional Information:
See Oracle8i Reference for information about nested cursors, and see Oracle8i Application Developer's Guide - Fundamentals for more information on using nested tables. |
Oracle provides a number of facilities for using user-defined datatypes in application programs:
Oracle SQL DDL provides the following support for user-defined datatypes:
Oracle SQL DML provides the following support for user-defined datatypes:
For a complete description of Oracle SQL syntax, see Oracle8i SQL Reference.
Additional Information:
PL/SQL is a procedural language that extends SQL. It offers modern software engineering features like packages, data encapsulation, information hiding, overloading, and exception handling. Most stored procedures are written in PL/SQL.
PL/SQL allows use from within functions and procedures of the SQL features that support user-defined types.
The parameters and variables of PL/SQL functions and procedures can be of user-defined types.
PL/SQL provides all the capabilities necessary to implement the methods associated with object types. These methods (functions and procedures) reside on the server as part of a user's schema.
|
Additional Information:
For a complete description of PL/SQL, see PL/SQL User's Guide and Reference. |
The Oracle Pro*C/C++ precompiler allows programmers to use user-defined datatypes in C and C++ programs.
Pro*C developers can use the Object Type Translator to map Oracle object types and collections into C datatypes to be used in the Pro*C application.
Pro*C provides compile time type checking of object types and collections and automatic type conversion from database types to C datatypes.
Pro*C includes an EXEC SQL syntax to create and destroy objects and offers two ways to access objects in the server:
For a complete description of the Pro*C precompiler, see Pro*C/C++ Precompiler Programmer's Guide.
Additional Information:
The Oracle call interface (OCI) is a set of C language interfaces to the Oracle server. It provides programmers great flexibility in using the server's capabilities.
An important component of OCI is a set of calls to allow application programs to use a workspace called the object cache. The object cache is a memory block on the client side that allows programs to store entire objects and to navigate among them without round trips to the server.
The object cache is completely under the control and management of the application programs using it. The Oracle server has no access to it. The application programs using it must maintain data coherency with the server and protect the workspace against simultaneous conflicting access.
OCI provides functions to
OCI improves concurrency by allowing individual objects to be locked. It improves performance by supporting complex object retrieval.
OCI developers can use the object type translator to generate the C datatypes corresponding to a Oracle object types.
|
Additional Information:
For a complete description of OCI, see Oracle Call Interface Programmer's Guide. |
The Oracle type translator (OTT) is a program that automatically generates C language structure declarations corresponding to object types. OTT facilitates using the Pro*C precompiler and the OCI server access package.
|
Additional Information:
For complete information about OTT, see Oracle Call Interface Programmer's Guide and Pro*C/C++ Precompiler Programmer's Guide. |
The Java Publisher (JPublisher) is a program that automatically generates Java class definitions corresponding to user-defined types in the database. JPublisher facilitates using SQLJ and the JDBC server access package.
|
Additional Information:
For complete information about JPublisher, see Oracle8i JPublisher User's Guide. |
JDBC (Java Database Connectivity) is a set of Java interfaces to the Oracle server. Oracle's JDBC:
For complete information about JDBC, see the Oracle8i JDBC Developer's Guide and Reference.
Additional Information:
SQLJ allows developers to use user-defined datatypes in Java programs. Developers can use JPublisher to map Oracle object and collection types into Java classes to be used in the application.
SQLJ provides access to server objects using SQL statements embedded in the Java code. SQLJ provides compile time type checking of object types and collections in the SQL statements.
|
Additional Information:
For complete information about SQLJ, see the Oracle8i Java Developer's Guide. |
