MERGE INTO not working with large CLOB, normal INSERT does

[Jorg Heymans]

Hi,

I have a merge into statement using CLOB columns, simplified here. Database is Oracle 11.2, mybatis 3.3

  MERGE INTO T_Q_MSG_LOG t

  USING dual

  ON (MESSAGE_ID = #{messageId})

  WHEN MATCHED THEN

  ...

  WHEN NOT MATCHED THEN

  INSERT (MESSAGE_ID, ...., STACKTRACE)

  VALUES (#{messageId}, ...., #{stackTrace})

When i attempt to bind more than 4000 bytes to the stackTrace column (the stacktrace field is just a string) i get this error :

Caused by: java.sql.SQLRecoverableException: No more data to read from socket

at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1157)

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

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

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

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

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)

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

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

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)

... 37 more

When i change the statement to a normal insert instead of merge everything works fine. Any ideas ?

Thanks,

Jorg

[Guy Rouillier]

Please clarify.  You say  "When i attempt to bind more than 4000 bytes to the stackTrace column (the stacktrace field is just a string) i get this error".  If you do a merge into with stackTrace set to less than 4000 bytes, does it work successfully?  I'd like to understand if it is always failing, or only when you supply more than 4000 bytes.  4000 bytes sounds like you might be running into a pagesize limitation (which would only affect inline CLOBs.)  You'd need to talk to your DBA about that.

 

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jorg Heymans]

Indeed the merge works fine when I bind less than 4000 bytes to the variable.

But I do not understand why a normal insert statement does not have this limitation. It does sound more like an oracle issue though, you are right.

Jorg