15
Object Views

This chapter describes object views. It contains the following major sections:

• Introduction to Object Views

• How Object Views Are Defined

• Use of Object Views

• Updates of Object Views

• View Hierarchies

  See Also: Oracle9i Application Developer's Guide - Object-Relational Features

Introduction to Object Views

Just as a view is a virtual table, an object view is a virtual object table.

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

Advantages of Object Views

Using object views can lead to better performance. Relational data that make up a row of an object view traverse the network as a unit, potentially saving many round trips.

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 migration 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.

How Object Views Are Defined

Conceptually, the process of defining an object view is simple. It consists of the following actions:

• Defining an object type to be represented by rows of the object view.

• Writing a query that specifies which data in which relational tables contain the attributes for objects of that type.

• Specifying an object identifier, based on attributes of the underlying data, to allow REFs to the objects (rows) of the object view.

The object identifier corresponds to the unique object identifier that Oracle generates automatically for rows of object tables. In the case of object views, however, the declaration must specify something that is unique in the underlying data (for example, a primary key).

If the object view is based on a table or another object view and you do not specify an object identifier, Oracle uses the object identifier from the original table or object view.

If you want to be able to update a complex object view, you may have to take another action:

• Write an INSTEAD OF trigger procedure for Oracle to execute whenever an application program tries to update data in the object view.

After doing these four things, you can use an object view just like an object table.

For example, the following SQL statements define an object view:

CREATE TABLE emp_table  (
    empnum   NUMBER (5),
    ename    VARCHAR2 (20),
    salary   NUMBER (9, 2),
    job      VARCHAR2 (20) );

CREATE TYPE employee_t  AS OBJECT(
    empno    NUMBER (5),
    ename    VARCHAR2 (20),
    salary   NUMBER (9, 2),
    job      VARCHAR2 (20) );

CREATE VIEW emp_view1 OF employee_t
    WITH OBJECT OID (empno) AS
        SELECT   e.empnum, e.ename, e.salary, e.job
            FROM     emp_table e
            WHERE    job = 'Developer';

The object view looks to the user like an object table whose underlying type is employee_t. Each row contains an object of type employee_t. Each row has a unique object identifier.

Oracle constructs the object identifier based on the specified key. In most cases, it is the primary key of the base table. If the query that defines the object view involves joins, however, you must provide a key across all tables involved in the joins, so that the key still uniquely identifies rows of the object view.

Note: Columns in the WITH OBJECT OID clause (empno in the example) must also be attributes of the underlying object type (employee_t in the example). This makes it easy for trigger programs to identify the corresponding row in the base table uniquely.

See Also: Oracle9i Database Administrator's Guide for specific directions for defining object views "Updates of Object Views" for more information about writing an INSTEAD OF trigger

Use of Object Views

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 UPDATE 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 REF and 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

Updates of Object Views

You can update, insert, and delete the data in an object view using the same SQL DML you use for object tables. Oracle updates the base tables of the object view if there is no ambiguity.

A view is not updatable if its view query contains joins, set operators, aggregate functions, GROUP BY, or DISTINCT. If a view query contains pseudocolumns or expressions, the corresponding view columns are not updatable. Object views often involve joins.

To overcome these obstacles Oracle provides INSTEAD OF triggers. They are called INSTEAD OF triggers because Oracle executes the trigger body instead of the actual DML statement.

INSTEAD OF triggers provide a transparent way to update object views or relational views. You write the same SQL DML (INSERT, DELETE, and 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.

See Also: Oracle9i Application Developer's Guide - Object-Relational Features for a purchase order/line item example that uses an INSTEAD OF trigger Chapter 18, "Triggers"

Updates of Nested Table Columns in Views

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 INSTEAD OF triggers to be created on these columns.

The INSTEAD 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 INSTEAD OF trigger on the nested table column is not fired.

See Also: Oracle9i Application Developer's Guide - Fundamentals for a purchase order/line item example that uses an INSTEAD OF trigger on a nested table column

View Hierarchies

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 15-1 Multiple View Hierarchies

Text description of the illustration scn81149.gif

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 (OID) from its superview and cannot be explicitly specified in any subview.

Example of Creating a View Hierarchy

CREATE VIEW person_v OF person_t 
  WITH OBJECT ID(ssn) AS
  SELECT ssn, name, address FROM allpersons WHERE typeid = 1;

CREATE VIEW student_v of student_t UNDER person_v AS
  SELECT ssn, name, address, deptid, major FROM allpersons
  WHERE typeid = 2;

The query underlying a view determines if the view is inherently updatable. This applies to subviews as well. Instead-of triggers can be defined on subviews to perform appropriate DML actions.

Instead-of triggers are not inherited by subviews.