Edit Question - KnowledgeHub

Title Body

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. 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" How can I achieve this? Thank you in advance for your kind cooperation. This is the procedural way I've thought: -- 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_RE

Tags (comma-separated) Cancel