sql - Query to find Constraint by SEARCH_CONDITION

Question

I want to find a CONSTRAINT_NAME in Oracle by SEARCH_CONDITION.

SELECT * FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = 'myTableName';
AND SEARCH_CONDITION = '"myColumn" IS NOT NULL';

ORA-00997: illegal use of LONG datatype.

How to query by SEARCH_CONDITION?

Answer 1

SEARCH_CONDITION is LONG so you can't use it for .... very much useful.

For this particular use, I suggest PLSQL routine to write the LONG col into a VARCHAR2(32767) and then apply the check on teh VARCHAR2 variable.

LONGs are an absolute pain.

Also, in your case you can restrict the dataset further by querying ALL_CONS_COLUMNS WHERE column_name = 'colname' and joining the ALL_CONSTRAINTS to get the SERACH_CONDITION.