How to select DISTINCT on a CLOB field.

Lets consider a table 'PREFERENCES' with two values 'ID' and 'PREFERENCES'. Where id is a Number and preferences is a CLOB field.

      ID   PREFERENCES
---------- ---------------------------------------------
         1 Oracle 
         2 Oracle 
         6 MSSQL
         7 MySQL
         8 Oracle

 

Now  if you want to find out how many distinct values are there in the Preference column.You might try to use;

SELECT DISTINCT PREFERENCES FROM PORTALPREFERENCES;

This will generate the following  SQL error:

ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 -  "inconsistent datatypes: expected %s got %s"> *Cause:    *Action: Error at Line: 3 Column: 17

If you then use

SELECT DISTINCT TO_CHAR(PREFERENCES) FROM PORTALPREFERENCES;

This will also generate error :-
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6608, maximum: 4000) 22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)" *Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where            the LOB size was bigger than the buffer limit for CHAR and RAW            types.            Note that widths are reported in characters if character length            semantics are in effect for the column, otherwise widths are            reported in bytes. *Action:   Do one of the following            1. Make the LOB smaller before performing the conversion,            for example, by using SUBSTR on CLOB            2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

as per the Error Description If you use

SELECT DISTINCT DBMS_LOB.SUBSTR(PREFERENCES) FROM PORTALPREFERENCES

Still, an SQL Error will be thrown , which is ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 06502. 00000 -  "PL/SQL: numeric or value error%s" *Cause:    *Action:

There are lot of ways to solve this problem.One of the simplest way is to increase the Buffer in the same sql . As displayed below.

SELECT DISTINCT   DBMS_LOB.SUBSTR(PREFERENCES,4000,1) FROM PORTALPREFERENCES;

This runs without error and gets you all the unique values of preferences column.

Happy Coding!!