Remove dupes with CLOB columns involved

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Mon, 22 Jun 2009 17:25:45 +0200
Message-ID: <h1o7sq$i82$1_at_news.eternal-september.org>



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,

--

Received on Mon Jun 22 2009 - 10:25:45 CDT

Original text of this message