0

I am trying to find a way to use DISTINCT upon an inner SELECT which reads a CLOB. Before applying the DISTINCT I am converting the CLOB to to_char, apparently there are some entries in the DB that go well over the 10.000 char count, thus I am using substr to limit them to the maximum of 4000 chars. Point of using DISTINCT is that there are many duplicates in the column I am searching (I cannot remove the duplicates beforehand) and I need to grab maximum of 1500 results, and in order that to work I need first to remove duplicates and then grab the first 1500 results. Following is the query I am using. In this case I am searching for any results.

SELECT
    sub_synonym
FROM
    ( ( (
        SELECT DISTINCT
            to_char(substr(sub_synonym, 0, 4000)) AS sub_synonym
        FROM
            ingr,
            sub_ca
        WHERE
            ( ( ( ( ingr.id_d IN (
                SELECT
                    sub_ca.id_d
                FROM
                    sub_ca
                WHERE
                    sub_ca.sub_synonym LIKE '%'
            ) ) ) )
              AND ingr.id_d = sub_ca.id_d
              AND ingr.log_d = 0 )
    ) ) )
WHERE
    ROWNUM <= 1500;

I am getting this error message when trying this: ORA-64203: Destination buffer too small to hold CLOB data after character set conversion.

Is there a way to overcome this? Or else how else could I remove duplicates from the reponse before I grab the first 1500 results? (I already tried with group_by but it cant work with this column apparently).

Sir. Hedgehog
  • 1,260
  • 3
  • 17
  • 40
  • I would suggest to create a hash value with `DBMS_CRYPTO.HASH` and compare the hash values. rather than comparing strings. – Wernfried Domscheit Mar 19 '20 at 10:52
  • Does this answer your question? [SELECT DISTINCT CLOB\_COLUMN FROM TABLE;](https://stackoverflow.com/questions/3921982/select-distinct-clob-column-from-table) – hotfix Mar 19 '20 at 10:53
  • check the answer of [Chris Saxon](https://stackoverflow.com/a/52482343/1715255) – hotfix Mar 19 '20 at 10:54
  • cant make any changes on the db at this point of time. @hotfix i cannot see how that helps me. all other replies in that Q are not helpfull in this case either. :/ – Sir. Hedgehog Mar 19 '20 at 11:26

0 Answers0