1. Created the below table and then imported the sample file having 230K rows in Oracle 11g. Using Transaction=Full, it took 15 minutes, while using Transaction=Batch with a batch size of 10000 it took 10 minutes.

CREATE TABLE "HR"."IMPORT_TEST"  ( 

        "A1"    VARCHAR2(25) NULL,

        "A2"    VARCHAR2(25) NULL,

        "A3"    VARCHAR2(25) NULL,

        "A4"    VARCHAR2(25) NULL 

        )

Updated the Import code to use prepared statements for Oracle when the Transaction Type is Batch.  This improves the performance significantly.

One drawback with this code change is that the inserted count may not be accurate when an error occurs.  With older versions of Oracle, the executeBatch method doesn't return any useful information if an error occurs, and we can't determine how many records have been successfully inserted in the batch.  Updated the message to say "inserted approximately {0} row(s)" in this case.

Using Oracle 12c, I can get the correct count from the executeBatch method when an error occurs.

Here is an excerpt from Oracle documentation:

Starting from Oracle Database 11g Release 1 (11.1), the executeBatch method has been improved so that when an error occurs in the middle of the batch execution, the BatchUpdateExecution exception that is thrown contains the position of the error in the batch. The BatchUpdateExecution.getUpdateCounts method returns an array of int containing the update counts for the updates that were executed successfully before this error occurred. So if an error occurs in the 5th element of the batch, then the size of the array returned is 4 and each value is Statement.SUCCESS_NO_INFO.

Starting from Oracle Database 11g Release 1 (11.1),...

Though the documentation states starting with Oracle 11.1, we found that not all versions of 11.1.x return the batch count correctly but were able to confirm that the count is returned correctly in Oracle 12c

For 700k records, with Transaction = Batch and Batch Size=9999, instead of 40 minutes, now

Oracle 12c  5s

           11g  5s

           10g  5s

            9i    5s

Need to test fluidshell sqlimport for Oracle and 8i

Completed fluidshell and Oracle 8i as well