Oracle Bulk Insert Scripts

07 Nov 2023

Introduction

KeepTool 16 now creates Oracle Bulk Insert Scripts. Creating Insert scripts from your data is one of Hora’s well-known features that’s available for years now and guess you don’t expect any relevant changes here.

Hello again and welcome to our November newsletter. Let’s dig into creating insert scripts and start with a look at our predecessor version Hora 15. We are connected to Oracle’s demo schema HR (human resources) and displaying the data contents of the JOB_HISTORY table. Let’s right-click the data content grid and choose the menu item “Create INSERT script”.

It contains two sub-menu items

• Save to file
• Copy to clipboard.

Regardless which one you choose, you will end up with an Insert script for your data that looks like that:

It starts with setting some SQL*Plus variables. That spares you some trouble when you are running the script with SQL*Plus later on. It is followed by sample code to temporarily disable triggers and foreign key constraints of detail tables. Since the JOB_HISTORY table doesn’t have any detail tables, there is only sample code for trigger deactivation. Just uncomment the line(s) to make the(se) statement(s) effective.

It is followed by a series of Insert statements, one statement for each data record. Finally, the transaction is closed by a Commit statement. Optionally, triggers and foreign key constraints of detail tables are enabled again.

Now you are done. The script can be run using either Hora, our separate SQL editor or Oracle SQL*Plus.

Improvements in Hora 16

KeepTool 16 comes with some improvements for creating Insert scripts. We will discuss each change in detail later.
As of now, we will give you an introduction what has been changed in version 16:

• Optionally, Hora creates bulk Insert scripts that collect multiple data rows into a one single Insert statement.
  That improves SQL execution speed on the target system.
• Direct Path Inserts are another approach to improve Insert performance.
• Large scripts can now be split into multiple files.
  Whereas this doesn’t boost execution performance automatically, it makes it much easier to open generated files in an editor of your choice.
• The use of these options can be controlled using the settings dialog on the Data Content tab.

Now let’s have a look at the changes in detail.

See how it works!

Explore possibilities of KeepTool.

Try for free

Bulk Inserts

There are several ways to perform a bulk insert into an Oracle database. We decided to implement the INSERT ALL syntax.

The settings dialog allows you to set a “Insert script bulk size”.

It specifies the number of data records collected into a single Insert statement. You can leave it by “1” to create non-bulk Insert statements like generated by Hora 15 and earlier:

For demo, we increased the value to not more than “3”.  Now Hora creates INSERT ALL statements, each collecting 3 data records into one Insert statement.

In real live, it is recommended to increase the value some more, but not beyond 1,000.

The SELECT FROM DUAL clause at the end of the statement is required by the INSERT ALL syntax.

If you are running Oracle 23c, Hora creates an INSERT ALL statement that eliminates both repetitive column names within each statement as well as the formal SELECT 1 FROM DUAL clause.

Test reports state the new syntax performs much faster than traditional INSERT ALL.

Direct Path Inserts

Direct Path Inserts are well-known practice when using Oracle export and import utilities. They also can be used by Insert scripts. Direct path inserts may run much faster, but make sure you are familiar with the benefits and shortcomings of direct path inserts.

You can choose direct path by activating the appropriate checkbox in the settings.

See later for an overview of all settings related to Insert script creation.

For Direct Path Inserts, there are two changes to the generated Insert script:

• For Oracle 11g and later the /*+append_values*/ hint is applied. For Oracle 9 and 10 it is /*+appened*/.
• Each Insert is followed by a Commit statement.
  Commit is required to wait for all parallel activities are finished before the next Insert is executed.

Split large files

If you create Insert scripts from a very large table, script file size may increase to hundreds of MBytes. Opening a file of this size in an editor of your choice, may result in poor editing performance, especially when SQL syntax highlighting is active. Hora now gives you the opportunity to split such scripts into multiple smaller files.

You can adjust the maximum file size by changing Settings | Data Content | Split Insert Scripts every MBytes. When opening in our SQL Editor, it is good practice to start with a maximum size of 50 MBytes.

Settings

Let’s summarize the settings that control the creation of Insert scripts.

On the right side of the Data Content tab, you now find the following (new and already existing) options:

• Split Insert Scripts every MBytes
  The script generator starts with a new file name after the file size exceeds the specified value.
• Insert script bulk size.
  The number of records collected into a single Insert statement
• Single-line Insert statements.
  When checked, both insert field names and values clauses are put into the same line.
  When unchecked, each clause is put into its own line.
  This option is relevant for bulk size 1 only, i.e. non-bulk insert scripts.
• Direct path Insert statements.
  An append hint is applied. Commit after each insert.
• Use time stamp literals for date values.
  For DATE columns, the script generator creates TIMESTAMP literals.
  That makes the script independent of NLS date time settings.
• Escape non-ASCII literals.
  Non-ASCII literals are scripted by their binary representation.

Whereas some options are already available from prior versions, new options have been added to control the new capabilities.

Summary

Hora 16 comes with a couple of improvements for the creation of Insert statements including Oracle Bulk Insert Scripts.
They are most useful in the case of large amounts of data, where performance may be crucial.

Addendum

KeepTool 16.1.0 added a modal dialog that allows you to preview and modify options releated to INSERT-script creation.

You can save your current choice as default.