Many a times we face a situation where we have to insert huge records of data in a target database table. I have seen that whenever transactions were happening over WAN and especially with the case of Oracle database, the inserts into the table were very slow. We tried out all sets of permutations and combinations on the receiver channel like trying to insert in Batch mode with an updated JDBC driver but with no luck. While doing my share of RnD on the issue, I came across an interesting workaround. In one of the links which I found during googling out a solution, I found the below syntax for inserting multiple rows in a single statement for Oracle DB. insert into table (COL1, COL2, COL3, COL4) select ‘RC11’, ‘RC12’, ‘RC13’, ‘RC14’ from dual union all select ‘RC21’, ‘RC22’, ‘RC23’, ‘RC24’ from dual The above statement inserts two rows at one go and in one single statement. I decided to leverage over this syntax and use it in PI for inserting data. Here is how I went about it: Firstly, create a data type conforming to the standard document type with action = “INSERT” as required by the JDBC receiver adapter. A sample data type structure is shown below. Make sure that occurrence of the access node is unbounded and that of the Statement node is 1.
14 Comments
