Re: Remove dupes with CLOB columns involved

From: Tim X <timx_at_nospam.dev.null>
Date: Tue, 23 Jun 2009 19:25:36 +1000
Message-ID: <87vdmns3n3.fsf_at_lion.rapttech.com.au>

"Álvaro G. Vicario" <alvaro.NOSPAMTHANX_at_demogracia.com> writes:

> 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,

Not sure I fully understand your requirements, but it sounds like either a nested table or perhaps an xml column type might provide a more workable solution??

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Tue Jun 23 2009 - 04:25:36 CDT