Re: Remove dupes with CLOB columns involved

From: John Hurley <>
Date: Mon, 22 Jun 2009 13:05:25 -0700 (PDT)
Message-ID: <>

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,
> --
> -- Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web:
> -- Mi web de humor satinado:
> --

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. Received on Mon Jun 22 2009 - 15:05:25 CDT

Original text of this message