SQL> sho user
USER is "SCOTT"
SQL> select constraint_name from user_constraints
2 where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
SQL> create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2
2 authid current_user
3 is
4 l_search_condition user_constraints.search_condition%type;
5 begin
6 select search_condition into l_search_condition
7 from user_constraints
8 where constraint_name = p_cons_name;
9
10 return l_search_condition;
11 end;
12 /
Function created.
SQL> select constraint_name
2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%';
CONSTRAINT_NAME
------------------------------
SYS_C006082
SYS_C006081
SYS_C006080
SYS_C005558
SYS_C005696
SYS_C005695
6 rows selected.
SQL>
Source:- Asktom`s get_search_condition function.
Sriram Sanka - My Experiences with Databases & More 