Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 20.6 Schema EvolutionChapter 20
Object Views
Next: 20.8 Postscript: Using the BFILE Datatype
 

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 ];


Previous: 20.6 Schema EvolutionOracle PL/SQL Programming, 2nd EditionNext: 20.8 Postscript: Using the BFILE Datatype
20.6 Schema EvolutionBook Index20.8 Postscript: Using the BFILE Datatype

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference