Oracle Database Discussions

Suggestion is DBMS_LOB

You can't use CLOB columns in this type of query.
If you really need to do the set operation, then consider using a select doesn't reference the CLOB columns.

if these columns doesnt have more than 4000 char, probably you can use like that (i am not sure):

  select To_Char(ename) from emp1
  MINUS
  select To_Char(ename) from emp2

if fields exceeds 4000 char you must use dbms_lob package.

Hi ,

thanks for responding , i used dbms_lob.substr function its working fine

Not sure why dbms_lob.substr and not dbms_lob.compare. Anyway, SQL does not support LOB ordering/comparing. You could use PL/SQL. If you want to do in in SQL, you could:

SQL> create table emp1 as select to_clob(ename) ename,deptno from emp
  2  /

Table created.

SQL> create table emp2 as select to_clob(ename) ename,deptno from emp where deptno = 20
  2  /

Table created.

SQL> select * from emp1
  2  minus
  3  select * from emp2
  4  /
select * from emp1
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


with t1 as (
            select  emp1.*,
                    rownum rn
              from  emp1
           ),
     t2 as (
            select  rn
              from  t1,
                    emp2
              where dbms_lob.compare(t1.ename,emp2.ename) = 0
           ),
     t3 as (
            select  *
              from  t1
              where rn not in (select rn from t2)
           ),
     t4 as (
            select  ename,
                    deptno,
                    rownum rn
              from  t3
           ),
     t5 as (
            select  t41.rn
              from  t4 t41,
                    t4 t42
              where dbms_lob.compare(t41.ename,t42.ename) = 0
                and t41.rn != t42.rn
           )
select  ename,
        deptno
  from  t4
  where rn not in (select rn from t5)
/

ENAME                                                                                DEPTNO
-------------------------------------------------------------------------------- ----------
ALLEN                                                                                    30
WARD                                                                                     30
MARTIN                                                                                   30
BLAKE                                                                                    30
CLARK                                                                                    10
KING                                                                                     10
TURNER                                                                                   30
JAMES                                                                                    30
MILLER                                                                                   10

9 rows selected.

SY.

Hi,

another option to check clobs is to use a HASH function. Take the hash of both clobs. If the hash_value is equal then the clobs are equal, otherwise not. Hash function are provided: DBMS_UTILITY.GET_HASH_VALUE, ORA_HASH (as from 10g) or dbms_crypto.hash.

Herald ten Dam
http://htendam.wordpress.com