Oracle批量插入数据SQL语句太长出错：无效的主机/绑定变量名

爱被打了一巴掌 2022-01-07 00:59 579阅读 0赞

Oracle数据库，用mybatic批量插入数据:

<insert id="saveBatch" parameterType="io.renren.entity.NodeDataEntity"  databaseId="oracle">
            insert into "NODE_DATA"
            (
            "NODE_ID",                                                                                     
            "DATA_TIME",                                                                                     
            "DATA_VALUE",       
            "DATA_NUMBER",                                                                                     
            "DATA_VERSION",
            "INVALID"                                                                                                                                                
            )
            <foreach collection ="list" item="item" index= "index" separator =" union ">
                SELECT                                                               
                    #{item.nodeId, jdbcType=VARCHAR},                                                                                 
                    #{item.dataTime, jdbcType=DATE},                                                                                 
                    #{item.dataValue, jdbcType=VARCHAR},                                                                                 
                    #{item.dataNumber, jdbcType=NUMERIC}, #{item.dataVersion, jdbcType=NUMERIC}, #{item.invalid, jdbcType=NUMERIC} FROM dual </foreach > </insert>

当数据量过大时，出错：

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

网上一查说是缺失逗号，查找并排除了，问题依旧。

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

长度限制的相关文章：[http://bbs.csdn.net/topics/390302851?page=1][http_bbs.csdn.net_topics_390302851_page_1]

长度限制说明如下：

> 1. IN 子句中的LIST个数最长为1000，超过该数目将报错，这里可转用一个临时表来解决;  
>   
> 2.　　\* CREATE TRIGGER语句文本的字符长度不能超过32KB(触发器中不能使用LONG, LONG RAW 类型;触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据;)顺便说一下，触发器中的PARENT关键字，只在嵌套表触发器中有效，  
>   
> 3.　　\* 11G以前，DBMS\_SQL对输入的SQL长度不能超过32K，原因是输入参数只能是VARCHAR2类型，11G后，可以用CLOB作为输入参数，则取消了这个限制  
>   
> 3.　　\* 一个PL/SQL的包、过程、函数、触发器的大小，在UNIX上最大是64K，而WINDOWS则是32K大小(32K这个应该不准，看下面的测试)  
>   
> 4.　　\* SQL语句可以有多长?(网友说)Oracle文档说是64K，实际受一些工具的限制会较这个值低，但网友测试发现可以很长，甚至超过1M(我测试过 170K的都没问题)。具体多长，10G也未说明，只是与很多环境有关：数据库配置，磁盘空间，内存多少。。。  
>   
> 5. PL/SQL中，表达式/SQL本身的长度是可以达到比较长的长度(50K)左右，如：v\_str:=:new.f1||:ndw.f2。。。 ; select :new.f1||:new.f2。。。 into v\_str from dual; 另外发现，如果这样写：v\_str := ‘a’||’b’||。。。则允许的表达式长度将大大的减少。如果表达式/SQL过长，超过了一个ORACLE包/过程允许的最大程序长度，则在编译时报 pls-123:program too large错误，这是pl/sql编译器本身的限制造成的，即表达式/SQL的长度在PL/SQL中受限于包/过程的最大大小  
>   
> varchar2 sql最多4000个字节，2000个汉字字符 pl/sql 最多32767个字节  
> clob 最多4Gb

明白了可能是这个问题后，针对原因改写代码，限制每条SQL语句最多插入2000条后，问题解决。

1         if (dataList.size() > 0) {
     2             new Thread() {
     3                 @Override
     4                 public void run() {
     5                     //为了防止SQL语句超出长度出错，分成几次插入
     6                     if(dataList.size()<=2000){
     7  nodeDataService.save(dataList); 8 }else{ 9 int times = (int)Math.ceil( dataList.size()/2000.0 ); 10 for(int i=0; i<times; i++ ){ 11 System.out.println("分批插入："+ i); 12 nodeDataService.save(dataList.subList(i*2000, Math.min((i+1)*2000, dataList.size()-1))); 13  } 14  } 15  } 16  }.start(); 17 }

转载于:https://www.cnblogs.com/legendjslc/p/7204734.html

[http_bbs.csdn.net_topics_390302851_page_1]: http://bbs.csdn.net/topics/390302851?page=1