OraFAQ Forum: SQL & PL/SQL » ORA-00932: inconsistent datatypes: expected

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00932: inconsistent datatypes: expected - got CLOB

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00932: inconsistent datatypes: expected - got CLOB [message #178629]

Wed, 21 June 2006 23:38

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.

Report message to a moderator

Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178657 is a reply to message #178629]

Thu, 22 June 2006 02:19

rleishman
Messages: 3728
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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178661 is a reply to message #178657]

Thu, 22 June 2006 02:29

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.

Report message to a moderator

Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178815 is a reply to message #178661]

Thu, 22 June 2006 22:28

rleishman
Messages: 3728
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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Unique CLOB data [message #192378 is a reply to message #178629]

Tue, 12 September 2006 01:42

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

Report message to a moderator

Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #598966 is a reply to message #178629]

Mon, 21 October 2013 02:33

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

Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #598967 is a reply to message #598966]

Mon, 21 October 2013 02:41

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 03 12:28:35 CDT 2025