oracleoracle19c

Oracle SQL query is failing on Oracle Cloud, but not on local Oracle/AWS databases. CAST CLOB

Our application is performing a query to update a value in a CLOB column, but the update needs to only be applied where the CLOB has an existing value. (I understand that this is not a great design paradigm, but it is a legacy application where column lengths have grown to support newer functionality)

This is the error that we get on an Oracle Cloud hosted version of Oracle 19c.

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

[Failed SQL: (22835) UPDATE Table1
SET Value = 'TEXT A'
WHERE DBMS_LOB.GETLENGTH(Value) < 2000 AND CAST(Value AS VARCHAR(2000)) = 'TEXT B']

The SQL above was modified from a previous version where the test for LOB length was not present, and it failed when the CLOB column contained values greater than 2000 characters. This issue was reproducible locally, and adding the test for LOB length allowed this to work on our local versions of Oracle, and Oracle 19C hosted in AWS. It was tested locally with large values in the CLOB column (> 16k bytes)

However, a client has reported that the above query is failing on Oracle 19C hosted on Oracle Cloud. (They provided the output above)

Is there reason why this query would fail on different instances of Oracle. Is there a database configuration setting that would cause this query to not work?

We had also tried re-factoring the query, so that only CLOBs of less that 2000 chars would bubble up to the update query.. However the client says that this is still producing the same error as above.

UPDATE Table1
SET Value = 'TEXT A'
WHERE (Id1, Id2) IN (
    SELECT Id1, Id2
    FROM (
        SELECT *
        FROM Table1
        WHERE DBMS_LOB.GETLENGTH(Value) < 2000
    )
    WHERE CAST(Value AS VARCHAR(2000)) = 'TEXT B'
);

I haven't yet tested this on an Oracle Cloud instance of Oracle 19C yet.. but even if I can reproduce, I still need a way to address this.

Thanks. Appreciate any insight.

Solution

  • I eventually went with:

    UPDATE Table1
SET Value = 'TEXT A'
WHERE DBMS_LOB.COMPARE(Value, 'TEST B') = 0

    I actually found that this was already in some of our source code which I hadn't seen, and this is a little more concise than the other options given.

    Appreciate your input though. Thanks.