16.6 How do I…Create and use object views?
Problem
My database contains some relational tables that are good candidates for object-oriented enhancement. Unfortunately, some existing applications use these tables and I don’t have the development resources to rewrite these legacy applications. How can I use object-oriented design techniques to model these tables without invalidating my existing database structures and applications?
Technique
With object views, users can utilize object-oriented programming and modeling without making pervasive changes to existing database structures. Object retrievals, moreover, sometimes result in client/server performance enhance-ments because they allow multi-table retrievals with fewer network trips to the database server.
To create object views on existing relational tables, start by defining the nec-essary object types, then create the object view using a SELECT statement that extracts the appropriate columns from the relational tables.
Steps
1. Start SQL*Plus and use the START command to load and execute the script CHP16_17.SQL. This script creates the familiar EMP and DEPT tables that user SCOTT owns in the test database; the script’s output appears in Figure 16.17.
Oracle8 uses the primary and foreign key structures in the EMP_TABLE16 and DEPT_TABLE16 tables to enforce the master-detail relationship between them.
2. Use the START command to load and execute the script CHP16_18.SQL as shown in Figure 16.18.
3. Load and execute the CHP16_19.SQL script using the start command as shown in Figure 16.19.
Notice that the DEPT_TYPE16 type looks exactly like the DEPT_TABLE16 table and that the EMP_TYPE16 object is almost identical to the EMP_TABLE16 table except that the last column is an embedded object of type DEPT_TYPE16 instead of a scalar attribute.
4. Load and execute the CHP16_20.SQL script to create the object view, as shown in Figure 16.20.
In line 3, the CREATE VIEW statement specifies that the primary key of the base table will be used as the object identifier for the view. In line 5, the statement uses the DEPT_TYPE16 constructor to instantiate the em-bedded DEPT object in each materialized row of the view. Notice that the outer join operator appears in line 7 of the script so that employee records with null values for the department items will still appear in the view.
How It Works
Start with some existing relational tables like those created and populated in Steps 1 and 2. Step 3 defined object types to model the relationships between the relational objects, and the script in Step 4 created an object view using these object types.
Comments
Object views come in two flavors; those with an object identifier and those without one. If the object view is based on a relational table then the WITH OBJECT OID clause is required so that Oracle8 can uniquely map each row in the materialized view with a row in the underlying relational table. The beta release documentation, which suggests it is possible to create an object view on an underlying relational table without the WITH OBJECT OID clause, is incorrect and generates a server error message as shown in Listing 16.3.
Listing 16.3 Attempting to generate a typed object view without the WITH OBJECT OID clause does not work
SQL> CREATE VIEW dept_oview16 OF dept_type16 AS
2 SELECT deptno, dname, loc
3 FROM dept_table16
4 WHERE deptno <= 20;
CREATE VIEW dept_oview16 OF dept_type16 AS
*
ERROR at line 1:
ORA-22974: missing WITH OBJECT OID clause