Oracle BULK INSERT Data SQL statement too long error: Invalid host/bound variable name

Oracle database, BULK INSERT data with mybatic:

<insert id= "Savebatch" parametertype= "io.renren.entity.NodeDataEntity" databaseid= "Oracle" > INSERT INTO "Node_data" ("node_id", "Data_time", "Data_v                                                                                             Alue "," Data_number ",                                                                                                                                                        "Data_version", "INVALID" ) <foreach collection = "List" item= "item" in dex= "index" separator = "union" > select#{item.nodeid, Jdbctype=varchar}, #{item.datatime, jdbctype=< C2>date}, #{item.datavalue , Jdbctype=varchar}, # {item.datanumber, jdbctype=NUMERIC}, #{item.dataversion, jdbctype=NUMERIC}, #{item.invalid, jdbctype= NUMERIC} from dual </foreach > </insert>       

When the amount of data is too large, an error occurs:

ORA-01745: Invalid host/bound variable name

Online A search said is missing comma, look side-by except, the problem is still.

Later thought might be the SQL statement is too long, in the above example inserted data is more than 10,000, stitching into the SQL statement at least dozens of K.

Related articles on length limitations: http://bbs.csdn.net/topics/390302851?page=1

The length limit is described below:

1. The number of lists in the in clause is the longest of 1000, exceeding that number will be an error, here can be converted to a temporary table to solve;

2. * The character length of the CREATE trigger statement text cannot exceed 32KB (a long, long RAW type cannot be used in the trigger; the trigger can reference the column value of the LOB type column, but not by: NEW to modify the data in the LOB column;) by the Way, The parent keyword in the trigger is valid only in nested table triggers.

3. * 11G ago, dbms_sql to input SQL length can not exceed 32K, because the input parameter can only be VARCHAR2 type, 11G, can use CLOB as input parameter, then canceled this limit

3. * A PL/SQL package, procedure, function, trigger size, maximum on UNIX is 64K, while Windows is 32K size (32K this should not be allowed, see the following test)

4. * How long can SQL statements be? (Netizen said) Oracle documentation is 64K, the actual limitations of some tools will be lower than this value, but the user test found can be very long, or even more than 1M (I have tested 170K no problem). Exactly how long, 10G also did not explain, just with a lot of environment Related: Database configuration, disk space, memory how much ...

5. PL/SQL, the length of the expression/sql itself can reach a relatively long length (50K) or so, such as: v_str:=:new.f1| |:ndw.f2 ...; select:new.f1| |:new.f2 ... into V_str from dual; Also found, if this is written: v_str: = ' a ' | | ' B ' | | ... The length of the allowed expression is greatly reduced. If the expression/sql is too long and exceeds the maximum program length allowed by an Oracle Package/procedure, the compile times Pls-123:program too large error, which is caused by the limitation of the PL/SQL compiler itself, that is, the length of the expression/sql in pl/ The maximum size of the package/procedure is limited by SQL

VARCHAR2 sql up to 4,000 bytes, 2000 kanji characters PL + 32,767 bytes
Clob up to 4Gb

It's possible that after this problem, rewrite the code for the reason, limit each SQL statement to insert up to 2000 after the problem is resolved.

1         if (datalist.size () > 0) {2             new                @Override 4 public                 void Run () {5                     // To prevent the SQL statement from exceeding the length of the error, insert the 6                     if (datalist.size () <=2000 nodedataservice.save (dataList) into several times, 8}else{9 int times = (int) Math.ceil (datalist.size ()/2000.0 ), ten for (int i=0; i<times; i++ ) {One System.out.println (" Insert in batches: "+ i); Nodedataservice.save (Datalist.sublist (i*2000, Math.min (i+1) *2000, Datalist.size ()-1 }. Start ();        

Oracle BULK INSERT Data SQL statement too long error: Invalid host/bound variable name