inconsistent type after querying search_condition column in all_constraints table Oracle I'm trying to get the not null constraints in my tables, so I'm using the following sql request : 

    select * from all_constraints
    where owner='userx'
    and constraint_type='C'
    and search_condition='%IS NOT NULL';  


The problem is that I'm getting the ORA-00997: illegal use of LONG datatype exception
When looking on the Oracle 9i documentation I realize that search_condition column is of LONG datatype.   

How to make use of this column ?
if you have xmldb installed (not sure if this will work for 9i) you can use something like this,

SELECT table_name, constraint_name, sc FROM   (SELECT  constraint_name, table_name, search_condition ,to_char(extractvalue(dbms_xmlgen.getxmltype ('select search_condition from user_constraints x' ||' where x.constraint_name  = ''||t.constraint_name||''''),'//text()')) sc FROM   user_constraints t) where sc like '%IS NOT NULL'