4
Applying an Object Model to Relational Data

This chapter shows how to write object-oriented applications without changing the underlying structure of your relational data:

Why to Use Object Views

Just as a view is a virtual table, an object view is a virtual object table. Each row in the view is an object: you can call its methods, access its attributes using the dot notation, and create a REF that points to it.

Object views are useful in prototyping or transitioning to object-oriented applications, because the data in the view can be taken from relational tables and accessed as if the table were defined as an object table. You can run object-oriented applications without converting existing tables to a different physical structure.

Object views provide the same features as traditional views, applied to object data. For example, you might provide an object view of an employee table that doesn't have attributes containing sensitive data and doesn't have a deletion method.

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 structs or C++ or Java classes, so 3GL applications can manipulate it just like native classes. You can also use object-oriented features like complex object retrieval with relational data.

By synthesizing objects from relational data, you can query the data in new ways. You can view data from multiple tables by using object de-referencing instead of writing complex joins with multiple tables.
Since the objects in the view are processed within the server, not on the client, this can result in significantly fewer SQL statements and much less network traffic.
The object data from object views can be pinned and used in the client side object cache. When you retrieve these synthesized objects in the object cache by means of specialized object-retrieval mechanisms, you reduce network traffic.
You gain great flexibility when you create an object model within a view in that you can continue to develop the model. If you need to alter an object type, you can simply replace the invalidated views with a new definition.
Using objects in views does not place any restrictions on the characteristics of the underlying storage mechanisms. By the same token, you are not limited by the restrictions of current technology. For example, you can synthesize objects from relational tables which are parallelized and partitioned.

You can create different complex data models from the same underlying data.

See Also:

Oracle8i SQL Reference for a complete description of SQL syntax and usage.

PL/SQL User's Guide and Reference for a complete discussion of PL/SQL capabilities

Oracle8i Java Stored Procedures Developer's Guide for a complete discussion of Java.

Oracle Call Interface Programmer's Guide for a complete discussion of those facilities.

Defining Object Views

The procedure for defining an object view is:

Define an object type, where each attribute of the type corresponds to an existing column in a relational table.
Write a query that specifies how to extract the data from relational tables. Specify the columns in the same order as the attributes in the object type.
Specify a unique value, based on attributes of the underlying data, to serve as an object identifier, which allows you to create pointers (REFs) to the objects in the view. You can often use an existing primary key.

If you want to be able to update an object view, you may have to take another step, if the attributes of the object type do not correspond exactly to columns in existing tables:

Write an INSTEAD OF trigger procedure (see "Updating Object Views") for Oracle to execute whenever an application program tries to update data in the object view.

After these steps, you can use an object view just like an object table.

For example, the following SQL statements define an object view, where each row in the view is an object of type EMPLOYEE_T:

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

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

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

To access the data from the EMPNUM column of the relational table, you would access the EMPNO attribute of the object type.

Using Object Views in Applications

Data in the rows of an object view may 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.

Additional Information:
See Oracle Call Interface Programmer's Guide for more information about OCI calls.

Nesting Objects in Object Views

If one of the attributes of an object type is itself an object type, you must extract suitable column objects from the relational data. You can either select them from a column object that already exists in the relational table, or synthesize them from a set of relational columns using the appropriate type constructor.

For example, consider the department table dept:

CREATE TABLE dept
(
    deptno       NUMBER PRIMARY KEY,
    deptname     VARCHAR2(20),
    deptstreet   VARCHAR2(20),
    deptcity     VARCHAR2(10),
    deptstate    CHAR(2),
    deptzip      VARCHAR2(10)
 );

You might want to create an object view where the addresses are objects inside the department objects. That would allow you to define reusable methods for address objects, and use them for all kinds of addresses.

Create the type for the address object:

CREATE TYPE address_t AS OBJECT 
(
   street   VARCHAR2(20),
    city    VARCHAR2(10),
    state   CHAR(2),
    zip     VARCHAR2(10)
);
/

Create the view containing the department number, name and address. The address is constructed from several columns of the relational table.

CREATE VIEW dept_view AS 
  SELECT d.deptno, d.deptname, 
               address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS 
deptaddr
   FROM   dept d;

Identifying Null Objects in Object Views

