Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00932: inconsistent datatypes: expected - got CLOB
ORA-00932: inconsistent datatypes: expected - got CLOB [message #178629] Wed, 21 June 2006 23:38 Go to next message
lawyer0413
Messages: 3
Registered: June 2006
Junior Member
There we have two tables: my_comment and sys_comment.
And field comment_desc in table my_comment is CLOB, and in table sys_comment is VARCHAR.

And I can run following two SQL successsfully:
SELECT comment_desc FROM my_comment;
SELECT TO_CLOB(comment_desc) FROM sys_comment;

But when I do UNION of them, execute following SQL:

SELECT comment_desc FROM my_comment
UNION
SELECT TO_CLOB(comment_desc) FROM sys_comment;


Error occurs:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Can anyone tell me why and how to resolve it?

Many thanks.


Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178657 is a reply to message #178629] Thu, 22 June 2006 02:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's not a very helpful message. The problem is not that they are different data types, it is that UNION involves a SORT (to remove duplicates), and you cannot ORDER BY a CLOB.

You can see the same error if you:
SELECT comment_desc FROM my_comment ORDER BY 1


If you know there will be no duplicates, use UNION ALL instead. Otherwise, look into the DBMS_LOB package (I haven't, but it might help).

Ross Leishman
Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178661 is a reply to message #178657] Thu, 22 June 2006 02:29 Go to previous messageGo to next message
lawyer0413
Messages: 3
Registered: June 2006
Junior Member
Rleishman,

Thanks for your help, I can use UNION ALL to run the SQl successfully.

And I can also use dbms_lob.substr() to convert CLOB to VARCHAR, but would you tell me what's difference between these two methods? Advantage and Disadvantage of them?

Many Thanks.
Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178815 is a reply to message #178661] Thu, 22 June 2006 22:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you wanted just a distinct list of results (ie. if a value exists in both tables it is reported only once), then UNION ALL will not help, because it simply appends the results of the second query to the first.

If you are happy to have duplicates (or you know that no duplicates exist), then UNION ALL is easily the best and fastest method because it involves no sorting and no joins.

To eliminate duplicates, you could try something like:
SELECT clob_col
FROM tab2
UNION ALL
(
  SELECT vc_col
  FROM   tab1
  MINUS
  SELECT dbms_lob.substr(clob_col ...) 
  FROM   tab2
)


You could also do something similar using a NOT IN sub-query that might perform a little better.

Ross Leishman
Unique CLOB data [message #192378 is a reply to message #178629] Tue, 12 September 2006 01:42 Go to previous messageGo to next message
thatsprem
Messages: 1
Registered: September 2006
Junior Member
It may or may not help you out....

My problem was to remove the duplicates that contains in a CLOB data column.

I followed below approach.

select unique(to_char(clobdata)) from clobdatatable


Thank,
Prem
Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #598966 is a reply to message #178629] Mon, 21 October 2013 02:33 Go to previous messageGo to next message
jayasimha
Messages: 1
Registered: October 2013
Junior Member
Hi Experts ,

Am facing same issue while executing below query


select logs.countrycode,logs.endsystem,logs.merchantid from (select countrycode,endsystem,SUBSTR(part2, 1, INSTR(part2, ';') - 1)MerchantId from (select countrycode,endsystem,SUBSTR(part1, INSTR(part1,'=') + 1) part2 from (select countrycode,endsystem,SUBSTR(additionalinfo, INSTR(additionalinfo, ';') + 1) part1 from esb_logs_1b where logtype= 'AUDIT_6'and medlaststate='SUCCESS') dual)dual )logs order by logs.merchantid

Here in above query additionalinfo is the clob datatype

Can any one please advise to resolve the above issue.

[Updated on: Mon, 21 October 2013 02:40]

Report message to a moderator

icon13.gif  Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #598967 is a reply to message #598966] Mon, 21 October 2013 02:41 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have the same error but have you the same issue? Nothing in your post allows us to diagnose.

Post a description of your table.
Use SQL*Plus and copy and paste a session with the error.
Please format your query. If you don't know how to do it, learn it using SQL Formatter.

Previous Topic: ORA-00904: "EID": invalid identifier
Next Topic: Mutating error
Goto Forum:
  


Current Time: Thu Dec 08 14:24:26 CST 2016

Total time taken to generate the page: 0.17095 seconds