Remark: The following scripts are run on an Oracle database 23c FREE - Developer Release using sqlplus.
First things first, let's start a 23c FREE Developer Release Docker image using Podman:
The command will configure the SYS and SYSTEM passwords as free (in lower case), it will allow connecting to the FREEPDB1 pluggable database using local port 1521 and it will mount our local folder mle into the container as /home/oracle/mle.
Next, let's create a new user named developer and grant the right privileges for getting started:
For the rest of this post, we'll focus on the Chance.js module from Victor Quinn under MIT License. This module is really interesting when working on data as it provides numerous ways to "generate random numbers, characters, strings, names, addresses, dice, and pretty much anything else"; in brief 100+ new data generators.
The latest version available at the time of writing is 1.1.11, so let's download it into our mle folder so that it can be used by the database:
And now connected as developer, run:
So far, we've installed the Chance.js module and an extended one that exports properly each of the function with a signature compatible with the Oracle database data types (including the native JSON and new 23c BOOLEAN).
You can now use all the new functions to generate random data. See the following examples.
Our first example will focus on very basic data generation:
If you want more than 1 row, you can leverage this trick:
Remark that DATE is an Oracle datatype and thus you need to use lowercase call to distinguish it from the function, that's why the double-quotes are mandatory and the lower case must be used here (as for "integer"). In the previous example, the first date is 15th of September 2049 because of my localized session (France).
When passing a JSON parameter, we can ask for an extended generation mode for a given data generator. But the PL/SQL function signatures have to
be compatible because PL/SQL is strongly typed. Hence the trick here is to have a third signature for that case:
If we had a second parameter then we allow the resulting type to be an Oracle database DATE instead of a VARCHAR2:
You can see how it is now easy to generate random data, it's up to you... but one last word!
Let's see some examples:
You'll note that the ordering of the JSON fields is not respected. This is expected since the template() function leverages the Object.keys() function which in the latest ES standard doesn't guarantee the ordering anymore. But that's not really a problem, right?
This would bring some interesting ability to populate the JSON Relational Duality Views examples from the 23c FREE documentation.
As you can see, the possibilities are infinite and benefiting from such a data generator for both simple values but also JSON documents is incredibly useful. This could be used for Oracle Application Express (APEX) applications, demos, random data for unit testing, etc.
And now, it's up to you!
Don't forget to provide us your feedback on the 23c FREE community forum that we monitor and where you can provide feedback.