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?

Maybe this post can give you some ideas http://stackoverflow.com/questions/15918240/oracle-comparing-default-value-of-a-column — A.B.Cade, Sep 30 '13 at 13:32
Are you going to extend or shrink the list of elements on a regular basis or it's a one time task? — Nick Krasnov, Sep 30 '13 at 13:51
@NicholasKrasnov Its a one time task for now, but the block to add the type is going to execute on regular basis, so for that reason I want to avoid execution of that block. — eatSleepCode, Sep 30 '13 at 13:54

score 3 · Accepted Answer · edited May 23 '17 at 11:58

3

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 '%&apos;g&apos;%'

Here is a sqlfiddle Demo

edited May 23 '17 at 11:58

Community

1
1

answered Sep 30 '13 at 13:58

A.B.Cade

16,735
1
37
53

'%'g'%' I am not getting how to put a search condition, can you please explain this? – eatSleepCode Sep 30 '13 at 14:12
Since I used the `DBMS_XMLGEN.GETXML` to convert the long into a varchar2 it was "xml escaped" so every `'` became `'`. You can run only the inner query and see the results – A.B.Cade Sep 30 '13 at 14:14
I am executing the same on sqldeveloper but it is asking for Enter substitution value for APOS and then it gives ORA-31011: XML parsing failed error. – eatSleepCode Sep 30 '13 at 14:15
You can always use plsql to compare the long value with a varchar2 as well, as plsql can implicitly convert bitween the two – A.B.Cade Sep 30 '13 at 14:17

Nick Krasnov · Answer 2 · 2013-09-30T15:00:19.097

As another approach, you could use a cursor - PL/SQL converts values that are of LONG data type to VARCHAR2 data type while fetching from a cursor:

set serveroutput on;
declare
  cursor c_cursor is
    select search_condition as sc
      from user_constraints
     where constraint_name = 'C_TYPE'; 

  l_list varchar2(4000);
begin
   /* 
      As long as you are querying user_constraints data dictionary view,
      specifying constraint name
      you guarantee that the only one row will be returned.

   */
  for i in c_cursor
  loop
    l_list := i.sc;
  end loop;
  dbms_output.put_line(l_list);
end;

Result:

anonymous block completed
col in ('a', 'b','c','d','e','f')

Comparing long data type with varchar data in oracle

2 Answers2