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
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.
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:
Now let’s have a look at the changes in detail.
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 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:
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.
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:
Whereas some options are already available from prior versions, new options have been added to control the new capabilities.
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.