3

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?

Aldwoni
  • 1,168
  • 10
  • 24
John O
  • 4,863
  • 8
  • 45
  • 78

2 Answers2

2

This is what I used when trying to solve the same problem, for posterity's sake:

-- Create the decoder function

create function funk_decode( p_cons_name in varchar2 ) return varchar2
    authid current_user
    is
        l_search_condition user_constraints.search_condition%type;
    begin
        select search_condition into l_search_condition
          from user_constraints
         where constraint_name = p_cons_name;

       return l_search_condition;
   end;
   /

-- Then use it in your select

SELECT constraint_name, constraint_type, status, search_condition FROM USER_CONSTRAINTS where funk_decode(constraint_name) like '%SEARCH_TERM%';

--- Then clean up
drop function funk_decode;

Of course, replace SEARCH_TERM with whatever you're looking for. It's based on some code I found here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582

Sophistifunk
  • 4,742
  • 4
  • 28
  • 37
1

There is a function to convert LONG to varchar2:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1025399

Iľja
  • 503
  • 4
  • 14
  • I'd like to thank you for this answer. Took a little while to figure out how to make use of it, but I wrapped this in a function and now I'm just letting that return a varchar which I can manipulate at will. – John O Jun 29 '12 at 19:52