Re: selecting constraints from dictionary

From: Ari Kaplan <akaplan_at_interaccess.com>
Date: 1998/01/05
Message-ID: <68rf2n$pf3$1@nntp3.interaccess.com>#1/1

Chuck Hamilton (chuckh_at_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_at_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 table_name, owner, 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:                      <->
<->                                                               <->
<->             http://homepage.interaccess.com/~akaplan          <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

: "Therefore the Lord Himself will give you a sign:
: Behold, a virgin will be with child, and bear a son,
: and she will call His name Immanuel." (Isa 7:14 NASB)
The correct quote is:

"Therefore the Lord Himself will give you a sign: Behold, a young woman will be with child, and bear a son, and she will call His name Immanuel." (Isa 7:14)

The original Hebrew is "septulah" which is young woman, not "betulah" which is virgin. Check some non-King James bibles or Hebrew bibles and you will see what I mean. Not to sidetrack the Oracle issue... Received on Mon Jan 05 1998 - 00:00:00 CST