INSERT Scripts with Sequence Values and Custom Expressions

Updated October 2025

---

Introduction

Welcome to our blog! Today, we’re excited to introduce a new enhancement to include with Sequence Values and Custom Expressions in INSERT Scripts created by Hora. This update gives you more flexibility and control when generating INSERT statements for your tables.

---

How INSERT Scripts Were Created Previously

Until now, INSERT scripts were generated by scripting the data content of a table. You could adjust the output by:

• Filtering rows with WHERE conditions to limit the data being scripted.

• Hiding columns in the data grid to exclude their content from the export.

While these options worked well, some scenarios still required additional flexibility.

---

What Was missing

Many customers asked for the ability to:

• Populate primary key columns with values from a sequence instead of the original data.

• Insert timestamp or date values using the current system date instead of existing values.

• Define other custom expressions such as constants or function calls directly in the INSERT script, rather than exporting table values.

This level of customization was not possible—until now.

---

The Solution: INSERT Scripts with with Sequence Values and Custom Expressions

In KeepTool 16.2.3, we’ve extended the INSERT Script dialog with a new  Custom Expressions section.

This section provides a grid with two columns:

1. Column Name – Select the target column (e.g., DEPARTMENT_ID).

2. Custom Expression – Enter a value manually or choose one from a dropdown list.

You can use any constant or expression valid in an INSERT statement. Hora suggests common options such as:

• CURRENT_TIMESTAMP

• Date constants

• Sequences from your Oracle schema

---

Example

Suppose you want to generate new primary key values instead of reusing exported ones.

• Select DEPARTMENT_ID.

• Set the value to DEPARTMENTS_SEQ.NEXTVAL.

Hora will then use the sequence to populate the primary key column automatically.

---

The Result

The generated INSERT script now assigns primary key values from a sequence rather than reusing existing table data.

INSERT /*+append_values*/ ALL
INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES( DEPARTMENTS_SEQ.NEXTVAL, 'QA', 204, 2700)
INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES( DEPARTMENTS_SEQ.NEXTVAL, 'Administration', 200, 1700)
INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES( DEPARTMENTS_SEQ.NEXTVAL, 'Marketing', 201, 1800)
-- ..
INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES( DEPARTMENTS_SEQ.NEXTVAL, 'Retail Sales', NULL, 1700)
INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES( DEPARTMENTS_SEQ.NEXTVAL, 'Recruiting', NULL, 1700)
INTO DEPARTMENTS( DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES( DEPARTMENTS_SEQ.NEXTVAL, 'Payroll', NULL, 1700)
SELECT 1 FROM DUAL
/
COMMIT
/

 

This makes it much easier to prepare scripts for populating fresh datasets, testing environments, or initializing new tables.

---

Summary

With the new INSERT Scripts with with Sequence Values and Custom Expressions feature, you can:

• Generate INSERT scripts with dynamic or constant values.

• Use sequences, functions, and other expressions to replace original data.

• Simplify the process of preparing reusable and flexible INSERT scripts.

This enhancement provides greater control and streamlines your workflow when working with INSERT statements in Hora.

---