Oracle批量插入数据异常 java.lang.ArrayIndexOutOfBoundsException

今天测试过程中一个Oracle mybatis批量插入数据的代码报出了一个异常
Caused by: java.lang.ArrayIndexOutOfBoundsException: -32768

具体异常堆栈信息如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Caused by: java.lang.ArrayIndexOutOfBoundsException: -32768
at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:2673)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2206)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3365)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3476)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1080)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)

感觉很奇怪,查看日志SQL打印,这个方也就拼接了400条SQL,参数也不是很多,于是从日志里面copy了一下接口入参在本地用Postman debug了一下,结果第一次居然入库成功,再执行一次,出现了同样的错误,再执行,又成功……反复如此,顿时懵逼。看异常信息,执行ojdbc包内的oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers方法数组下标越界,好吧,放Google搜一下,发现一段这样描述

The 10g driver apparently keeps a global serialnumber for all parameters in the entire batch, with a “short”variable. So you can have at most 32768 parameters in the batch. I was havingthe same exception because I have a INSERT statement with 42 parameters and mybatches can be as big as 1000 records, so 42000 > 32768 and this overflowsto a negative index. I reduced the batch factor to 100 to be safe, and all iswell. I guess your update DML should have a larger number of parameters perrecord, right? (My diagnostic of the bug is just deduction from the symptoms)
https://community.oracle.com/thread/599441?start=15&tstart=0>

说是10g driver statement最大允许参数个数为32768,超过会报错。似乎有点类似,但是我只插入了400条啊,而且每个SQL参数只有9个,也就是3600个参数,远小于32768。
还有另外一个说法

In Oracle Metalink (Oracle’s support site - Note ID 736273.1) I found that this is a bug in JDBC adapter (version 10.2.0.0.0 to 11.1.0.7.0) that when you call preparedStatement with more than 7 positional parameters then JDBC will throw this error.
https://stackoverflow.com/questions/277744/jdbc-oracle-arrayindexoutofboundsexception

感觉也不符合,但是从搜索的结果看,10g 的 ojdbc似乎确实有些问题,于是看了下pom,乖乖

1
2
3
4
5
<dependency>
<groupId>ojdbc14</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.4.0</version>
</dependency>

那么换个版本吧,我们数据库是11.2g的,于是换了个ojdbc6

1
2
3
4
5
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.4.0</version>
</dependency>

嗯,这次很顺利,没有再出现异常,看来ojdbc14确实有些问题,但是还是比较疑惑,单单400条数据,每条9个参数就已经超过限制了吗?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO table (HSTRY_ID, PB_BOND_ID, APPID,
SR_NO_ID, GRP_ID, TNDR_PRC,
THE_REF_YLD, CRT_TM, UPD_TM
)
SELECT SEQ.nextval,A.* FROM (
<foreach collection="list" item="item" index="index" separator="union all">
SELECT
#{item.pbBondId,jdbcType=BIGINT}, #{item.appId,jdbcType=VARCHAR},
#{item.srNoId,jdbcType=BIGINT}, #{item.grpId,jdbcType=VARCHAR}, #{item.tndrPrc,jdbcType=VARCHAR},
#{item.theRefYld,jdbcType=VARCHAR}, #{item.crtTm,jdbcType=TIMESTAMP}, #{item.updTm,jdbcType=TIMESTAMP}
FROM dual
</foreach>) A
</insert>

拼接下来实际SQL如下,类似于insert into tableA select * from tableB

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO table (HSTRY_ID, PB_BOND_ID, APPID, SR_NO_ID, GRP_ID, TNDR_PRC, THE_REF_YLD, CRT_TM, UPD_TM)
SELECT
SEQ.nextval,
A.*
FROM
( SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM dual
union all
SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM dual
union all
SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM dual
union all
......
) A

实际debug了一下,确实出异常的时候OraclePreparedStatement.setupBindBuffers方法short数组 bindIndicators大小超过了32768,换成ojdbc6的时候该方法未调用。