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

Using UNION without truncating CLOB data

You answered the following question way back in 2002 ("Is it possible to do a UNION of two Oracle 8i views where there are CLOB fields involved?"). When I tried the solution I was able to get the UNION to work, but the CLOB is truncated to 4,000 characters only. Is there any other way where data is not truncated and UNION still works?

You answered the following question way back in 2002 ("Is it possible to do a UNION of two Oracle 8i views where there are CLOB fields involved?"). When I tried the solution I was able to get the UNION to work, but the CLOB is truncated to 4,000 characters only. I'm using Oracle 9.2. Is there any other way where data is not truncated and UNION still works? I'm trying to do a UNION between two views. One has a VARCHAR2 field and the other has a CLOB. Thanks.
My solution used the TO_CHAR function to allow the UNION since you cannot perform a UNION on CLOB columns directly. The TO_CHAR function converted the CLOB to a different datatype, which is limited to 4,000 characters. In the Oracle docs, I see "The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table." So I had to perform a conversion to be able to perform the UNION operation. If this is not acceptable, then you will probably have to code your own routine with PL/SQL to perform this for you as the operation is not supported by Oracle.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close