Just a note, we're using 11g, and I have no choice. I'm looking through all_constraints and trying to check the search_condition column, something like this:
select * from all_constraints
where table_name = UPPER('STVTERM') AND
constraint_type = 'C' AND
CAST(search_condition AS VARCHAR2(100)) NOT LIKE '%IS NOT NULL';
I was hoping to chuck this into a quick and dirty proc that spits out a Grails domain. And the constraints are the only missing piece. Is there an easy way to exclude those constraints which are just "not null" other than a where/like that I'm missing? I've tried the obvious, Oracle also balks at casting the long to varchar and then checking. Since I'm likely to want to do other manipulations of this column, some of the solutions where I create a function that does a kludgy PL-SQL conversion, checks that, and returns a "match/not-match" result aren't much help either.
Anyone have any ideas?
There is a function to convert LONG to varchar2:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1025399