The need for data

I was a mentor at the "World Innovation Day Hack 2023" Hackathon a few weeks ago. And, of course, at the heart of all endeavours was the data. All of the teams needed certain specialised datasets to construct their apps on, but virtually all of them also required some well-known information and a common data source, such as a list of countries or languages. I won't go into too much detail on how many different sites you can obtain that information from. I'll only recommend Kaggle as one of the top websites for this purpose that I often visit. However, finding the data set, extracting the data into the database, and creating the necessary database structures such as tables and views all take time.

The revelation

Following that event, I met with Anton Nielsen for a code review, during which I shared my need for bespoke datasets. And then he showed me a lesser-known feature of Oracle APEX that not many people use, and certainly not me, to produce synthetic data and populate tables with it. He showed some examples of using the APEX Data Generator which is a truly amazing tool. Apart from the link to the official Oracle documentation, you can also learn more about how to best use Blueprints in this great blog article by sssuarez.

The benefit

What's nice is that Oracle APEX has a repository of prebuilt data sets that have been created with great care, and some of them have even been manually adjusted row by row to ensure that data is cleansed and accurate. And they're ready to use out-of-the-box. They are all available when using the APEX Builder and the Blueprints feature. But they can also be accessed if you know which views keep that information. All you have to do is execute the right query to get the needed data.

For my personal use and in case someone else needs to know what kind of information is available, I created a small DEMO page where you can see a list of available data views and preview the information. Kudos to the whole team that gathered this information, cleaned it and made it possible for all APEX developers to benefit from it.

The Demo

Here is a query that gives you all the available views:

 select distinct apex_view_name
   from apex_dictionary
  where apex_view_name like 'APEX_DG_BUILTIN_%'
  order by apex_view_name;

Alternatively, you can use this one:

select *
  from all_views
  where view_name like 'APEX_DG_BUILTIN%'
   and owner = 'APEX_230100'
  order by view_name;

Remember to change the owner in your query, depending on the APEX version you are using. This query will give you duplicate view names if you have multiple versions installed and you are missing the owner filter.

A list and a preview of the datasets can be found in my DEMO here:

https://apex.oracle.com/pls/apex/r/gamma_dev/demo/built-in-datasets

Follow me

Like that post? Follow me on Twitter and LinkedIn!