CODEWITHSUNDEEP
sqloracle-databaseclobset-operations
Faiqa Saeed
Faiqa Saeed

Reputation: 13

MINUS operator not working with CLOB type column

I'm working in Oracle database and getting

ORA-00932: inconsistent datatypes: expected - got CLOB

error while using MINUS operator with a column of CLOB datatype.

Following is the query in which I'm getting the error:

SELECT  COMPONENT_ITEM, COMPONENT_REF_DES, QUANTITY
FROM M_ITEM2_CTE

MINUS

SELECT  COMPONENT_ITEM, COMPONENT_REF_DES, QUANTITY
FROM M_ITEM1_CTE

COMPONENT_REF_DES is the CLOB type column

I've tried NOT IN condition as well but same error, is there any alternative for MINUS operator while comparing CLOB datatypes?

Upvotes: 0

Views: 232

Answers (1)

Paul W
Paul W

Reputation: 11363

Rather than using set operators like MINUS (which require sorts, and you cannot sort a LOB), do an outer join and compare the LOBs with the dbms_lob.compare function:

SELECT i2.COMPONENT_ITEM, i2.COMPONENT_REF_DES, i2.QUANTITY
  FROM M_ITEM2_CTE i2,
       M_ITEM1_CTE i1
 WHERE i2.component_item = i1.component_item(+)
   AND NOT (i1.quantity = i2.quantity AND
            ((i1.component_ref_des IS NULL AND i2.component_ref_des IS NULL) 
             OR NVL(dbms_lob.compare(i1.component_ref_des,i2.component_ref_des),1) = 0
            )
           )

Upvotes: 1

Related Questions