Oracle批量插入資料SQL語句太長出錯：無效的主機/綁定變數名

標籤：mat   bms   lock   變數   過程   一個   通過   工具   時報   

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

長度限制說明如下：

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         }

 

Oracle批量插入資料SQL語句太長出錯：無效的主機/綁定變數名