Is it possible to use direct path insertion with JDBC/Java?

Question

We have an application written in C and Pro*C which inserts data from log files into an Oracle 11.2 database using host arrays and bulk insertion. This uses the APPEND and NOLOGGING hints to take advantage of direct path insertion and reduce the amount of redo generated. NOLOGGING makes sense for this as it is a temporary staging table and the data can be recovered from the log files if needed.

We are trying to replicate this functionality in Java but have been unable to make use of direct path insertion of large numbers of records. Is this possible with Java/JDBC?

The things that I have tried and investigated are:

• JDBC batching (both standard batching and Oracle's extensions). This approach saves on round trip times but this is negligible as the application is on the same machine as the database. It also does not use a direct path.
• The APPEND_VALUES hint. This sounds promising but makes little sense as JDBC batching does not appear to actually perform an "array" insert of many records.

From what I understand, direct path inserts only supports the subquery syntax and not the VALUES clause. This cannot be used as the data to be inserted does not exist in the database yet.

I have been unable to find any reference to Java being able to use the host array style loading which Pro*C uses.

As an aside, we are investigating external table loading or SQL*loader and appreciate that these tools are capable of direct path loading, but this question is really about getting a definitive answer on whether direct path insertion is even possible from Java. Understanding the limitations of the Java API is useful not only for this project but for future projects.

So to reiterate the question, is there a way that I can make use of direct path insertion from Java?

Related question:

• Oracle direct-load INSERTs through JDBC?

Answer 1

I found this answer:

direct path inserts are only possible in a insert into x as select * from y scenario. This can be done using jdbc, no problem. This can not be done with insert and values. This also can not be done when the database in in force logging mode. Most of the times when a standby database in connected, the primary database will be in force logging mode.

As Gary Myers mentioned, since 11gR2 there is the APPEND_VALUES hint. As with the 'old' append hint, it should only be used for bulk inserts.