20.7 Object Views Housekeeping

Chapter 20
Object Views
 

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 Evolution20.8 Postscript: Using the BFILE Datatype

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



Oracle PL/SQL Programming
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
ISBN: 0596514468
EAN: 2147483647
Year: 2004
Pages: 234
Authors: Steven Feuerstein, Bill Pribyl
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net