测试批量插入数据的时候遇到一个问题:ORA-01745: 无效的主机/绑定变量名
oracle 执行批量插入的操作不像mysql,因为oracle的主键不能自动生成,因此我们在使用oracle执行批量插入的时候,经常使用union all的方式执行批量插入。导致拼接的sql语句过长引起该报错。
<insert id="batchInsert" parameterType="list" useGeneratedKeys="false">
INSERT INTO TEST_ITWUYI_DL(<include refid="Down_Column_List"/>,INSERTTIME,STATUS)
<foreach collection="testItWuyiList" item="item" separator="union all">
select
#{item.recordid,jdbcType=DECIMAL}, #{item.version,jdbcType=VARCHAR},
#{item.batchno,jdbcType=DECIMAL}, #{item.listid,jdbcType=DECIMAL},
#{item.fileid,jdbcType=NVARCHAR}, #{item.filetype,jdbcType=DECIMAL},
#{item.amount,jdbcType=DECIMAL}, #{item.count,jdbcType=DECIMAL},
#{item.createtime,jdbcType=TIMESTAMP},#{item.domain,jdbcType=VARCHAR},
SYSDATE,
#{item.status,jdbcType=DECIMAL}
from dual
</foreach>
</insert>
查看oracle11 的官方文档,明确说明变量的数量不能超过64k(64k=64*1024B=65536B)。16位的2进制最大的数字,即1111111111111111,换算成10进制就是65536,那么我们计算可以插入的条数 n=65536/变量数。
改写代码,限制每条SQL语句最多插入500条后,问题解决:
if (testInserts.size()>0) {
//为了防止SQL语句超出长度出错,分成几次插入
if(testInserts.size()<=500){
Integer flag = testInsertsMapper.batchInsert(testInserts);
if(flag>0){
logger.info("TEST_INSERT分批次插入落地成功!");
}else{
logger.error("TEST_INSERT分批次插入落地失败!");
}
}else{
int times = (int)Math.ceil( testInserts.size()/500.0 );
for(int i=0; i<times; i++ ){
Integer flag = testInsertsMapper.batchInsert(testInserts.subList(i * 500, Math.min((i + 1) * 500, testInserts.size())));
if(flag>0){
logger.info("TEST_INSERT分批次插入落地成功!");
}else{
logger.error("TEST_INSERT分批次插入落地失败!");
}
}
}
}
此次是因为数量少的情况正常执行,数量多的时候报错。平时遇到这种报错可能是关键字或者符号的问题。
参考: