Skip to Main Content
  • Questions
  • Union of CLOB datatype -> ORA-00932: inconsistent datatypes

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Rajesh.

Asked: April 13, 2001 - 12:35 pm UTC

Last updated: January 14, 2013 - 1:02 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

I have table T1 ( id number, content CLOB ) and table T2 ( id number, content CLOB ) where T2 is snapshot of remote table. Both of them have identical structure. I want to select all the records from T1 and T2, thus I am trying,
"select id, content from T1 UNION select id , content from T2 ;"

This returns ORA-00932: inconsistent datatypes

Is it not allowed to have union of clob ?
How do I get all the records using a single Query ?

Thanks,
Rajesh


and Tom said...

Use a union ALL

ops$tkyte@ORA8I.WORLD> create table t1 ( x int, y clob );

Table created.

ops$tkyte@ORA8I.WORLD> create table t2 ( x int, y clob );

Table created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> select * from t1
2 union
3 select * from t2;
select * from t1
*
ERROR at line 1:
ORA-00932: inconsistent datatypes


ops$tkyte@ORA8I.WORLD> select * from t1
2 union all
3 select * from t2;

no rows selected


A union of "a" and "b" is really:

SORT DISTINCT ( "a" + "b" )

A union ALL of "a" and "b" is

"a" + "b"

the sort distinct cannot be done on the clob type.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Union of CLOB datatype -> ORA-00932: inconsistent datatypes

Rajesh Jadhav, April 13, 2001 - 4:47 pm UTC

Thanks,
Rajesh

a great help

Alex, December 16, 2004 - 6:08 am UTC

I had this exact problem, found this question immediately (full marks to the original poster for a clear title!) and can now get on with other work!

This site should be called "thanks tom"!



UNION of CLOB limitation

Martin, November 13, 2006 - 1:08 pm UTC

Hi Tom,

If I really want to do a UNION and not a UNION ALL.
I try to use a TO_CHAR before the CLOB.
This fix the issue but cause another one when the CLOB go over 4000 CHARs.

The use case is mask some fields value (make NULL) depending on some access control.

This will probably redesign in few months to remove the UNION. But if you have an idea of a quick workaroud...

Thanks
Martin


Tom Kyte
November 14, 2006 - 4:08 am UTC

there isn't one, UNION implies "distinct", there is no way to "distinct" a clob.

very helpful

Resly Mathews, November 16, 2006 - 10:47 am UTC

Thanx ...this was helpful n i din have t go around searchin, the ttle had it all

Manuel Vidigal, January 09, 2013 - 12:53 pm UTC

I'm using the UNION ALL trick with CLOBs in order to preform a FULL OUTER JOIN, but I'm getting a strange behaviour.

Small test case:

create table test_table (id number, c_lob clob);
insert into test_table VALUES (1,'AAAAA');
COMMIT;

The following query returns a NULL clob:

WITH t_old AS
(SELECT id,
c_lob
FROM test_table
WHERE 1=2),
t_new AS
(SELECT id,
c_lob
FROM test_table
WHERE id = 1)
SELECT t_old.id,
nvl(t_old.c_lob, t_new.c_lob)
FROM t_old,
t_new
WHERE t_old.id = t_new.id(+)
UNION ALL
SELECT t_new.id,
t_new.c_lob
FROM t_old,
t_new
WHERE t_old.id(+) = t_new.id
AND t_old.id IS NULL;

but if I only use the second query of the union all the clob returns AAAAA.

Is this a bug, or am I missing something?

Tested on 11gR2
Tom Kyte
January 14, 2013 - 1:02 pm UTC

ops$tkyte%ORA11GR2> WITH t_old AS
  2   (SELECT id,
  3            c_lob
  4      FROM test_table
  5     WHERE 1=2),
  6  t_new AS
  7   (SELECT id,
  8            c_lob
  9      FROM test_table
 10     WHERE id = 1)
 11  SELECT t_old.id,
 12         nvl(t_old.c_lob, t_new.c_lob)
 13    FROM t_old,
 14         t_new
 15   WHERE t_old.id = t_new.id(+)
 16  UNION ALL
 17  SELECT t_new.id,
 18         t_new.c_lob
 19    FROM t_old,
 20         t_new
 21   WHERE t_old.id(+) = t_new.id
 22     AND t_old.id IS NULL;

        ID NVL(T_OLD.C_LOB,T_NEW.C_LOB)
---------- --------------------------------------------------------------------------------
         1 AAAAA




I cannot reproduce.

can you give a real example that shows what you are trying to do for real. T_new and T_old are distracting from the problem you are trying to solve here (they confuse me)


what are you really trying to do in real life?

A reader, January 15, 2013 - 7:10 pm UTC

I know the test case was not brilliant, but I wanted to create a small example of what was hapenning to a query that worked on 11gR1, but when I upgraded to 11gR2 it didn't work properly.

The bug only exists on 11.2.0.1.0, its fixed on 11.2.0.3.0.


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here