oracle 输入绑定,oracle 使用 union all 批量插入报错 ORA-01745: 无效的主机/绑定变量名...

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

用法经常是这样的

insert into t_virgo_table(ID,BIZ_TYPE,SYSTEM_FROM,ORDER_NO,CREATE_TIME)

SELECT SER_t_virgo_table.NEXTVAL ID,t.* FROM (

select #{item.bizType},

#{item.systemFrom},

#{item.orderNo},

sysdate as CREATE_TIME

FROM DRUL

)t

但是如果插入的条数过多，会抛出一个异常：Java异常之ORA-01745: 无效的主机/绑定变量名

如数是数据量达到一定条数的时候，才会抛出，这个异常时因为变量过多引起的；查看你执行的sql，你会发现大量的？？？？，这些都是变量。查看oracle11 的官当文档，明确说明变量的数量不能超过64k。64k=64*1024B=65536B。16位的2进制最大的数字，即1111111111111111，换算成10进制就是65536。

那么我们计算可以插入的条数 n=65536/变量数

以下是官方文档内容

Variables

A number of variables can be used with the ALLOCATE descriptor. These include: array_size, desc_nam, and occurrences.

array_size

The optional array_size clause (an Oracle extension) supports table processing. It tells the precompiler that the descriptor is usable for table processing.

GLOBAL | LOCAL

The optional scope clause defaults to LOCAL if not entered. A local descriptor can be accessed only in the file in which it is allocated. A global descriptor can be used in any module in the compilation unit.

desc_nam

The desc_nam variable defines the local descriptors that must be unique in the module. A runtime error is generated if the descriptor has been previously allocated, but not deallocated. A global descriptor must be unique for the application or a runtime error results.

occurrences

The optional occurrences clause is the maximum number of host variables possible in the descriptor. The occurrences variable must be an integer constant between 0 and 64K, or an error is returned. The default is 100. A precompiler error results if it does not conform to these rules.

Examples

EXEC SQL ALLOCATE DESCRIPTOR 'SELDES' WITH MAX 50 END-EXEC.

EXEC SQL FOR :BATCH ALLOCATE DESCRIPTOR GLOBAL :BINDDES WITH MAX 25

END-EXEC.

文档链接！https://docs.oracle.com/cd/E11882_01/appdev.112/e10826/pco10ady.htm#i1005237