2 Replies Latest reply on Nov 4, 2015 11:03 PM by MaxOrgiyan-Oracle

    ORA-29902 When using Prepared Statement with JSON Full Text Search

    3048146

      Hello all,

       

      I'm currently working in a WLS 12.1.3 environment backed by a database version 12.1.0.2.0 and am attempting to use the new JSON functionality provided in that release. WLS has been patched with 20741228 to upgrade the JDBC driver, and the application itself references the latest 12.1.0.2.0 driver.

       

      The database setup is basically a CLOB column with an "is json" constraint, and a full text index. Example:

       

      create table JSON_TEST ( GID varchar2(50) not null, JSON_TAGS CLOB );

      alter table JSON_TEST add constraint CST_JSON_TAGS_TEST check("JSON_TAGS" is json (strict)) enable;

      create index IDX_JSON_TAGS_TEST on JSON_TEST (JSON_TAGS) indextype is CTXSYS.CONTEXT PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC(ON COMMIT)');


      insert into JSON_TEST (GID, JSON_TAGS) values ('testtesttest1', '{"key1":{"key2":"val1"}}');

      insert into JSON_TEST (GID, JSON_TAGS) values ('testtesttest2', '{"key1":{"key2":"val2"}}');

      insert into JSON_TEST (GID, JSON_TAGS) values ('testtesttest3', '{"key1":{"key2":"val2", "key3":"val2-1"}}');

      insert into JSON_TEST (GID, JSON_TAGS) values ('testtesttest4', '{"key1":{"key2":"val3"}}');

       

      In the application, I create the prepared statement and execute it as follows:

       

      PareparedStatement stmt = connection.prepareStatement("select * from JSON_TEST where json_exists(json_tags, '$.key1.key3') and json_value(json_tags, '$.key1.key2') = ?");

      stmt.bindString("val2");

      stmt.executeQuery();

       

      What results is an exception, with the following:

      Caused by: java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine

      ORA-01489: result of string concatenation is too long

       

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)

              at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)

              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)

              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)

              at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)

              at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)

              at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)

              at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)

              at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)

              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)

              at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)

              at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)

              at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)

              at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:141)

              at com.test.JSONUtil.testQuery(JSONUtil.java:826)

       

      If, however, I execute the statement without binding the parameter (i.e. hard coding the value into the query string), everything works wonderfully.

       

      I was hoping someone could possibly shed some light on this issue.