My Experiments with Oracle

By Sriram Sanka -The AUAA

Archive for December, 2009

Not null constraint :–ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Posted by sriram on December 9, 2009


SQL> sho user
USER is "SCOTT"
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

SQL> 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.

SQL> select constraint_name
2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%';

CONSTRAINT_NAME
------------------------------
SYS_C006082
SYS_C006081
SYS_C006080
SYS_C005558
SYS_C005696
SYS_C005695

6 rows selected.

SQL>

Source:- Asktom`s get_search_condition function.

Advertisements

Posted in Uncategorized | 1 Comment »

 
%d bloggers like this: