How to find automatically generated "not null" checks in user_constraints?

Hello,

When I create a table with a not null column, Oracle implicitly creates a check constraint "<field> is not null":

> create table AAA (B number(10) not null);
> select * from user_constraints where table_name = 'AAA';

       constraint_name:  SYS_C00114059
       constraint_type:  'C'
       search_condition: '"B" IS NOT NULL'

Since the information is already stored in user_tab_columns.nullable, I want to omit these rows from user_constraints. The problem is I cannot issue such query:

> select * from user_constraints where constraint_type = 'C'
       and search_condition not like '% IS NOT NULL';

...because search_condition is of type LONG:

       ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I've tried to cast search_condition to string using TO_CHAR() and CAST(... AS VARCHAR2(2000)), but I was still getting the same error.

How to solve it? I'd prefer not to create any PL/SQL functions/procedures, since I've got read-only access to the database...

Thank you!

PS. I use Oracle9i 9.2.0.1.0. Received on Fri Jan 28 2005 - 07:26:18 CST