Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to find automatically generated "not null" checks in user_constraints?
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;
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