Generating Test Data with Blueprint In Oracle APEX

What is a Blueprint?

The Data Generator in Oracle APEX is a powerful utility for automating the creation of test data. Blueprint is a key feature within this tool that allows you to define data structures and generate realistic test data  

A Blueprint defines:

•  Table structure (columns and data types)

•  Data generation rules (random values, sequences, or predefined data)

•  Reusable templates for future projects

Benefits of Blueprint

1. Generate structured test data for APEX applications

2. Quickly build demo databases without manual data entry

3. Simulate real-world data for reports, charts, and forms

4. Performance testing with large datasets

😃Using Blueprint

SQL Workshop > Utilities > Data Generator

Click Create > Blueprint

Now you have 3 options, let's go ahead and check one by one

1. Use Existing Tables 

2. From Scratch

3. Import JSON File

1. Use Existing Tables 

Creates a Blueprint by copying the structure of an existing database table.

For example, you need to populate a production-like DEPT table with test data for a payroll app demo. 

Select "Use Existing Tables" and choose the DEPT  table.APEX auto-detects columns (DEPTNO, DNAME, LOC).

 2. From Scratch 

Let's you manually define a table structure and data generation rules without an existing table. Ideal for new tables not yet in the database and 

 

Then you can add columns for example :

• review_id (Type: Number, Generator: Sequence)

3. Import JSON File

Imports a preconfigured Blueprint from a JSON file (useful for sharing/reusing templates across teams). Your team needs identical test data for orders across Dev/Test environments. choose "Import JSON File" and upload the file then APEX loads the structure .

***********************************************************************************

🔎 NOW I want to focus on column properties

Here in Data Source, we have 4 options:

1. Built-in Data Source

2. Inline Data Source

3 . Sequence Data Source 

4. Formula Data Source

1. Built-in Data Source

Purpose: Use APEX's pre-defined realistic data generators for common data types.

Key Features:

• 50+ categories of sample data

• Data relationships maintained (city↔state, first name↔gender)

• Localized data for different regions

Practical Examples:

1. For a customer_email column:

   • Path: Built-in > Internet > Email Address

   • Sample output: "john.smith@example.com", "sara.jones@mail.com"

2. For a shipping_address column:

   • Path: Built-in > Location > Full Address

   • Sample output: "123 Main St, Anytown, CA 90210"

3. For correlated columns:

   • city: Built-in > Location > City

   • state: Built-in > Location > State

   • Ensures "New York" city always pairs with "NY" state

Tip: Use "Person > Full Name" with "Internet > Email" to generate matching names and emails. 

2. Inline Data Source

Purpose: Define custom value sets with precise frequency control.

Business Examples:

1. Product status distribution:

   • "Active" (Weight: 80)

   • "Discontinued" (Weight: 10)

   • "Backordered" (Weight: 10)

2. Regional sales distribution:

   • "North" (Weight: 30)

   • "South" (Weight: 25)

   • "East" (Weight: 25)

   • "West" (Weight: 20)

3. Priority levels:

   • "High" (Weight: 15)

   • "Medium" (Weight: 60)

   • "Low" (Weight: 25)

Implementation Note: Weights are relative, not percentages. The system normalizes them automatically.

4. Formula Data Source

Purpose: Generate data through PL/SQL expressions and calculations.

Advanced Examples:

Dynamic pricing calculation:

ROUND(base_price * (1 - discount_percent/100), 2)

Age calculation from birth date:

FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date)/12)

Complex string generation:

'CUST-' || TO_CHAR(SYSDATE, 'YYYYMM') || '-' || LPAD(SEQ_NUM, 5, '0')

Conditional values:

CASE WHEN credit_score > 700 THEN 'Premium'

WHEN credit_score > 600 THEN 'Standard'

ELSE 'Basic' END

3 .Sequence Data Source 

Purpose: Generate ordered numeric values with custom formatting.

Tip: Combine with SYSDATE for time-based sequences:

TO_CHAR(SYSDATE, 'YYYYMM') || '-' || LPAD(SEQ_NUM, 5, '0')