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:
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>)
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 ] )
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.