CLOB Filter - Number+text

Asked

Viewed 195 times

-1

I have a system whose all fields of a document are Clobs, including combo box with measly "Yes" and "No". I am building a query, I need to filter a Clob field and the values are '0||Yes' and '1||No'.

Here’s what I’ve done:

Having campoclob like '0||Yes' Error:ORA-22835: Buffer very small for conversion of Clob to Char or BLOB to RAW (real:4300, max:4,000)

CASE campoclob like '0||Yes' THEN 0 Else 1 END Error:ORA-22835: Buffer too small to convert Clob to Char or BLOB to RAW (real:4300, max:4,000)

Anyway, everything I change gives the same error, are 325 results that without filter shows the field with the correct values, even using the case, but I can’t filter that gives the error. I analyzed all documents and there is no problem with the fields, as they are combobox has no way the user type, in the other months the query runs smoothly.

oracle type-conversion

  • Ana, would it be possible to spend the rest of the query or assemble a simple example in Sqlfiddle? CLOB with clusters is always a headache ...

1 answer

0

I can’t test it now, but try it this way: 

Having dbms_lob.substr( campoclob, 4000, 1 ) like '0||Sim'

  • Also had tried with this function, I played there again and gives the same error. the problem is in WHERE, when game in Where value=1 it gives clob error

  • try Trim(value) = '1'

Browser other questions tagged oracle type-conversion

You are not signed in. Login or sign up in order to post.