Ibatis BULK Insert Oracle Summary

1. Use BULK Insert first think of the following insert statement

Insert into a (id,name) values (', '), (', '), but Oracle does not support this notation

Then the query learns that you can use the following notation

Insert All

into a (id,name) values (",")

into a (id,name) values (",") (Note: You can insert one table at a time or multiple tables)

But there is a limitation in this way, that is, the number of rows multiplied by columns cannot be greater than 1000 (I have not verified), and this approach is said to be inefficient (I did not verify)

But when I do bulk insert data from other interfaces, I have no idea how much data there is, and the table has more than 50 databases, so I abandoned this way, pass.

Continue Baidu, know that you can use the view insert, no limit on the number of lines (it is said that the maximum data 64k, to be validated on-line project), and high efficiency

As follows:

Insert into a (id,name)

<iterate conjunction= "UNION all" property= "D" >

Select #d [].id#, #d [].name# from dual

</iterate>

Can succeed, happy. But

We use Oracle, ID is generated by sequence, simple, replace ID with sequence generation just

So

Insert into a (id,name)

<iterate conjunction= "UNION all" property= "D" >

Select Seq_a.nextval, #d [].name# from dual

</iterate>

Run, I go, error: The number is not allowed here, what???

What exactly is under test does not support serial number, open Plsql

Insert into a (id,name)

Select Seq_a.nextval,null from dual

No problem...

And then execute

Select Seq_a.nextval,null from dual

UNION ALL

Select Seq_a.nextval,null from dual

Note that the number is not allowed here, the problem is found, the original is union all and nextval can not be used together ...

Baidu Next Reason:

Restrictions on Sequence Values You cannot use Currval and Nextval in the

Following constructs:
A subquery in a DELETE, SELECT, or UPDATE statement
A query of a view or of a materialized view
A SELECT statement with the DISTINCT operator
A SELECT statement with a GROUP by clause or ORDER by clause
A SELECT statement that's combined with another SELECT statement with the
UNION, INTERSECT, or minus set operator
The WHERE clause of a SELECT statement
The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
The condition of a CHECK constrain

Reference (connection can not be put up, because the content of prohibited http://www.+++ W2BC +++.com/article/18614, access to remove the plus sign)

It should be the result of the red one above, roughly meaning that the two select that is connected by union cannot use a sequence

But we see that in a single select is available,

Select Seq_a.nextval, null from dual is correct:

Dual can be any table, including views, let's first connect all the select with union All to form a view.

Then use this view to query ...

Modify the following

Insert into a (id,name)

Select Seq_a.nextval, T. * FROM (

<iterate conjunction= "UNION all" property= "D" >

Select #d [].name# from dual

</iterate>

) T

Normal operation ...

Also note
Select Id,name from (select seq_b_log_id. Nextval ID, ' elong_deo ' name from dual);

This is also wrong, in violation of the second article, in the view of the query using the ordinal

can be changed into

Select seq_b_log_id. Nextval id,name from (select ' Elong_deo ' name from dual);

3.---The error occurred while applying a parameter map.
---Check the doinsertflightdynamic-inlineparametermap.
---Check the statement (update failed).
---cause:java.sql.sqlsyntaxerrorexception:ora-01745: Invalid host/bound variable name

SQL statement, no comma is written between two fill variables

Ibatis BULK Insert Oracle Summary