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?
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
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.
