|
Let's look at a simple example. I have two tables with
a CLOB column:
ORA9I SQL> select * from test1;
ID VAL
---------- --------------------
1 one clob
2 two clob
ORA9I SQL> desc test2
Name Null? Type
-------------------- ------ -----------
ID NUMBER
VAL CLOB
ORA9I SQL> select * from test2;
ID VAL
---------- --------------------
3 three clob
4 four clob
When I try to use the UNION operator on these two
tables, I get an error.
ORA9I SQL> select val from test1
2 union
3 select val from test2;
select val from test1
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
So you can't just natively use the UNION operator on
the CLOB columns. But you can convert to a character
datatype and use the UNION clause.
ORA9I SQL> select to_char(val) from test1
2 union
3 select to_char(val) from test2;
TO_CHAR(VAL)
------------------------------
four clob
one clob
three clob
two clob
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
|
