New *_VC columns in Oracle database 12c dictionary views.

There are new columns named *_VC in some of the dictionary views in Oracle Database 12c . Those are VARCHAR versions of those columns which were previously defined as LONG.

This is useful in some situations. For example if you want to query the dictionary to list out the names of the materialized views defined on a specific base table.

You cannot query based on the user_mviews.query column which is long data type (long meaning unstructured binary data); it is not possible to use regular expressions and string compares to write a query like

select * from user_mviews v where CAST(v.QUERY AS CHAR) like '%EMP%';

You'd hit an error like this one.

SQL> select * from user_mviews v where CAST(v.QUERY AS CHAR) like '%EMP%';

select * from user_mviews v where CAST(v.QUERY AS CHAR) like '%EMP%'

ORA-00932: inconsistent datatypes: expected CHAR got LONG

As a workaround, it I possible to query the user_dependencies view, where there is a link between the mview and the depended-on table.

But in Oracle Database 12c, there are some dictionary views, like  *_views, *_constraints which have an additional varchar column storing the same content stored as the pre-existing long. The long columns are the ones used to store query or query fragments.

For example, consider the simple employee table below, which, by the way, has a constraint that start_date should be less than end_date:

SQL> select * from emp where empno=1;

     EMPNO NAME                 START_DATE  END_DATE

---------- -------------------- ----------- -----------

         1 emp1                 01-Jan-14   31-Jan-14

Well, I queried the new 12c column search_condition_vc on user_constraints, which is next to the pre-existing search_condition long column, and I got this output

SQL> ed

Wrote file afiedt.buf

  1  select table_name, search_condition, search_condition_vc from user_constraints

  2* where table_name='EMP'

SQL> /

TABLE_NAME      SEARCH_CONDITION               SEARCH_CONDITION_VC

--------------- ------------------------------ ------------------------------

EMP             start_date < end_date          start_date < end_date

Note the 2nd and 3rd columns:

search_condition    : -- older long column (START_DATE < END_DATE)

search_condition_vc : -- new varchar2 equivalent of long (START_DATE < END_DATE)

Some caveats still apply though, on what kinds of SQL can be captured in the new VC columns.