title: SELECT DISTINCT CLOB_COLUMN FROM TABLE;
body: <p>I would like to find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB) contained in the table called COPIA.</p>
  
  <p>I have selected a PROCEDURAL WAY to solve this problem, but I would prefer to give a simple SELECT as the following: SELECT DISTINCT CLOB_COLUMN FROM TABLE avoiding the error "ORA-00932: inconsistent datatypes: expected - got CLOB"</p>
  
  <p>How can I achieve this?</p>
  
  <p>Thank you in advance for your kind cooperation.  This is the procedural way I've thought:</p>
  
  <pre><code>-- Find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB)
  -- contained in the table called COPIA
  -- Before the execution of the following PL/SQL script, the CLOB values (including duplicates) 
  -- are contained in the source table, called S1
  -- At the end of the excecution of the PL/SQL script, the distinct values of the column called CLOB_COLUMN
  -- can be find in the target table called S2
  
  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE S1 DROP STORAGE';
  
     EXECUTE IMMEDIATE 'DROP TABLE S1 CASCADE CONSTRAINTS PURGE';
  EXCEPTION
     WHEN OTHERS
     THEN
        BEGIN
           NULL;
        END;
  END;
  
  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE S2 DROP STORAGE';
  
     EXECUTE IMMEDIATE 'DROP TABLE S2 CASCADE CONSTRAINTS PURGE';
  EXCEPTION
     WHEN OTHERS
     THEN
        BEGIN
           NULL;
        END;
  END;
  
  CREATE GLOBAL TEMPORARY TABLE S1
  ON COMMIT PRESERVE ROWS
  AS
     SELECT CLOB_COLUMN FROM COPIA;
  
  CREATE GLOBAL TEMPORARY TABLE S2
  ON COMMIT PRESERVE ROWS
  AS
     SELECT *
       FROM S1
      WHERE 3 = 9;
  
  BEGIN
     DECLARE
        CONTEGGIO   NUMBER;
  
        CURSOR C1
        IS
           SELECT CLOB_COLUMN FROM S1;
  
        C1_REC      C1%ROWTYPE;
     BEGIN
        FOR C1_REC IN C1
        LOOP
           -- How many records, in S2 table, are equal to c1_rec.clob_column?
           SELECT COUNT (*)
             INTO CONTEGGIO
             FROM S2 BETA
            WHERE DBMS_LOB.
                   COMPARE (BETA.CLOB_COLUMN,
                            C1_REC.CLOB_COLUMN) = 0;
  
           -- If it does not exist, in S2, a record equal to c1_rec.clob_column, 
           -- insert c1_rec.clob_column in the table called S2
           IF CONTEGGIO = 0
           THEN
              BEGIN
                 INSERT INTO S2
                      VALUES (C1_REC.CLOB_COLUMN);
  
                 COMMIT;
              END;
           END IF;
        END LOOP;
     END;
  END;
  </code></pre>