[Chapter 20] 20.7 Object Views Housekeeping

20.7 Object Views Housekeeping

There are a few commands and facilities we haven't yet discussed that will help you to manage and control object views.

20.7.1 Data Dictionary

The USER_VIEWS (or ALL_VIEWS or DBA_VIEWS) view in the data dictionary includes several columns specific to object views. Table 20.2 lists some examples of using this information.

Table 20.2: Entries for Object ViewsData Dictionary
To Answer the Question...	Use This View	As In
What object views have I created?	USER_VIEWS	SELECT view_name FROM user_views WHERE type_text IS NOT NULL;
What object views have I created of the Foo_t type?	USER_VIEWS	SELECT view_name FROM user_views WHERE UPPER(type_text) LIKE 'FOO_T%';
What is the virtual OID of the Foo_v view?	USER_VIEWS	SELECT oid_text FROM user_views WHERE UPPER(type_text) LIKE 'FOO_T%';
What is the query on which I defined the Foo view?	USER_VIEWS	SET LONG 1000 -- or greater SELECT text FROM user_views WHERE view_name = 'FOO';
What columns are in view Foo?	USER_TAB_COLUMNS	SELECT column_name, data_type_ mod, data_type FROM user_tab_columns WHERE table_name = 'FOO';

Another way of getting a quick listing of the columns in a view is to "describe" it in SQL*Plus:

SQL> desc images_v

Name                            Null?    Type
------------------------------- -------- ----
IMAGE_ID                                 NUMBER(38)
FILE_NAME                                VARCHAR2(512)
FILE_TYPE                                VARCHAR2(12)
BYTES                                    NUMBER(38)
ARTIST_REF                               REF OF ARTIST_T
KEYWORDS                                 KEYWORD_TAB_T

20.7.2 Privileges

As with conventional views, creating an object view requires the creator to have either the CREATE VIEW or the CREATE ANY VIEW system privilege.

If you are attempting to create an instance of an object type by using the default constructor, you must have the EXECUTE privilege on that type.

However, if a user has a DML privilege on a view that has an INSTEAD OF trigger for that DML operation, he doesn't need explicit EXECUTE privileges if the trigger invokes a constructor; the trigger runs with the "owner rights model" described in Chapter 18.

20.7.3 Forcing Compilation

As with conventional views, if you make changes to an underlying database object on which an object view is based, Oracle will mark the view as "invalid." When this happens, Oracle will also mark as invalid any other database objects (such as other views, INSTEAD OF triggers, or PL/SQL packages) that depend on the view. Then, the next time that you use the view, Oracle is supposed to attempt to "recompile" it first.

However, when you are modifying schema, there are some cases where Oracle does not seem to automatically recompile the view. One case in particular occurs when you CREATE OR REPLACE, or otherwise recompile, the object type on which the view is defined. When this happens, your view becomes invalid, leading to some disconcerting error messages; at this point, you must either recreate or recompile the object view. The command to compile the view is:

ALTER VIEW <view name> COMPILE;

If compilation fails, Oracle should return an error message, and the view will remain "invalid." You may need to redefine the view using, for example, CREATE OR REPLACE VIEW.

The ALTER VIEW ... COMPILE command has the side effect of marking as invalid all database objects that are dependent on the view, such as other views or types. Oracle does appear to automatically recompile these dependent database objects.

Similarly, INSTEAD OF triggers can be recompiled using the following:

ALTER TRIGGER <trigger name> COMPILE [ DEBUG ];


20.6 Schema Evolution		20.8 Postscript: Using the BFILE Datatype