views

Views as “stored text”

Connor McDonald ,

You’ll often see comments that views are not copies of the data, they are merely the stored text of a query that defines.

This is by and large true, but don’t forget, this is not  the same as saying we simply take whatever text you give us, and store it.

Let’s take a look at a simple example proving that this is not the case.


SQL> create table t as select * from all_objects;

Table created.

SQL>
SQL> create or replace
  2  view V as select * from T;

View created.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
------------------------------------------------------------
select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
ATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIME
STAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPA
CE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINE
D" from T

Notice that “*” has been expanded out to the full list of columns.

Sidebar: Notice also the nice column TEXT_VC which is the varchar2 equivalent of the older style LONG column TEXT. You’ll get that on 12c.

Interestingly, aliases will be preserved in the view definition if provided


SQL> create or replace
  2  view V as select t.* from T;

View created.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
------------------------------------------------------------
select t."OWNER",t."OBJECT_NAME",t."SUBOBJECT_NAME",t."OBJEC
T_ID",t."DATA_OBJECT_ID",t."OBJECT_TYPE",t."CREATED",t."LAST
_DDL_TIME",t."TIMESTAMP",t."STATUS",t."TEMPORARY",t."GENERAT
ED",t."SECONDARY",t."NAMESPACE",t."EDITION_NAME",t."SHARING"
,t."EDITIONABLE",t."ORACLE_MAINTAINED" from T

One important takeaway from this, is that since the “*” is not preserved in the view definition, if you add or drop columns to the base table, the view needs to be recreated to correctly reflect that change.

views in 12c

Connor McDonald ,

Observed an interesting idiosyncracy in the creation of views in 12c (this is in a pluggable database, but not confirmed whether this is related or not). This database was upgraded from 11.2 without incident…until we came to replace one of the existing views.

SQL> create or replace
  2  view EXISTING_VIEW_NAME
  3  as select * from other_schema.account;
ERROR at line 3:
ORA-01720: grant option does not exist for 'OTHER_SCHEMA.ACCOUNT'
*

Now that struck us as odd, because this was a script from our source code control repository, which had no previous entries about grants for the OTHER_SCHEMA.ACCOUNT table. So then I tried this:

SQL> create or replace
  2  view NEW_VIEW_NAME
  3  as select * from other_schema.account;

View created.

so there doesn’t appear to be the need for the grant option. Similarly, dropping the original view also solves the problem.

SQL> drop view EXISTING_VIEW_NAME;

View dropped.

SQL> create or replace
2 view EXISTING_VIEW_NAME
3 as select * from other_schema.account;

View created.

So when you get errors regarding grants in 12c, perhaps just double check to make sure you really need it.