Chuck Hamilton (chuckh@dvol.com) wrote:
: Is there a way to select all of the constraints on a table except the
: not null ones? The problem I'm encountering is that if I try to say
: 
: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'MYTABLE'
: AND SEARCH_CONDITION NOT LIKE '%IS NOT NULL'
: 
: I get an error stating basically that SEARCH_CONDITION is a LONG
: datatype.
: 
: If I try to join USER_CONSTRAINTS, USER_CONS_COLUMNS, and
: USER_TAB_COLUMNS to see if USER_TAB_COLUMNS.NULLABLE <> 'N', then it
: excludes ALL constraints on the not null columns, not just the NOT
: NULL constraints (i.e. there could be a not null constraint *and* a
: check constraint on a column).
: --
: Chuck Hamilton
: chuckh@dvol.com
: 
Chuck,

You cannot do string comparisons on LONG columns in SQL. You have to use
PL/SQL. I wrote a script for you to do your request:

DECLARE
constraint_text long;
c_name varchar2(100);
c_type varchar2(100);
c_owner varchar2(100);
c_table_name varchar2(100);
cursor CONSTRAINTS is SELECT owner, table_name, search_condition,
                             constraint_name, constraint_type
                      FROM dba_constraints;
BEGIN
OPEN CONSTRAINTS;
LOOP
   FETCH CONSTRAINTS
         INTO c_owner, c_table_name, constraint_text, c_name, c_type;
   exit when CONSTRAINTS%NOTFOUND;
   IF instr(upper(constraint_text),'IS NOT NULL') = 0 or
      constraint_text is null THEN
           dbms_output.put_line ('Table Name =
'||c_owner||'.'||c_table_name);
           dbms_output.put_line ('Constraint Name = '||c_name);
           dbms_output.put_line ('Constraint Name = '||c_name);
           dbms_output.put_line ('Constraint Type = '||c_type);
           dbms_output.put_line ('Search Condition = '||constraint_text);
   END IF;
END LOOP;
CLOSE CONSTRAINTS;
END;
/

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 150+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page