Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to find automatically generated "not null" checks in user_constraints?

Re: How to find automatically generated "not null" checks in user_constraints?

From: <nomorekiss_at_yahoo.com>
Date: 28 Jan 2005 06:03:01 -0800
Message-ID: <1106920981.012740.8770@f14g2000cwb.googlegroups.com>


from http://asktom.oracle.com :

GOTO a page to Bookmark Review | Bottom | Top How to deal with long for this query June 24, 2003 Reviewer: Yong from CA, USA

Tom,

Search_condition is long type in Oracle data dictionary. How can I construct a
query to get the following results without set a condition on a long type

SQL> select constraint_name from user_constraints 2 where search_condition not like '%NOT NULL'; where search_condition not like '%NOT NULL' *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Followup:

ops$tkyte_at_ORA920LAP> create or replace function get_search_condition( p_cons_name in varchar2 ) return varchar2 2 authid current_user
3 is
4 l_search_condition user_constraints.search_condition%type; 5 begin

6      select search_condition into l_search_condition
7        from user_constraints
8       where constraint_name = p_cons_name;
9
10      return l_search_condition;

11 end;
12 /

Function created.

ops$tkyte_at_ORA920LAP>
ops$tkyte_at_ORA920LAP> select constraint_name 2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%'; Received on Fri Jan 28 2005 - 08:03:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US