Because the constructor for an object never returns a null, none of the address objects in the above view can ever be null, even if the city, street, and so on columns in the relational table are all null. The relational table has no column that specifies whether the department address is null. If we define a convention so that a null deptstreet column indicates that the whole address is null, then we can capture the logic using the DECODE function, or some other function, to return either a null or the constructed object:

CREATE VIEW dept_view AS
  SELECT d.deptno, d.deptname,
        DECODE(d.deptstreet, NULL, NULL, 
            address_t(d.deptstreet, d.deptcity, d.deptstate, d.deptzip)) AS 
deptaddr
  FROM dept d;

Using such a technique makes it impossible to directly update the department address through the view, because it does not correspond directly to a column in the relational table. Instead, we would define an INSTEAD-OF trigger over the view to handle updates to this column.

Using Nested Tables and Varrays in Object Views

Collections, both nested tables and VARRAYs, can be columns in views. You can select these collections from underlying collection columns or you can synthesize them using subqueries. The CAST-MULTISET operator provides a way of synthesizing such collections.

Taking the previous example as our starting point, we represent each employee in an emp relational table with following structure:

CREATE TABLE emp
(
   empno    NUMBER PRIMARY KEY,
   empname  VARCHAR2(20),
   salary   NUMBER,
   deptno   NUMBER REFERENCES dept(deptno)
);

Using this relational table, we can construct a dept_view with the department number, name, address and a collection of employees belonging to the department.

Define a employee type and a nested table type for the employee type:

CREATE TYPE employee_t AS OBJECT
(
  eno NUMBER,
  ename VARCHAR2(20),
  salary  NUMBER
);

CREATE TYPE employee_list_t AS TABLE OF employee_t;

The dept_view can now be defined:

CREATE VIEW dept_view AS 
  SELECT d.deptno, d.deptname, 
         address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip) AS deptaddr,
             CAST( MULTISET (
                           SELECT e.empno, e.empname, e.salary
                           FROM emp e 
                           WHERE e.deptno = d.deptno) 
                        AS employee_list_t)
                   AS emp_list
   FROM   dept d;

The SELECT subquery inside the CAST-MULTISET block selects the list of employees that belong to the current department. The MULTISET keyword indicates that this is a list as opposed to a singleton value. The CAST operator casts the result set into the appropriate type, in this case to the employee_list_t collection type.

A query on this view could give us the list of departments, with each department row containing the department number, name, the address object and a collection of employees belonging to the department.

Specifying Object Identifiers for Object Views

You can construct pointers (REFs) to the row objects in an object view. Since the view data is not stored persistently, you must specify a set of distinct values to be used as object identifiers. The notion of object identifiers allows the objects in object views to be referenced and pinned in the object cache.

If the view is based on an object table or an object view, then there is already an object identifier associated with each row and you can reuse them. Either omit the WITH OBJECT IDENTIFIER clause, or specify WITH OBJECT IDENTIFIER DEFAULT.

However, if the row object is synthesized from relational data, you must choose some other set of values.

Oracle lets you specify object identifiers based on the primary key. The set of unique keys that identify the row object is turned into an identifier for the object. These values must be unique within the rows selected out of the view, since duplicates would lead to problems during navigation through object references.

The object view created with the WITH OBJECT IDENTIFIER clause has an object identifier derived from the primary key. If the WITH OBJECT IDENTIFIER DEFAULT clause is specified, the object identifier is either system generated or primary key based, depending on the underlying table or view definition.

Continuing with our department example, we can create a dept_view object view that uses the department number as the object identifier:

Define the object type for the row, in this case the dept_t department type:

CREATE TYPE dept_t AS OBJECT
(
  dno        NUMBER,
  dname      VARCHAR2(20),
  deptaddr   address_t,
  emplist    employee_list_t
);

Because the underlying relational table has deptno as the primary key, each department row has a unique department number. In the view, the deptno column becomes the dno attribute of the object type. Once we know that dno is unique within the view objects, we can specify it as the object identier:

CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) 
   AS SELECT d.deptno, d.deptname, 
                address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip),
                CAST( MULTISET (
                           SELECT e.empno, e.empname, e.salary
                           FROM emp e 
                           WHERE e.deptno = d.deptno) 
                      AS employee_list_t)
   FROM   dept d;

See Also:
Object Identifiers

