This chapter contains the following topics:
Oracle object technology is a layer of abstraction built on Oracle's relational technology. New object types can be created from any built-in database types or any previously created object types, object references, and collection types. Metadata for user-defined types is stored in a schema available to SQL, PL/SQL, Java, and other published interfaces. Object datatypes make it easier to work with complex data, such as images, audio, and video.
An object type differs from native SQL datatypes in that it is user-defined, and it specifies both the underlying persistent data (attributes) and the related behaviors (methods). Object types are abstractions of the real-world entities, for example, purchase orders. Object types store structured business data in its natural form and allow applications to retrieve it that way.
Object types and related object-oriented features, such as variable-length arrays and nested tables, provide higher-level ways to organize and access data in the database. Underneath the object layer, data is still stored in columns and tables, but you can work with the data in terms of the real-world entities--customers and purchase orders, for example--that make the data meaningful. Instead of thinking in terms of columns and tables when you query the database, you can simply select a customer.
Internally, statements about objects are still basically statements about relational tables and columns, and you can continue to work with relational datatypes and store data in relational tables. But you have the option to take advantage of object-oriented features too. You can use object-oriented features while continuing to work with most of your relational data, or you can go over to an object-oriented approach entirely. For instance, you can define some object datatypes and store the objects in columns in relational tables. You can also create object views of existing relational data to represent and access this data according to an object model. Or you can store object data in object tables, where each row is an object.
The Oracle database server lets you 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.
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. You may need the current value of the shipped or unshipped line items.
An object type is a schema object 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, such as a purchase order. Some attributes, such as the list of line items, can 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.
Because the logic of the purchase order's structure and behavior is in your schema, your applications do not need to know the details and do not 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 do not fit neatly into those basic types. Oracle Enterprise Edition supports modeling and implementation of these complex datatypes.
Object 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.
A name, which serves to identify the object type uniquely within that schema
Methods, which are functions or procedures written in PL/SQL or Java and stored in the database, or written in a language such as C and stored externally. Methods implement operations the application can perform on the real-world entity.
An object type is a template. A structured data unit that matches the template is called an object.
Oracle Database Application Developer's Guide - Object-Relational Features for a complete purchase order example
Methods of an object type model the behavior of objects. The methods of an object type broadly fall into these categories:
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.
A Static method is a function or a procedure that does not have an implicit
SELF parameter. Such methods can be invoked by qualifying the method with the type name, as in
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.
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. You can also define your own constructor functions to use in place of the constructor functions that the system implicitly defines for every object type.
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 that you have defined an object type called
rectangle, with attributes
width. You can define a map method area that returns a number, namely the product of the rectangle's
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 could return -1 if the first is smaller, 0 if they are equal, and 1 if the first is larger.
Suppose that you have defined an object type called
address, with attributes
zip. 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:
If all the attributes are non-null and equal, Oracle reports that the objects are equal.
If there is an attribute for which the two objects have unequal non-null values, Oracle reports them unequal.
Otherwise, Oracle reports that the comparison is not available (null).
See Also:Oracle Database Application Developer's Guide - Object-Relational Features for examples of how to specify and use comparison methods
Oracle lets you view this table in two ways:
A single column table in which each entry is an
A multicolumn table in which each of the attributes of the object type
phone, occupies a column
Every row object in an object table has an associated logical object identifier. Oracle assigns a unique system-generated identifier of length 16 bytes as the object identifier for each row object by default.
The object identifier column of an object table is a hidden column. Although the object identifier 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 that are used for fetching and navigating objects.
The purpose of the object identifier 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 object identifier 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 16 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 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.
See Also:"Introduction to Object Views"
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
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
REFs require less storage space and allow more efficient access than unscoped
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
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
You can obtain a
REF to a row object by selecting the object from its object table and applying the
See Also:Oracle Database Application Developer's Guide - Object-Relational Features for examples of how to use
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.
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.
Creating an array type does not allocate space. It defines a datatype, which you can use as:
The datatype of a column of a relational table
An object type attribute
A PL/SQL variable, parameter, or function return type.
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
See Also:Oracle Database Application Developer's Guide - Object-Relational Features for more information about using
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 multicolumn table, with a column for each attribute of the object type. If compatibility is set to Oracle9i or higher, nested tables can contain other nested tables.
A table type definition does not allocate space. It defines a type, which you can use as:
The datatype of a column of a relational table
An object type attribute
A PL/SQL variable, parameter, or function return type
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.
A convenient way to access the elements of a nested table individually is to use a nested cursor.
Oracle Database Reference for information about nested cursors
Oracle Database Application Developer's Guide - Object-Relational Features for more information about using nested tables
An object type can be created as a subtype of an existing object type. A single inheritance model is supported: the subtype can be derived from only one parent type. A type inherits all the attributes and methods of its direct supertype. It can add new attributes and methods, and it can override any of the inherited methods.
Figure 27-1 illustrates two subtypes,
Employee_t, created under
Furthermore, a subtype can itself be refined by defining another subtype under it, thus building up type hierarchies. In the preceding diagram,
PartTimeStudent_t is derived from subtype
A type declaration must have the
FINAL keyword, if you want it to have subtypes. The default is that the type is
FINAL; that is, no subtypes can be created for the type. This allows for backward compatibility.
A type can be declared to be
INSTANTIABLE. This implies that there is no constructor (default or user-defined) for the type. Thus, it is not possible to construct instances of this type. Typically, you would define instantiable subtypes for such a type as follows:
CREATE TYPE Address_t AS OBJECT(...) NOT INSTANTIABLE NOT FINAL; CREATE TYPE USAddress_t UNDER Address_t(...); CREATE TYPE IntlAddress_t UNDER Address_t(...);
A method of a type can be declared to be
INSTANTIABLE. Declaring a method as
INSTANTIABLE means that the type is not providing an implementation for that method. Furthermore, a type that contains any non-instantiable methods must necessarily be declared
A subtype of a
INSTANTIABLE type can override any of the non-instantiable methods of the supertype and provide concrete implementations. If there are any non-instantiable methods remaining, the subtype must also necessarily be declared
A non-instantiable subtype can be defined under an instantiable supertype. Declaring a non-instantiable type to be
FINAL is not allowed.
Oracle supports a fixed set of aggregate functions, such as
SUM. These is also a mechanism to implement new aggregate functions with user-defined aggregation logic.
User-defined aggregate functions (UDAGs) refer to aggregate functions with user-specified aggregation semantics. Users can create a new aggregate function and provide the aggregation logic through a set of routines. After it is created, the user-defined aggregate function can be used in SQL DML statements in a manner similar to built-in aggregates. The Oracle database server evaluates the UDAG by invoking the user-provided aggregation routines appropriately.
Databases are increasingly being used to store complex data such as image, spatial, audio, video, and so on. The complex data is typically stored in the database using object types, opaque types, or LOBs. User-defined aggregates are primarily useful in specifying aggregation over such new domains of data.
Furthermore, UDAGs can be used to create new aggregate functions over traditional scalar datatypes for financial or scientific applications. Because it is not possible to provide native support for all forms of aggregates, it is desirable to provide application developers with a flexible mechanism to add new aggregate functions.
An object datatype can be referenced by any of the following schema objects:
Table or subtable
Type or subtype
Program unit (PL/SQL block): procedure, function, package, trigger
View (including object view)
When any of these objects references a type, either directly or indirectly through another type or subtype, it becomes a dependent object on that type. Whenever a type is modified, all dependent program units, views, operators and indextypes are marked invalid. The next time each of these invalid objects is referenced, it is revalidated, using the new type definition. If it is recompiled successfully, then it becomes valid and can be used again.
When a type has either type or table dependents, altering a type definition becomes more complicated because existing persistent data relies on the current type definition.
You can change an object type and propagate the type change to its dependent types and tables.
TYPE lets you add or drop methods and attributes from existing types and optionally propagate the changes to dependent types, tables, and even the table data. You can also modify certain attributes of a type.
Oracle Database SQL Reference for details about syntax
Oracle Database PL/SQL User's Guide and Reference for details about type specification and body compilation
Oracle Database Application Developer's Guide - Object-Relational Features for details about managing type versions
Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables from data—of either built-in or user-defined types—stored in the columns of relational or object tables in the database.
Object views provide the ability to offer specialized or restricted access to the data and objects in a database. For example, you can use an object view to provide a version of an employee object table that does not have attributes containing sensitive data and does not have a deletion method.
Object views allow the use of relational data in object-oriented applications. They let users:
Try object-oriented programming techniques without converting existing tables
Convert data gradually and transparently from relational tables to object-relational tables
Use legacy RDBMS data with existing object-oriented applications
You can fetch relational data into the client-side object cache and map it into C or C++ structures so 3GL applications can manipulate it just like native structures.
Object views provide a gradual upgrade path for legacy data. They provide for co-existence of relational and object-oriented applications, and they make it easier to introduce object-oriented applications to existing relational data without having to make a drastic change from one paradigm to another.
Object views provide the flexibility of looking at the same relational or object data in more than one way. Thus you can use different in-memory object representations for different applications without changing the way you store the data in the database.
Data in the rows of an object view can come from more than one table, but the object still traverses the network in one operation. When the instance is in the client side object cache, it appears to the programmer as a C or C++ structure or as a PL/SQL object variable. You can manipulate it like any other native structure.
You can refer to object views in SQL statements the same way you refer to an object table. For example, object views can appear in a
SELECT list, in an
SET clause, or in a
WHERE clause. You can also define object views on object views.
You can access object view data on the client side using the same OCI calls you use for objects from object tables. For example, you can use
OCIObjectPin for pinning a
OCIObjectFlush for flushing an object to the server. When you update or flush to the server an object in an object view, Oracle updates the object view.
See Also:Oracle Call Interface Programmer's Guide for more information about OCI calls
A view is not updatable if its view query contains joins, set operators, aggregate functions,
DISTINCT. If a view query contains pseudocolumns or expressions, the corresponding view columns are not updatable. Object views often involve joins.
OF triggers provide a transparent way to update object views or relational views. You write the same SQL DML (
UPDATE) statements as for an object table. Oracle invokes the appropriate trigger instead of the SQL statement, and the actions specified in the trigger body take place.
Oracle Database Application Developer's Guide - Object-Relational Features for a purchase order/line item example that uses an
A nested table can be modified by inserting new elements and updating or deleting existing elements. Nested table columns that are virtual or synthesized, as in a view, are not usually updatable. To overcome this, Oracle allows
OF triggers to be created on these columns.
OF trigger defined on a nested table column of a view is fired when the column is modified. If the entire collection is replaced by an update of the parent row, then the
OF trigger on the nested table column is not fired.
See Also:Oracle Database Application Developer's Guide - Fundamentals for a purchase order/line item example that uses an
OFtrigger on a nested table column
An object view can be created as a subview of another object view. The type of the superview must be the immediate supertype of the type of the object view being created. Thus, you can build an object view hierarchy which has a one-to-one correspondence to the type hierarchy. This does not imply that every view hierarchy must span the entire corresponding type hierarchy. The view hierarchy can be rooted at any subtype of the type hierarchy. Furthermore, it does not have to encompass the entire subhierarchy.
Figure 27-2 illustrates multiple view hierarchies.
By default, the rows of an object view in a view hierarchy include all the rows of all its subviews (direct and indirect) projected over the columns of the given view.
Only one object view can be created as a subview of a given view corresponding to the given subtype; that is, the same view cannot participate in many different view hierarchies. An object view can be created as a subview of only one superview; multiple inheritance is not supported.
The subview inherits the object identifier from its superview and cannot be explicitly specified in any subview.