20.3.1 CREATE VIEW: Creating an Object View

This is the basic syntax for creating an object view:

CREATE [ OR REPLACE ] VIEW <view name>
   OF <object type name>
   [ WITH OBJECT OID DEFAULT | (<attribute list>) ]
   AS <query>
   [ WITH [ READ ONLY | CHECK OPTION ]];

Note that we've omitted some of the optional keywords, such as FORCE and CONSTRAINT, from this syntax discussion. The elements are as follows:

OF object type name

The name of the preexisting user-defined object type that this view will emulate.

WITH OBJECT OID

Indicates only that the OID specification follows. (It's a bit strange that the clause requires all three keywords. It seems grammatically sufficient to me to say WITH OID, which should mean "with object identifier," as opposed to WITH OBJECT OID, which means "with object object (sic) identifier." Must have been a late night at the syntax factory...

DEFAULT

In the event that the object view is defined on an underlying object table or object view, you can tell Oracle to use the OID of the underlying object. If your view is eligible to use DEFAULT, the result is the same whether you include or omit the WITH OBJECT OID DEFAULT clause. That is, the default is DEFAULT (!).

attribute list

Comma-separated list of type attributes which comprise a (usually unique) identifier.

query

Your query must retrieve columns or expressions that match one for one, in order, the individual attributes of the object type. The datatype of each SELECTed expression must also match, or be type-compatible with, the corresponding attribute defined in the object type. The maximum number of columns or expressions is 1000.

WITH CHECK OPTION

If your view is updateable -- either because it is inherently updateable or because you have created an INSTEAD OF trigger -- this option will prevent inserts or updates of data that cannot subsequently be selected (for example, because of a WHERE clause restriction).

WITH READ ONLY

Prevents any DML operation from being executed. This clause takes precedence over INSTEAD OF triggers.

It's also important to note what is missing. Conventional views may use an alias clause; that is, a comma-separated list of names that Oracle will assign, in order, to the columns of the view. By contrast, you cannot use an alias clause in an object view. Instead, the object view always derives its list of column (attribute) names from the attribute names of the underlying type.

20.3.2 DROP: Dropping Views and Triggers

There is no syntactic difference between dropping a conventional view and dropping an object view. Both are accomplished using the command:

DROP VIEW <view name>;

Dropping a view has the side effect of dropping any INSTEAD OF triggers that you have created on the view. Of course, you can drop INSTEAD OF triggers explicitly, using the following:

DROP TRIGGER <trigger name>;

20.3.3 MAKE_REF: Returning a Virtual REF

The MAKE_REF function returns a "virtual REF" for an object view. (REFs are described in Chapter 18.) Its syntax is:

MAKE_REF (<view name>, <value list>)

Where:

view name

The view from which you wish to derive a REF value (which other object views may reference).

value list

Comma-separated list of column values whose datatype must match one for one with the OID attribute(s) of <view name>.

As a generic example, let's say that we have a table foo, and we define a corresponding object type and object view:

CREATE TABLE foo (
   id NUMBER PRIMARY KEY,  -- defining it as a PK is optional
   name VARCHAR2(30)
);

CREATE TYPE Foo_t AS OBJECT (
   id NUMBER,
   name VARCHAR2(30)
);

CREATE VIEW foo_v
   OF Foo_t
   WITH OBJECT OID (id)
AS
   SELECT id, name
     FROM foo;

Now we can use MAKE_REF in an any statement, including something as simple as:

SELECT MAKE_REF(foo_v, 123) FROM DUAL;

This statement will return a REF to the virtual object with id = 123. (Although you will see a result for this query when you execute it from SQL*Plus, Oracle's earlier admonition still applies: don't attempt to store this value anywhere. Incidentally, this query causes the ORA-00932 error, "inconsistent datatypes," in SQL Worksheet.[2] )

[2] SQL Worksheet is an SQL interpreter that ships with Oracle Enterprise Manager. SQL Worksheet has one significant advantage over SQL*Plus: it retains in memory a good number of your recently issued statements, allowing you to retrieve and edit SQL statements or PL/SQL code easily. I developed many of the examples for the objects chapters of this book using this tool.

If you want to construct a REF via the foo_v view for object 123, the record in the foo table with id = 123 does not even need to exist! MAKE_REF merely applies an internal Oracle algorithm to the supplied arguments to derive a REF; it does not read the foo_v view to determine whether the object really exists in the underlying table.

One final note about MAKE_REF: you might be tempted to call MAKE_REF natively in PL/SQL:

DECLARE
   foo_ref REF Foo_t;
BEGIN
   foo_ref := MAKE_REF (foo_v, 123);    -- invalid
END;

But that statement fails with the error PLS-00201, "identifier 'MAKE_REF' must be declared." You might also try the following:

DECLARE
   foo_ref REF Foo_t;
BEGIN
   SELECT MAKE_REF(foo_v, 123)    -- invalid
     INTO foo_ref
     FROM DUAL;
END;

But this too fails, at least in Oracle 8.0.3. This behavior is a suspected bug.