Creating References to View Objects

In the example we have been developing, each object selected out of the dept_view view has a unique object identifier derived from the department number value. In the relational case, the foreign key deptno in the emp employee table matches the deptno primary key value in the dept department table. We used the primary key value for creating the object identifier in the dept_view. This allows us to use the foreign key value in the emp_view in creating a reference to the primary key value in dept_view.

We accomplish this by using MAKE_REF operator to synthesize a primary key object reference. This takes the view or table name to which the reference points and a list of foreign key values to create the object identifier portion of the reference that will match with a particular object in the referenced view.

In order to create an emp_view view which has the employee's number, name, salary and a reference to the department in which she works, we need first to create the employee type emp_t and then the view based on that type

CREATE TYPE emp_t AS OBJECT
(
  eno      NUMBER,
  ename    VARCHAR2(20),
  salary   NUMBER,
  deptref  REF dept_t
);

CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno)
   AS SELECT e.empno, e.empname, e.salary, 
                      MAKE_REF(dept_view, e.deptno) 
         FROM emp e;

The deptref column in the view holds the department reference. We write the following simple query to determine all employees whose department is located in the city of San Francisco:

SELECT e.eno, e.salary, e.deptref.dno
FROM emp_view e
WHERE e.deptref.deptaddr.city = `San Francisco';

Note that we could also have used the REF modifier to get the reference to the dept_view objects:

CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno)
   AS SELECT e.empno, e.empname, e.salary, REF(d)
         FROM emp e, dept_view d
          WHERE e.deptno = d.dno;

In this case we join the dept_view and the emp table on the deptno key. The advantage of using MAKE_REF operator instead of the REF modifier is that in using the former, we can create circular references. For example, we can create employee view to have a reference to the department in which she works, and the department view can have a list of references to the employees who work in that department.

Note that if the object view has a primary key based object identifier, the reference to such a view is primary key based. On the other hand, a reference to a view with system generated object identifier will be a system generated object reference. This difference is only relevant when you create object instances in the OCI object cache and need to get the reference to the newly created objects. This is explained in a later section.

As with synthesized objects, we can also select persistently stored references as view columns and use them seamlessly in queries. However, the object references to view objects cannot be stored persistently.

Modelling Inverse Relationships with Object Views

Views with objects can be used to model inverse relationships.

One-to-One Relationships

One-to-one relationships can be modeled with inverse object references. For example, let us say that each employee has a particular computer on her desk, and that the computer belongs to that employee only. A relational model would capture this using foreign keys either from the computer table to the employee table, or in the reverse direction. Using views, we can model the objects so that we have an object reference from the employee to the computer object and also have a reference from the computer object to the employee.

One-to-Many and One-to-Many Relationships

One-to-many relationships (or many-to-many relationships) can be modeled either by using object references or by embedding the objects. One-to-many relationship can be modeled by having a collection of objects or object references. The many-to-one side of the relationship can be modeled using object references.

Consider the department-employee case. In the underlying relational model, we have the foreign key in the employee table. Using collections in views, we can model the relationship between departments and employees. The department view can have a collection of employees, and the employee view can have a reference to the department (or inline the department values). This gives us both the forward relation (from employee to department) and the inverse relation (department to list of employees). The department view can also have a collection of references to employee objects instead of embedding the employee objects.

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

Updating 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. Note that if the entire collection is replaced (by an update of the parent row), the INSTEAD OF trigger on the nested table column is not fired.

Using INSTEAD-OF Triggers to Control Mutating and Validation

INSTEAD-OF triggers provide a way of updating complex views that otherwise could not be updated. They can also be used to enforce constraints, check privileges and validate the DML. Using these triggers, you can control mutation of the objects created though an object view that might be caused by inserting, updating and deleting.

For instance, suppose we wanted to enforce the condition that the number of employees in a department cannot exceed 10. To enforce this, we can write an INSTEAD-OF trigger for the employee view. The trigger is not needed for doing the DML since the view can be updated, but we need it to enforce the constraint.

We implement the trigger by means of the following code:

CREATE TRIGGER emp_instr INSTEAD OF INSERT on emp_view 
FOR EACH ROW
DECLARE
  dept_var dept_t;
  emp_count integer;
BEGIN
  -- Enforce the constraint..!
  -- First get the department number from the reference
  UTL_REF.SELECT_OBJECT(:NEW.deptref,dept_var);

  SELECT COUNT(*) INTO emp_count
  FROM emp
  WHERE deptno = dept_var.dno;

  IF emp_count < 9 THEN
       -- let us do the insert
      INSERT INTO emp VALUES (:NEW.eno,:NEW.ename,:NEW.salary,dept_var.dno);
  END IF;
END;

Applying the Object Model to Remote Tables

Although you cannot directly access remote tables as object tables, object views let you access remote tables as if they were object tables.

Consider a company with two branches -- one in Washington D.C., and another in Chicago. Each site has an employee table. The headquarters in Washington has a department table with the list of all the departments. To get a total view of the entire organization, we can create views over the individual remote tables and then a overall view of the organization.

First, we create an object view for each employee table:

CREATE VIEW emp_washington_view (eno,ename,salary)
   AS SELECT e.empno, e.empname, e.salary
          FROM   emp@washington_link e;

CREATE VIEW emp_chicago_view
   AS SELECT e.eno, e.name, e.salary
          FROM   emp_tab@chicago_link e;

We can now create the global view:

CREATE VIEW orgnzn_view  OF dept_t WITH OBJECT IDENTIFIER  (dno) 
    AS SELECT d.deptno, d.deptname,
              address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip),
              CAST( MULTISET (
                      SELECT e.eno, e.ename, e.salary
                      FROM emp_washington_view e) 
                   AS employee_list_t) 
       FROM   dept d
       WHERE d.deptcity = `Washington'
   UNION ALL
       SELECT d.deptno, d.deptname,
              address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip),
              CAST( MULTISET (
                       SELECT e.eno, e.name, e.salary
                       FROM emp_chicago_view e) 
                    AS employee_list_t)
       FROM   dept d
       WHERE d.deptcity = `Chicago';

This view has the list of all employees for each department. We use UNION ALL since we cannot have two employees working in more than one department. If we had to deal with that eventuality, we could use a UNION of the rows. However, one caveat in using the UNION operator is that we need to introduce an ORDER BY operator within the CAST-MULTISET expressions so that the comparison of two collections is performed properly.

Defining Complex Relationships in Object Views

You can define circular references in object views using the MAKE_REF operator: view_A can refer to view_B which in turn can refer to view_A. This allows an object view to synthesize a complex structure such as a graph from relational data.

For example, in the case of the department and employee, the department object currently includes a list of employees. To conserve space, we may want to put references to the employee objects inside the department object, instead of materializing all the employees within the department object. We can construct ("pin") the references to employee objects, and later follow the references using the dot notation to extract employee information.

Because the employee object already has a reference to the department in which the employee works, an object view over this model contains circular references between the department view and the employee view.

You can create circular references between object views in two different ways.

Method 1: Create Both Views

Create view A without any reference to view B.
Create view B, which includes a reference to view A.
Replace view A with a new definition that includes the reference to view B.

Method 2:

Create view A with the reference to view B using the FORCE keyword.
Create view B with reference to view A. When view A is used, it is validated and re-compiled.

Method 2 has fewer steps, but the FORCE keyword may hide errors in the view creation. You need to query the USER_ERRORS catalog view to see if there were any errors during the view creation. Use this method only if you are sure that there are no errors in the view creation statement.

Also, if errors prevent the views from being recompiled upon use, you must recompile them manually using the ALTER VIEW COMPILE command.

We will see the implementation for both the methods.

Tables and Types to Demonstrate Circular View References

First, we set up some relational tables and associated object types. Although the tables contain some objects, they are not object tables. To access the data objects, we will create object views later.

The emp table stores the employee information:

CREATE TABLE emp
(
   empno    NUMBER PRIMARY KEY,
   empname  VARCHAR2(20),
   salary   NUMBER,
   deptno   NUMBER
);

The emp_t type contains a reference to the department. We need a dummy department type so that the emp_t type creation succeeds.

CREATE TYPE dept_t;
/

The employee type includes a reference to the department:

CREATE TYPE emp_t AS OBJECT
(
  eno NUMBER,
  ename VARCHAR2(20),
  salary  NUMBER,
  deptref REF dept_t
);
/

We represent the list of references to employees as a nested table:

CREATE TYPE employee_list_ref_t AS TABLE OF REF emp_t;
/

The department table is a typical relational table:

CREATE TABLE dept
(
    deptno        NUMBER PRIMARY KEY,
    deptname      VARCHAR2(20),
    deptstreet    VARCHAR2(20),
    deptcity      VARCHAR2(10),
    deptstate     CHAR(2),
    deptzip       VARCHAR2(10)
 );

To create object views, we need object types that map to columns from the relational tables:

CREATE TYPE address_t AS OBJECT 
(
   street        VARCHAR2(20),
    city         VARCHAR2(10),
    state        CHAR(2),
    zip          VARCHAR2(10)
);
/

We earlier created an incomplete type; now we fill in its definition:

CREATE OR REPLACE TYPE dept_t AS OBJECT
(
  dno           NUMBER,
  dname         VARCHAR2(20),
  deptaddr      address_t,
  empreflist    employee_list_ref_t
);
/

Creating Object Views with Circular References

Now that we have the underlying relational table definitions, we create the object views on top of them.

Method 1: Views compiled now.

We first create the employee view with a null in the deptref column. Later, we will turn that column into a reference.

CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno)
   AS SELECT e.empno, e.empname, e.salary, 
                       NULL
         FROM emp e;

Next, we create the department view, which includes references to the employee objects.


CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) 
   AS SELECT d.deptno, d.deptname, 
                address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip),
                CAST( MULTISET (
                           SELECT MAKE_REF(emp_view, e.empno)
                           FROM emp e 
                           WHERE e.deptno = d.deptno) 
                        AS employee_list_ref_t)
   FROM   dept d;

We create a list of references to employee objects, instead of including the entire employee object. We now re-create the employee view with the reference to the department view.

CREATE OR REPLACE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno)
   AS SELECT e.empno, e.empname, e.salary, 
                       MAKE_REF(dept_view, e.deptno)
         FROM emp e;

This creates the views.

Method 2: Views compiled upon use.

If we are sure that the view creation statement has no syntax errors, we can use the FORCE keyword to force the creation of the first view without the other view being present.

First, we create an employee view that includes a reference to the department view, which does not exist at this point. This view cannot be queried until the department view is created properly.

CREATE FORCE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno)
   AS SELECT e.empno, e.empname, e.salary, 
                       MAKE_REF(dept_view, e.deptno)
         FROM emp e;

Next, we create a department view that includes references to the employee objects. We do not have to use the FORCE keyword here, since emp_view already exists.

CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) 
   AS SELECT d.deptno, d.deptname, 
                address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip),
                CAST( MULTISET (
                           SELECT MAKE_REF(emp_view, e.empno)
                           FROM emp e 
                           WHERE e.deptno = d.deptno) 
                        AS employee_list_ref_t)
   FROM   dept d;

This allows us to query the department view, getting the employee object by de-referencing the employee reference from the nested table empreflist:

SELECT DEREF(e.COLUMN_VALUE)
FROM TABLE( SELECT e.empreflist FROM dept_view e WHERE e.dno = 100) e;

COLUMN_VALUE is a special name that represents the scalar value in a scalar nested table. In this case, COLUMN_VALUE denotes the reference to the employee objects in the nested table empreflist.

We can also access only the employee number of all those employees whose name begins with "John".

SELECT e.COLUMN_VALUE.eno
FROM TABLE(SELECT e.empreflist FROM dept_view e WHERE e.dno = 100) e
WHERE e.COLUMN_VALUE.ename like `John%';

To get a tabular output, unnest the list of references by joining the department table with the items in its nested table:

SELECT d.dno, e.COLUMN_VALUE.eno, e.COLUMN_VALUE.ename
FROM dept_view d, TABLE(d.empreflist) e
WHERE e.COLUMN_VALUE.ename like `John%' 
 AND   d.dno = 100;

Finally, we can rewrite the above query to use the emp_view instead of the dept_view to show how you can navigate from one view to the other:

SELECT e.deptref.dno, DEREF(f.COLUMN_VALUE)
FROM emp_view e, TABLE(e.deptref.empreflist) f
WHERE e.deptref.dno = 100 
AND f.COLUMN_VALUE.ename like `John%';

4 Applying an Object Model to Relational Data