Easily query testdata with Oracle APEX and the power of SQL Macros

APEX Test data with the Data Generator

Have you ever used the Data Generator feature in Oracle APEX? It is hidden in SQL Workshop > Utilities > Data Generator. It allows you to create data shapes for your tables and, afterward, lets you easily generate test data for them.

Furthermore, it comes with a great set of pre-defined domains like person.first_name or country.name so that the generated data looks realistic. You can customize the data shapes, create your own ones, or hook up values from your own tables.

Check out my ~1-min video to see the Data Generator in action.

Using it from SQL

Today, I had a use-case where I did not want to create a blueprint in the APEX UI. Instead, I wanted to generate rows of test data on the fly from SQL.

I looked over the APEX_DG_DATA_GEN PL/SQL documentation, which describes the data generator API. Unfortunately, most functions require you to reference a blueprint ID, so you need to create one first. I also found the GET_EXAMPLE function, which allows you to pass a domain name and a number of rows and gives you back an apex_t_varchar2 array with example values of the domain.

This sounded like a promising solution, so I tried to construct a single query that would result in 20 rows of a generated first name, last name, country, etc. This is the resulting SQL, which is enormous and complex:

1with first_names as (
2  select rownum as rn, column_value as first_name
3    from table(
4      APEX_DG_DATA_GEN.GET_EXAMPLE (
5        p_friendly_name   => 'person.first_name',
6        p_rows            => 20 )
7      )
8), last_names as (
9  select rownum as rn, column_value as last_name
10    from table(
11      APEX_DG_DATA_GEN.GET_EXAMPLE (
12        p_friendly_name   => 'person.last_name',
13        p_rows            => 20 )
14      )
15), countries as (
16  select rownum as rn, column_value as country
17    from table(
18      APEX_DG_DATA_GEN.GET_EXAMPLE (
19        p_friendly_name   => 'country.name',
20        p_rows            => 20 )
21      )
22), credit_cards as (
23  select rownum as rn, column_value as credit_card
24    from table(
25      APEX_DG_DATA_GEN.GET_EXAMPLE (
26        p_friendly_name   => 'credit_card.card_number',
27        p_rows            => 20 )
28      )
29), phone_numbers as (
30  select rownum as rn, column_value as phone_number
31    from table(
32      APEX_DG_DATA_GEN.GET_EXAMPLE (
33        p_friendly_name   => 'phone.us_phone_number',
34        p_rows            => 20 )
35      )
36)
37select fn.first_name,
38       ln.last_name,
39       ct.country,
40       cc.credit_card,
41       ph.phone_number
42  from first_names fn
43  join last_names ln on fn.rn = ln.rn
44  join countries ct on ct.rn = ln.rn
45  join credit_cards cc on cc.rn = ln.rn
46  join phone_numbers ph on ph.rn = ln.rn
47;
48

We have to call APEX_DG_DATA_GEN.GET_EXAMPLE for each domain, convert the returned apex_t_varchar2 array to rows, and then join everything together. While it works great, it is not really flexible as adding or changing domains is cumbersome.

SQL Macros to the rescue

As the query follows a certain pattern, I thought it would be straightforward to dynamically generate the SQL. I could either build a function that returns the SQL, do some ref-cursor magic, or create a pipelined table function that returns the rows. But then I remembered one of the best new features of the Oracle Database recently: SQL Macros. Recently meaning that unfortunately the macro I show is only available from Oracle 23ai.

SQL macros allow you to encapsulate complex SQL logic into reusable functions with SQL like performance. One of the best use cases is parameterized views. I don’t want to go into too much detail here; please refer to the documentation if you are interested in the details.

The core of a SQL macro looks like a function where you just return the string of an SQL statement. Notice the return type clob sql_macro(table):

1create or replace function gen_test_data (
2  p_parameter in varchar2
3) return clob sql_macro(table)
4as
5  l_sql clob;
6begin
7  l_sql := q'! select ... !';
8  -- do whatever you want to build the SQL
9
10  return l_sql;
11end;
12

