Speed Up Large Data Inserts with Oracle Bulk Insert Scripts in KeepTool

Updated October 2025

---

Introduction

KeepTool 16 introduces a powerful enhancement: native support for Oracle Bulk Insert Scripts. While users have long appreciated the ability to generate INSERT scripts from data in Hora, the new version takes it further—optimizing performance for large data volumes.

Let’s revisit the behavior in Hora 15, then explore what’s new in version 16.

---

Recap: Insert Scripts in Hora 15

Suppose you are connected to Oracle’s HR schema and view data from the JOB_HISTORY table. You right-click the data grid and choose “Create INSERT script”.

You see two options:

• Save to file

• Copy to clipboard

Whichever you pick, Hora generates a standard INSERT script. The script begins with SQL*Plus boilerplate (setting variables), includes commented stubs to disable triggers or FK constraints if needed, then outputs individual INSERT statements per row, and finally re-enables constraints and finishes with a COMMIT.

This approach works well for moderate data sets—but it can become slow or unwieldy when insert volumes grow.

---

What’s New in Hora / KeepTool 16

Version 16 brings several enhancements to script generation:

• Bulk Insert Option: Now you can group multiple rows into a single INSERT statement, improving execution throughput.

• Direct Path Inserts: Optionally generate scripts that use Oracle’s direct-path insert hints for even faster loading.

• Script Splitting: Large insert scripts can be split into multiple smaller files—improving editor usability and maintainability.

• These options are fully configurable under the Data Content settings tab.

Let’s dig deeper into each improvement.

---

Bulk Inserts with INSERT ALL

To support bulk insertion, we adopted Oracle’s INSERT ALL syntax.
In the settings dialog, you can define a bulk size—i.e. how many rows to include per INSERT ALL statement.

If you set bulk size to 1, it behaves exactly like in Hora 15 (individual inserts). In our demo, we set it to 3; Hora produces statements like:

In Oracle 23c, Hora optimizes further—omitting repeated column names and the SELECT 1 FROM DUAL clause where allowed.

While bulk inserts don’t always guarantee faster performance in every scenario, tests suggest they often outperform traditional row-by-row inserts.

---

Direct Path Inserts

For further speed improvement, direct-path inserts are supported. These bypass some conventional SQL processing, but they come with trade-offs. Use this option with caution.

If enabled:

• An APPEND_VALUES hint is added (for 11g+; older versions use APPEND).

• Each INSERT is followed by a COMMIT, to ensure parallel operations complete correctly.

---

Splitting Large Scripts into Chunks

Large datasets can generate massive scripts (hundreds of MBs), which are difficult to open or edit. Hora 16 lets you split insert scripts into multiple files based on a maximum file size.

For convenience, we recommend starting with a default size of 50 MB when using the embedded SQL Editor.

---

Configuration Options Summary

On the Data Content tab, the following controls are now available:

• Split Insert Scripts every MBytes – begin a new file when size limit is reached

• Insert script bulk size – number of rows per INSERT ALL

• Single-line Insert – whether to write field and value clauses on one line (for non-bulk mode)

• Direct path Inserts – toggle appending hints & commits

• Use timestamp literals for DATE columns – ensures script independence from NLS settings

• Escape non-ASCII literals – serializes problematic text properly

---

Summary

Hora 16’s enhancements to INSERT script generation—bulk inserts, direct-path support, file splitting, and more—are particularly beneficial for large-volume data operations where performance matters most.

If you handle sizable datasets, these features can reduce runtime and improve manageability of generated scripts.

---

Addendum

Version 16.1.0 introduced a modal preview dialog for adjusting INSERT script options before generation.
You can also save custom settings as defaults.

---