2 Replies Latest reply: Apr 28, 2011 12:22 AM by Nagaraja Akkivalli RSS

    Using Clob in Where Clause- Issue.

    Nagaraja Akkivalli
      In a given session, user does a list of transactions. I am storing these transaction codes in a CLOB variable and are “-“ separated(say pattern). To chck wether patten exists are not in table, I am querying as below.

      Queries :
      SELECT non_clob_column FROM table_with_clob_column WHERE clob_column = CLOB variable.
      This gives datatype mismatch error.

      However,

      SELECT non_clob_column FROM table_with_clob_column WHERE to_char(clob_column) = to_char(CLOB variable)

      The above query works fine if columns contains less than 4000 characters. If column contains more than 4000 characters than I am getting an error as ,

      Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 7367, maximum: 4000).

      PLease let me know if you thew solution for the same.
      Thanks and Regards
      Nagaraja A.