ORA-01745: 无效的主机/绑定变量名

ORA-01745: 无效的主机/绑定变量名

1、问题引入

当使用oracle数据库，并且使用mybatis 批量插入数据时，如果插入的条数过多，会抛出一个异常：Java异常之ORA-01745: 无效的主机/绑定变量名。

比如，下面一段代码：

<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>

2、问题排查

oracle 执行批量插入的操作不像mysql，因为oracle的主键不能自动生成，因此我们在使用oracle执行批量插入的时候，经常使用union all的方式执行批量插入。

但是，当数据量过大时，出错：

ORA-01745: 无效的主机/绑定变量名

开始排查时，一般认为是缺失逗号，但是经过查找，代码里并没有缺失或者添加多个逗号，问题依旧存在，这时候怀疑是长度过长，数据库自动截断了sql，所以导致出现错误。

后来想到可能是SQL语句太长，在上例中插入数据是两万多条，拼接成的SQL语句至少好几十k。

这个错误是当数据量达到一定条数的时候，才会抛出。这个异常是因为变量过多引起的。当我们查看执行的sql时，会发现有大量的？？？？，这些都是变量。查看oracle11 的官方文档，明确说明变量的数量不能超过64k（64k=64*1024B=65536B）。16位的2进制最大的数字，即1111111111111111，换算成10进制就是65536，那么我们计算可以插入的条数 n=65536/变量数。

3、解决问题

改写代码，限制每条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分批次插入落地失败！");
							}
						}
					}
				}