inconsistent type after querying search_condition column in all_constraints table Oracle
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'