Now that we've looked at one example and considered what INSTEAD OF triggers can offer, let's examine with more rigor the syntax required to create your own object views.
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:
The name of the preexisting user-defined object type that this view will emulate.
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...
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 (!).
Comma-separated list of type attributes which comprise a (usually unique) identifier.
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.
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).
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.
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>;
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>)
The view from which you wish to derive a REF value (which other object views may reference).
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 ] )
 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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.