ColdFusion 2021 CF_SQL_CLOB

[allen800]

We have a table in an Oracle database with a data type CLOB.  We use the CF_SQL_CLOB CFSQLTYPE in the database query but we get an Oracle error indicating that the data exceeds the 4000 character limit with the VARCHAR2 datatype.  We tried using both the ColdFusion Oracle driver and the JDBC driver  from Oracle's site.  Both are throwing the same error (see below).

What is this CF_SQL_CLOB type?  This site indicates that this is for CLOB or NCLOB data types.

https://cfdocs.org/cfsqltype-cheatsheet

Any suggestions would be greatly appreciated.  Thank you.

Error:

OriginalSql = select 1 as test
from request_desc
where to_char(text) = ?
and requests_id = ?, Error Msg = ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4030, maximum: 4000)

This is the table definition:

COLUMN_NAME DATA_TYPE
--------------------    ------------------------------
REQUESTS_ID       NUMBER
TEXT                       CLOB

[BKBK]

Oracle does have CLOB datatype. But that is not what you are passing. You are instead passing a string of characters, which quickly exceeds the buffer limit. 

I expected you to use to_clob(characters), for example, rather than to_char(text).

[allen800]

Unfortunately, Oracle doesn't support CLOB data types in the where clause.  I guess I will have to take a different approach in this application.  Thank you.

https://stackoverflow.com/questions/12980038/ora-00932-inconsistent-datatypes-expected-got-clob

[BKBK]

Have you tried something like this?

WHERE dbms_lob.compare(clob_value_1, clob_value_2) = 0

[allen800]

I came up with a different solution but yours is better.  Thank you!