Problem solve Get help with specific problems with your technologies, process and projects.

UNION of two Oracle 8i views with CLOBs

Is it possible to do a UNION of two Oracles 8i views where there is CLOB fields involved?

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;

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.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.