ORA-00932: inconsistent datatypes: expected - got CLOB
One thing I must share is, I have explained what I was facing and that is explained below. If you are facing same problem then it is pleasure for me, my post is helpful for you. But if this is not the case then please visit following links may be you can find some thing for you.
http://www.orafaq.com/forum/t/65196/2/
http://albert-myptc.blogspot.co.uk/2009/04/ora-00932-inconsistent-datatypes.html
https://github.com/rsim/oracle-enhanced/issues/72
http://tracker.moodle.org/browse/MDL-21890
Can't export my database using exp with XMLTYPE member column
https://forums.oracle.com/forums/thread.jspa?threadID=572154
LEARNING TO LOVE LOBS
And please let me know what kind of problem you are facing by leaving comment so that I can blog about that too.
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
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 |
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.
 |
| ora 00932 inconsistent datatypes expected got clob |
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
 |
| ora 00932 inconsistent datatypes expected got clob |
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()
Your feedback/comment is quite important for improving this blog so feel free to leave your comments & suggestions
Thank you! This helped me with trying to figure out how I was getting this error and what to do to get around it. Well, now that I have this, I'm another step along the journey!
ReplyDeleteThanks for this!
You are always welcome.
DeleteLooking forward for your suggestions.
I have the same problem but with OWB 11g while trying to import data from a SQL Server to an Oracle database. The mapping which makes migrating data possible returns this error fact that makes it impossible to fill the oracle table with data. Do you have any idea about what I should do?
ReplyDeleteHave you tried this dbms_lob.substr()?
DeleteI got into this scenario when concatenating multiple records in one col to a single cell value. I got struck and searching for solution. I applied your solution and it worked!!!.
ReplyDeleteThank you very much!!!
Manickalal
Welcome :)
DeleteBut the dbms_log.substr(<>,[startindex],[numberofchar]) doesn't work in this case. Have anyone tried this ?
ReplyDeleteThanks Ritesh
Dear Ritesh,
DeleteI have tried following query and it worked fine,
select dbms_lob.substr(ADDRESS_TEXT) a,
dbms_lob.substr(ADDRESS_TEXT,1,10) b
from HZ_CUST_ACCT_SITES_ALL
Regards
I got this issue while using comment field in union, used dbms_lob.substr and this helped. Thank you..Amit
ReplyDeleteYou are Welcome Dear.
Deleteselect distinct o.id "Organization ID",
ReplyDeleteen.entity_id "Contact ID",
en.entity_cd "Note Entity Code",
ed.entity_name "Note Entity",
en.entity_id "Note Entity_id",
en.note_dt "Note Date",
en.create_dt "Create Date",
en.update_dt "Update Date",
FN_LOCALIZE_BI(nt.desc_id, 'EN', 'default') "Type",
en.subject "Subject",
replace(replace(dbms_lob.substr(en.description, 4000, 1),
chr(10),
'\n'),
chr(13),
'\n') "Content"
from entity_notes en
left join note_type nt
on nt.type_cd = en.note_type
left join entity_definition ed
on en.entity_cd = ed.entity_cd
inner join contact_owner co
on co.contact_id = en.entity_id
inner join organization o
on o.id = co.organization_id
where (en.sync_src_cd = 'MANUAL' or en.sync_src_cd is null)
this is the original query
with this query client is getting only 4000 characters for the description column.
They need almost to 32000 characters for the description column with the same query and logic
Any way this can be achieved?
Thank you so much....it helped !!! :)
ReplyDeleteThanks you very much
ReplyDeletei use TO_CHAR instead of SUBSTR
ReplyDeletefaced same issue after upgrade to 11g from 10g.
ReplyDeleteError Message: ORA-00932: inconsistent datatypes: expected - got CLOB
modified column size from varcha2(255) to varchar2(300) and it worked.
Super It dbms_lob.substr worked for me
ReplyDelete