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.