Filling in the magic, this is now my SQL macro that generates a with section, the columns in the select and the from and join clauses from the array of domains you pass:

1create or replace function gen_test_data (
2  p_domains in apex_t_varchar2,
3  p_rows in number default 20
4) return clob sql_macro(table)
5as
6  l_sql clob;
7  l_with_clauses clob;
8  l_select_clause clob;
9  l_from_clause clob;
10begin
11  l_select_clause := 'select ';
12  l_from_clause := 'from ';
13
14  <<domain_loop>>
15  for i in 1..p_domains.count loop
16    l_with_clauses := l_with_clauses ||
17      case when i > 1 then ', ' else ' ' end ||
18      't' || i || ' AS (
19          SELECT rownum AS rn, column_value AS ' || replace(p_domains(i), '.', '$') || '
20          FROM table(
21              APEX_DG_DATA_GEN.GET_EXAMPLE(
22                  p_friendly_name => ''' || p_domains(i) || ''',
23                  p_rows => ' || p_rows || '
24              )
25          )
26      )';
27
28      l_select_clause := l_select_clause ||
29        case when i > 1 then ', ' else ' ' end ||
30        't' || i || '.' || replace(p_domains(i), '.', '$');
31
32      l_from_clause := l_from_clause ||
33        case when i = 1 then 't' || i
34             else ' join t' || i || ' on t1.rn = t' || i || '.rn'
35        end;
36  end loop domain_loop;
37
38  l_sql := 'with ' || l_with_clauses || ' ' ||
39    l_select_clause || ' ' ||
40    l_from_clause;
41
42  return l_sql;
43end gen_test_data;
44

More interestingly, see how easy it is now to query the data:

1select * from gen_test_data(
2  p_domains => apex_t_varchar2(
3    'person.first_name', 'person.last_name', 'country.name'
4  , 'credit_card.card_number', 'phone.us_phone_number'),
5  p_rows => 3
6);
7/*
8PERSON$FIRST_NAME    PERSON$LAST_NAME    COUNTRY$NAME    CREDIT_CARD$CARD_NUMBER    PHONE$US_PHONE_NUMBER
9____________________ ___________________ _______________ __________________________ ________________________
10Shaina               Catlin              Mauritania      3465040348097883           (703) 318-4641
11Otha                 Livshits            Oman            3458152993437495           (929) 977-5636
12Gregoria             Oathout             Dominica        6579642930445935           (556) 757-1107
13*/
14

Let’s try another example:

1select * from gen_test_data(
2  p_domains => apex_t_varchar2(
3    'animal.species', 'element.name', 'plant.species'),
4  p_rows => 3
5);
6/*
7
8ANIMAL$SPECIES    ELEMENT$NAME    PLANT$SPECIES
9_________________ _______________ ________________
10A.melanoleuca     Molybdenum      speciosa
11M.novaeangliae    Mendelevium     insularis
12L.rufus           Cerium          asahinae
13*/
14

How do you know which domains are available?

You can query the APEX_DG_BUILTINS view, which lists all the available domains and their categories. In APEX 24.2 there are 193 domains of a wide variety.

1select * from APEX_DG_BUILTINS;
2

As I also frequently use my Alfred extension to look up APEX-specific things. I added support for the list of domains to it. If you are on macOS and also use Alfred, this is probably interesting for you too.

The user types in apex data generator in the Alfred search bar. The Alfred window shows a list of domains with their friendly names. The user types in city to filter the list and then selects 'school.cities'. The word is typed out in a query editor window and then used with the macro to show results.

More you can do with SQL Macros

I used a table macro (which you use in the from section). You can also create scalar macros (which you select as columns). Macros also allow you to pass literal tables and lists of columns, making them super flexible.

Andrej Pashchenko blogged about more complex but super powerful macros, which are great examples of their flexibility. I recommend checking them out: