Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-31981

Fix ORA-00932 when using a CLOB is a WHERE clause

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2021.40
    • Fix Version/s: 2021.40, 2023.1
    • Component/s: Core VCS
    • Release Notes Summary:
      A new property is added to use setClob method in PreparedStatement for Oracle.
    • Release Notes Description:
      Hide

      Added a new property nuxeo.oracle.use.clob to use the setClob method in PreparedStatement for Oracle. Defaults to false.

      To use setClob method, configure in nuxeo.conf:

      nuxeo.oracle.use.clob=true
      Show
      Added a new property nuxeo.oracle.use.clob to use the setClob method in PreparedStatement for Oracle. Defaults to false . To use setClob method, configure in nuxeo.conf : nuxeo.oracle.use.clob=true
    • Tags:
    • Backlog priority:
      1,000
    • Sprint:
      nxplatform #92
    • Story Points:
      1

      Description

      After fixing NXP-31955 to use setClob for CLOB column, it appears that Oracle throws an error when using a CLOB in a WHERE clause with the following stacktrace

      SyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

org.nuxeo.ecm.core.api.NuxeoException: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.compareAndSet(SQLKeyValueStore.java:918) ~[nuxeo-core-storage-sql-2021.40-SUPNXP-45992.jar:?]
 at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.compareAndSet(SQLKeyValueStore.java:815) ~[nuxeo-core-storage-sql-2021.40-SUPNXP-45992.jar:?]
 at org.nuxeo.ecm.core.transientstore.keyvalueblob.KeyValueBlobTransientStore.atomicUpdate(KeyValueBlobTransientStore.java:381) ~[nuxeo-core-cache-2021.37.4.jar:?]
 at org.nuxeo.ecm.core.transientstore.keyvalueblob.KeyValueBlobTransientStore.putParameter(KeyValueBlobTransientStore.java:417) ~[nuxeo-core-cache-2021.37.4.jar:?]
 at org.nuxeo.ecm.automation.server.jaxrs.batch.Batch.addFile(Batch.java:187) ~[nuxeo-automation-server-2021.37.4.jar:?]
 at org.nuxeo.ecm.restapi.server.jaxrs.BatchUploadObject.addBlob(BatchUploadObject.java:309) ~[nuxeo-rest-api-server-2021.37.4.jar:?]

(...)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
(...)
at org.nuxeo.ecm.core.storage.sql.kv.SQLKeyValueStore.compareAndSet(SQLKeyValueStore.java:909) ~[nuxeo-core-storage-sql-2021.40-SUPNXP-45992.jar:?]

      We tested a workaround with the customer : if the value's length is less than 8192, we continue to use setString. If above, we use setClob based on the hypothesis that the WHERE clause will never test a very long STRING

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: