Unsung but so helpful new 12.1 feature ....

I just came across this really nice new feature in 12.2 (and looked back to find it was in 12.1). If you work with the data dictionary and with the *_CONSTRAINTS table, feast your eyes on this description:

SQL> desc dba_constraints
 Name                                                                                                      Null?    Type
 ------------------------------------------------
 OWNER                 VARCHAR2(128)
 CONSTRAINT_NAME       NOT NULL VARCHAR2(128)
 CONSTRAINT_TYPE       VARCHAR2(1)
 TABLE_NAME            NOT NULL VARCHAR2(128)
 SEARCH_CONDITION      LONG
 SEARCH_CONDITION_VC   VARCHAR2(4000) R_OWNER               VARCHAR2(128)
 R_CONSTRAINT_NAME     VARCHAR2(128)
 DELETE_RULE           VARCHAR2(9)
 STATUS                VARCHAR2(8)
 DEFERRABLE            VARCHAR2(14)
 DEFERRED              VARCHAR2(9)
 VALIDATED             VARCHAR2(13)
 GENERATED             VARCHAR2(14)
 BAD                   VARCHAR2(3)
 RELY                  VARCHAR2(4)
 LAST_CHANGE           DATE
 INDEX_OWNER           VARCHAR2(128)
 INDEX_NAME            VARCHAR2(128)
 INVALID               VARCHAR2(7)
 VIEW_RELATED          VARCHAR2(14)
 ORIGIN_CON_ID         NUMBER
  
Of course, I am sure you noticed the new column - SEARCH_CONDITION_VC! A Search condition we can easily search! No more of this:

select a.owner, a.table_name, a.search_condition, b.column_name, count(*)
from dba_constraints a, dba_cons_columns b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.search_condition_vc like '%''t'',''f''%'
group by a.owner, a.table_name, a.search_condition, b.column_name;
group by a.owner, a.table_name, a.search_condition, b.column_name
                                *
ERROR at line 7:
ORA-00997: illegal use of LONG datatype
Now we can do this!!

select a.owner, a.table_name, a.search_condition_vc, b.column_name, count(*)
from dba_constraints a, dba_cons_columns b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.search_condition_vc like '%''t'',''f''%'
group by a.owner, a.table_name, a.search_condition_vc, b.column_name;
OWNER      TABLE_NAME                     SEARCH_CONDITION_VC            COLUMN_NAME         COUNT(*)
---------- ------------------------------ ----------------------         ----------------    --
PROD       LCONFIG                        ride IN ('t','f')              RIDE                1
PROD       VENT                           pvent in ('t','f','p','s','c') VENT                1


Very nice....