In Java, I am appending the following to a query text:

"(LOWER(CAST(pr.exampleColumn as string)) LIKE :value)"

This transforms into the following SQL query:

(LOWER(CAST(p14_0.exampleColumn AS VARCHAR2(4000 CHAR)))) LIKE '%test%'

Where example_column is a CLOB column in the database.

I am receiving the error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4215, maximum: 4000)

What solution can I apply to resolve this issue?

In Java, I am appending the following to a query text:

"(LOWER(CAST(pr.exampleColumn as string)) LIKE :value)"

This transforms into the following SQL query:

(LOWER(CAST(p14_0.exampleColumn AS VARCHAR2(4000 CHAR)))) LIKE '%test%'

Where example_column is a CLOB column in the database.

I am receiving the error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4215, maximum: 4000)

What solution can I apply to resolve this issue?

• java
• sql
• oracle-database
• hibernate
• jdbc

3 Answers 3

Error you got says that you're trying to put your foot size 45 into a shue size 38. What to do? Cut off your toes.

Or, in this actual case, try another approach. The simplest one is to do nothing.

I created sample table:

create table test (col clob);

and used dummy text generator to create text whose length is 5000 characters;

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ...

and then inserted it into the table.

Query which - as I said - does nothing is

SQL> select count(*)
  2  from test
  3  where lower(col) like '%consectetuer%';

  COUNT(*)
----------
         1

SQL>

So - no casting at all. Try it, see if it does any good in what you're doing.

[EDIT]

If you're worried about performance as you're searching through a lot of data, consider using Oracle Text.

Create appropriate index and use the CONTAINS operator (additional advantage: you don't have to worry about letter case):

SQL> create index i1_col_clob on test (col) indextype is ctxsys.context;

Index created.

SQL> select count(*)
  2  from test
  3  where contains(col, 'CONseCTetuer') > 0;

  COUNT(*)
----------
         1

SQL>

Littlefoot's answer points out that you don't have to use a CAST expression, but you can instead use LIKE, even though the documentation seems to say that it requires a CHAR, NCHAR, VARCHAR2 or NVARCHAR2 argument (but somehow it still works with CLOB). The problem is that you want a case-insensitive search, and doing LOWER(column) creates a new large object to compare using LIKE, which can be resource-intensive. To specify a case-insensitive search while avoiding the creation of a new object, you can use REGEXP_LIKE:

"REGEXP_LIKE(pr.exampleColumn, :value, 'i')"

The function returns true if the second argument (the pattern string) is contained in the given source string (i.e. you don't have to use ".*" around the :value). And, unlike LIKE, the REGEXP_LIKE function is explicitly documented to work with CLOB types.

If the string you are searching for within the CLOB will reliably be found within the first 4KB and there's therefore no need to search beyond this point, then you can certainly cast it to a varchar2, but to avoid the error you are getting, you must substring before you cast:

(LOWER(CAST(SUBSTR(p14_0.exampleColumn,1,4000) AS VARCHAR2(4000)))) LIKE '%test%'

If you have any multibyte characters, however, 4000 chars will overflow 4KB. In that case, since Oracle lacks SUBSTRB for LOBs, you must lower the amount of characters to a level that a few multibyte characters won't push it above 4KB. What that safe level is depends on how often foreign characters show up. E.g.:

(LOWER(CAST(SUBSTR(p14_0.exampleColumn,1,3500) AS VARCHAR2(4000)))) LIKE '%test%'

You could maximize this by using a PL/SQL function that pulls 4000 chars into a varchar2(32767) variable and then uses SUBSTRB to get exactly 4000 bytes, backing off if necessary in an exception handler if that byte boundary happens to split a multibyte character. But at that point the added complexity is generally not worth it. This is of more usefulness when selecting "as close to 4000 chars as possible" in a varchar2 type, rather than scanning values in a WHERE clause as you're doing.

Of course, if you need to search beyond the 4KB point, you'll need to look at the answers others have already given, which will work with LOBs as a LOB and not attempt to cast it to something else.