Comparing long data type with varchar data in oracle

Question

Consider following scenario :

CREATE TABLE test
(
  name VARCHAR2(50),
  type LONG,
  CONSTRAINT c_type CHECK (type IN ('a',  'b', 'c', 'd', 'e', 'f'))
);

I want to alter constraint c_type and add a new type in check constraint say 'g'.

Now to alter a constraint we need to drop it and recreate it, but I want to drop the constraint only if it do not contains check for type 'g'.

I checked table user_constraints, it contains column search_condition but problem here is the data type for column "type" is long which i am not able to compare with varchar.

How to compare the Long data type?

Answer 1

I think that your problem isn't that the TYPE column is LONG but that SEARCH_CONDITION of user_constraints is a LONG.

So you can do something similar to the answers in this post, in your case it can look like this:

select count(*)
from 
(SELECT XMLTYPE(
DBMS_XMLGEN.GETXML('select SEARCH_CONDITION from user_constraints ')
).extract('//SEARCH_CONDITION/text()').getstringval() srch_cond
from dual)
where srch_cond like '%'g'%'

Here is a sqlfiddle Demo