This section guides you through a worked data generation example in Oracle. It includes steps to create an empty target database, which is then used in the data generation example. It provides links to further examples and to resources to understand how the data generator works.

Contents

Preparation

Preparation of Data Generator

Please do the following before beginning the worked example

1. Install the data generator CLI.
2. Verify your installation by running the following command in a terminal window:
   1. Windows cmd: datagenerator.exe --version
   2. Windows PowerShell: ./datagenerator --version
   3. Linux: ./datagenerator --version

The data generator should report its version number as below (example in Windows cmd).

C:\DataGeneration\>datagenerator.exe --version
0.2.0.0

Preparation of Database Engine

You will need the Oracle Instant Client. Specifically, you need to also install the SQL*Plus Package so that you can access the command-line SQL query tool, sqlplus. Oracle Instant Client and SQL*Plus are downloaded as .zip files, so you would need to find your own directory for placing them and making sure that they are extracted into the same folder. You can run sqlplus by opening a command prompt directly in this directory, or add the directory to your PATH environment variable to enable running sqlplus from any where on your computer.

Executing SQL queries in sqlplus requires setting up an Oracle administrator account and password. The administrator account is typically SYS. After creating the password and finishing the setup, execute the following at a command prompt:

sqlplus sys@localhost:1521/FREEPDB1 as sysdba

Enter your password when prompted. If successful you should see something similar to the following (can vary depending on your Oracle and SQL*PLUS version):

SQL*Plus: Release 23.0.0.0.0 - Production on Wed Nov 1 10:53:53 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>

Now you have entered sqlplus and you should be able to execute SQL queries against the databases you created. To exit sqlplus, simply type:

quit

Create a target database

Download the SQL script sample-oracle-database.sql. You will need to edit the line CONNECT "TargetDatabase"/P455w07d@FREEPDB1; This line assumes the Oracle service is FREEPDB1 and that your password is P455w07d. If you use a different service and/or (hopefully) password, you will need to make changes accordingly.

You need to execute this script file in your Oracle server using your administrator account. Here is the command to execute the script in sqlplus:

@/path-to-script/sample-oracle-database.sql

This will create a user "TargetDatabase" with four tables, containing example data. It represents a simple social media website, where users create posts and comment on posts, and users belong to organizations.

Check the database contents

Check that the tables exist and are empty. Use a SQL query such as this below which lists the data in a table:

SELECT * FROM "Users";

And a SQL query as below which to list the data in a table. The database name needs to be in full capital:

SELECT object_name FROM dba_objects WHERE upper(owner)='SOURCEDATABASE' AND object_type='TABLE';

Run the data generator

The commands below run the data generator. If the commands cannot run successfully, you may need to work through the connection strings section below to configure them to your environment.

Click on the sections below to view the example command lines:

./datagenerator.exe `
--database-engine oracle `
--target-connection-string 'DATA SOURCE=localhost:1521/FREEPDB1;USER ID=TARGETDATABASE;PASSWORD=P455w07d' `
--rows-to-generate 1000

datagenerator.exe
--database-engine oracle ^
--target-connection-string "DATA SOURCE=localhost:1521/FREEPDB1;USER ID=TARGETDATABASE;PASSWORD=P455w07d" ^
--rows-to-generate 1000

#!/usr/bin/env bash
./datagenerator \
--database-engine oracle \
--target-connection-string "DATA SOURCE=localhost:1521/FREEPDB1;USER ID=TARGETDATABASE;PASSWORD=P455w07d" \
--rows-to-generate 1000

Connection strings

You may need to change the connection strings to suit your environment. The connection strings identify the source and target database.

Change PASSWORD to match that of your own.

Use DATA SOURCE to specify the Oracle server containing the database. In the example above we are using localhost to connect to an instance running on the same computer that is running the data generator. 

We have further advice on connection strings in our troubleshooting and known limitations section. There is also a lot of information online about connection strings.

Output from the command

The command will produce detailed output similar to that shown below. The details may vary. Check that the last line of output says Data generation completed.

2023-11-09 13:58:14.119 +00:00 [INFO] DataGenerator has started in 'Debug' mode. Log folder is C:\ProgramData\Red Gate\Logs\TDM\DataGenerator.
2023-11-09 13:58:14.128 +00:00 [INFO] Validating data generation configuration data...
2023-11-09 13:58:14.383 +00:00 [INFO] Completed initial validation of data generation configuration data in 00h 00m 00s.254ms
2023-11-09 13:58:14.401 +00:00 [INFO] Using Application Insights telemetry reporter in bucket 'tdm-test' (debug) in developer mode.
2023-11-09 13:58:14.652 +00:00 [INFO] Running Oracle database table extraction using target '' database on 'localhost:64637/FREEPDB1' server...
2023-11-09 13:58:16.712 +00:00 [INFO] Successfully extracted 4 table(s) (with 4 relationships) from target '' database on 'localhost:64637/FREEPDB1' server in 00h 00m 02s.059ms.
2023-11-09 13:58:16.811 +00:00 [INFO] Validating target '' database on 'localhost:64637/FREEPDB1' server structure...
2023-11-09 13:58:16.863 +00:00 [INFO] Completed validation of target '' database on 'localhost:64637/FREEPDB1' server structure in 00h 00m 00s.052ms...
2023-11-09 13:58:16.928 +00:00 [INFO] Creating data generation plan
2023-11-09 13:58:17.012 +00:00 [INFO] Starting to generate data
2023-11-09 13:58:45.161 +00:00 [INFO] Data generation completed in 00h 00m 30s.759ms.

Check the data in the target database

Check the contents of the tables in the target database. You should find that each table contains 1000 data rows.

Congratulations! This shows that the data has been generated successfully.

Further examples

The documentation on configuration files explains the formats and provides worked examples of their use, that build on this worked example. 

Learning more about how the data generator works

Now that you have carried out the worked example, you might like to understand more about how the data generator works. Please consult the page Using the worked example to understand the data generator. This page uses the output and the commands from the worked example to explain how the data generator produces referentially valid data.