Oracle bulk insert or select from Java with Eclipselink

Database Bulk operations like a 10.000 rows insert operation from java can be hard and costly plus you won't come near the time when you do the same in PL/SQL. So the solution is to pass on the array of java JPA entities to a PL/SQL package and let the optimized Oracle Database do the hard work.

To make this work in java we need to use eclipselink at least version 2.3.2, this has the required java classes.

In this blogpost I will show you, how you can retrieve all the departments from the HR demo schema. Add a new department and do a multi insert.

On the PLSQL side we need to define PLSQL row and table types in our Oracle Package. Because Eclipselink can't handle these PLSQL table types directly we also need to define the matching Oracle Types counter parts.

So let's create these objects in the Oracle Database under the HR schema



We are ready with the Oracle Database.

So the first step is to create the Departments entity. This entity is not based on a Table but on the Oracle Type, so we need to add @Embeddable and @Struct annotations.



We also need to create some department utils in which we define the eclipselink PLSQLrecord and PLSQLCollection variables and an ObjectRelationalDataTypeDescriptor descriptor for the entitymanager session.
We need to do this else we can't map the Oracle Types to PLSQL table types. Also eclipselink need to link the Deparments class to the Oracle Type.


Off course we need to create a persistence unit which connects to the HR demo schema.

We are ready to test our solutions.

First we start with a single insert where we pass a department as parameter of a procedure.
These are the steps to make this work.
Define a PLSQLStoredProcedureCall with your Oracle procedure name and all the parameters. Because we only pass on a record so we only need to use the department PLSQLrecord. Create DataReadQuery and add our department entity. Retrieve the ServerSession and add the department descriptor.
And at last fire the procedure.


In the second test we will do a multi insert. This works the same as the single result but only now we need to use the department PLSQLCollection and set our parameters to the Array type.


At last we will retrieve all the departments and we will use a function which returns an array of departments. In this case we will use PLSQLStoredFunctionCall and also use the department PLSQLCollection. As result we get a DatabaseRecord where we can read the RESULT key which contains all our departments.


Here you can download the project on github https://github.com/biemond/jdev11g_examples/tree/master/PLSQLWrapper