ORA-00932: inconsistent datatypes: expected - got CLOB

Today when I was trying to execute following query it errors out "ORA-00932: inconsistent datatypes: expected - got CLOB". Though it works fine for "distinct cust.*", "distinct hp.*", "distinct hps.*" and "distinct hpsu.*","distinct cust.*" but when I try to SELECT distinct rows from raa ("distinct raa.*") it errors out.
I have googled this problem but no cure because everyone suggested to check database and change the data types. Though in other cases it could be only achieved by making changes in database but in my case I just remove "DISTINCT" from query and it works fine. :)

SELECT  DISTINCT raa.*
    FROM hz_cust_accounts cust,
                 hz_parties hp,
                 hz_party_sites hps,
                 hz_party_site_uses hpsu,
                 ra_addresses_all raa
 WHERE cust.party_id = hp.party_id
       AND hp.party_id = hps.party_id
       AND hps.PARTY_SITE_ID = hpsu.PARTY_SITE_ID
       AND hps.PARTY_SITE_ID = raa.PARTY_SITE_ID
       AND raa.org_id =:p_org_id
       AND hpsu.SITE_USE_TYPE = 'BILL_TO'



ORA-00932: inconsistent datatypes: expected - got CLOB

In addition when I try to drill the error what I found that we cannot ORDER BY or Distinct a CLOB. I first Describe the table and checked which field is having CLOB data type.




Now I know that "ADDRESS_TEXT" is the field. I try to run the following queries



select distinct raa.ADDRESS_TEXT
  from ra_addresses_all raa
select raa.ADDRESS_TEXT
  from ra_addresses_all raa
order by raa.ADDRESS_TEXT



each query returned the same error.


Now I try the following query and it works fine.



select distinct dbms_lob.substr(raa.ADDRESS_TEXT)
  from ra_addresses_all raa
order by dbms_lob.substr(raa.ADDRESS_TEXT)




You can too use this function to avoid the error dbms_lob.substr()


*********************************************************************************************************

 

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...