Remove dupes with CLOB columns involved
Date: Mon, 22 Jun 2009 17:25:45 +0200
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,
- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
- Mi sitio sobre programación web: http://borrame.com
- Mi web de humor satinado: http://www.demogracia.com --