APEX Data Generator

Introduction

Data Generator is a relatively new feature, launching last year as part of the APEX 22.1 update. This new addition provides the developer with a built-in solution for creating customised sample data to aid development and testing.

The introduction of Data Generator was one of the many recent improvements that fall neatly in place with the ever-expanding line of developer utilities, which aim to increase the quality and efficiency of the APEX development experience.

Using Data Generator

Data Generator is under the 'utilities' section of the SQL Workshop. Any previously created blueprints are stored here. These can all be edited, exported, and used multiple times to re-generate data. For building a new sample data set, you have the option to create a new blueprint.

Creating Blueprints

Data Generator works using blueprint files. Blueprints are JSON files that contain all the information on how the sample data should be created, including the tables, columns, and data sources. 

Thankfully, we do not need to write blueprint files from scratch. Instead, Data Generator contains a blueprint designer that guides us through building blueprints based on one of the following options:

• Use Existing Tables – This allows you to select several pre-existing tables and build the blueprint based on these table definitions. The generated sample data can even be inserted directly into the tables.
• From Scratch – Creating blueprints from scratch allows you to build a sample data set more freely by fully defining the table, column, and data constructs within the blueprint.
• Import JSON File – You also have the option to export/import blueprints by either entering some JSON code or uploading a JSON blueprint file. The data that gets generated may be different each time, but will still adhere to the rules and definitions set out in the blueprint.

Choosing any of these options will always take you to the blueprint designer page.

Blueprint Designer

Blueprint designer is where you will define the structure and attributes of the data to be generated based on tables, columns, and data sources.

If you are building a blueprint based on existing tables, the blueprint designer will attempt to make some intelligent decisions on the types of data to use and the source of the data. For example, if a chosen table has a foreign key, the blueprint designer will create a custom data source based on the existing related table. It will then use the primary key column from this source to generate the data for the foreign key column ensuring relational integrity maintains between the two.

If you want to choose your data source for a column, the following options are available:

• Blueprint – A conditional option based on whether the blueprint has more than one table. This selection lets you make use of foreign key relations between sample data by referencing a column from one blueprint table column as the source of another.
• Custom Data Source – An existing table from the database or a custom SQL query.
• Built-in – Real-world sample data sets.
• Inline – Custom hard-coded values. You can define a frequency that will determine how often each value is generated compared to another.
• Sequence – A numeric sequence with custom start and incremental values.
• Formula – A PL/SQL expression. Formulas can also be used in conjunction with any other data source options and reference other columns within the blueprint.

Depending on the data type and source, some further attributes can be set at the column level, including number precision, format mask, whether a value must be required (and if not, what percentage should be null), maximum length, and multi-value options. When working with existing tables, the blueprint designer will again make some decisions for you here to reduce the likeliness of errors. It will populate the maximum length values to match that of the target column, and if a column has a not null constraint, it will mark the blueprint data as required for that column. Of course, you are also free to change any of these if needed.

These few points are worth considering before you complete your blueprint:

• Sequence of operation –  It is essential to check the table sequence values. If you are using one table within the blueprint as a data source for another blueprint table, you will want to ensure the sequence values for these are in the correct order. The sequence will prioritise the smallest value first and ascend in numerical order. The same applies to columns. If you are referencing columns in the formulas elsewhere, you should ensure these columns have a smaller sequence value than where they are referenced.
• Number of rows – This can be changed at the table level and allows you to specify different values for each table. There does not appear to be an upper limit, although larger data sets may take some time to generate, depending on the complexities of the blueprint.
• Maximum length – If you are inserting directly into tables, ensure your defined maximum lengths for each column do not exceed that of the target table.

Once you are happy with your blueprint, you can save and export it or preview and generate your sample data.

Generating Data

The output of your sample data can be generated to files in several formats – JSON, CSV, or SQL (inserts). CSV outputs where multiple tables are used will produce a ZIP file containing a CSV file for each table.

When a blueprint is based on existing tables, there will be the additional option to insert the generated data straight into the database. There will be the choice of either using regular insert into (if any row fails, it skips to the next) or fast insert into (if a single row fails, no data will be inserted).

Summary

The range of development tools in APEX continues to grow, with Data Generator proving itself as an excellent addition to the collection. Having built-in functionality to create sample data takes Data Generator the extra mile by opening up the ability to directly interact with the database, whether building blueprints based on existing tables, using data sources or inserting generated data directly into the database.

Data Generator and the blueprint designer present a level of versatility that will cater to almost any sample data need whilst maintaining simplicity in understanding and usage. Hopefully, this is a utility that will continue to expand and improve with even more enhancements in the future.

For more information, check out our APEX services, and if you liked this blog, check out our other APEX blogs here.