Re: Remove dupes with CLOB columns involved

From: Álvaro G. Vicario <>
Date: Tue, 23 Jun 2009 09:45:18 +0200
Message-ID: <h1q19n$ch2$>

John Hurley escribió:
> On Jun 22, 11:25 am, "Álvaro G. Vicario"
> <> wrote:

>> I've created a GLOBAL TEMPORARY TABLE where one of the columns stores
>> the result of concatenating a variable (and unknown) number of
>> VARCHAR2(100) cells. I didn't want to hit the 4,000 character limit when
>> the source table has more than 40 rows so I created such temp column as
>> CLOB. But when I joined my temporary table with some other regular
>> tables I got an "ORA-00932: inconsistent datatypes: expected - got CLOB"
>> error message. The problem seems to be that it's a "SELECT DISTINCT"
>> query and I've read in docs that it's a known limit. Replacing DISTINCT
>> with GROUP BY doesn't change this.
>> Looking at the documentation, I appeared to me that TO_CHAR() could be a
>> solution but it isn't: it fixes ORA-00932 but I get "ORA-22835: Buffer
>> too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4001,
>> maximum: 4000)" when the CLOB contains more than 4000 bytes. Some forums
>> suggest DBMS_LOB.SUBSTR() but it also fails for 4000+ (ORA-06502:
>> PL/SQL: error : buffer de cadenas de caracteres demasiado pequeño
>> numérico o de valor ORA-06512: en línea 1).
>> I just need to run a query where some of the columns are CLOB and get
>> rid of duplicated rows. Is there an easy approach I could use?
>> It's a PHP/5.2+OCI8 app running on top of Oracle 10g Express Edition in
>> a Windows XP box. Thank you in advance,
> Could you give an overview of what exactly you are trying to
> accomplish?
> Perhaps a redesign of the database tables involved might be a better
> idea.  While there's nothing exactly wrong with BLOBs or CLOBs ...
> they can get unwieldy and quite complicated unless planned and tested
> from the beginnings of design phases.

It's part of a module that prints custom reports (defined by users). Among (manu) other things, each reportable object has a list of attributes. A simplified example:



I'm emulating MySQL's GROUP_CONCAT() function so I get one row per object with all the attributes in one cell:

object_name, attribute_list
'object_1', 'attr_1_1; attr_1_2; attr_1_2' 'object_2', 'attr_2_1; attr_2_2'

It happened that the code I'm using [1][2] implies some complex operations (CONNECT BY and the like) and was crashing Oracle Express Edition if used as subquery in the main query. So I decided to run these queries first and store the result in a temporary table. Since the attribute table can have lots of rows I created attribute_list as CLOB to prevent truncation errors.

So far I'm considering two possibilities:

  • Get over with it and just truncate large data (after all, 4,000 bytes of attributes might not be a real life possibility).
  • Fetch all rows and remove dupes in the application side (the PHP code might not be optimal but it's easy to write).

[1] [2]

-- - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web:
-- Mi web de humor satinado:
Received on Tue Jun 23 2009 - 02:45:18 CDT

Original text of this message