Using JavaScript to create test data in Oracle Database 23c

If you think this post sounds familiar, it’s because Martin Bach already wrote it.

If Martin already wrote it, then why are you writing this?

Because I am going to show how to do it using our Web IDE vs from the command-line. By the way, there is no ‘right’ or ‘good’ way for everyone, you do what works best for you!

Step 1: Installing the faker module in the database

Using SQL Developer Web’s JavaScript page, I can easily create a new module by simply pointing to the URL of the ECMAScript (+ESM).

So, using the Web button,

Give it a name, ‘fakerJS’, and hit the ‘Save’ button’ – and now I can see it in my list of modules for my schema, HR.

Step 2: Create the fakerenv Environment

Switching from Modules to ‘Environments’ in the browser, I can create a new one, and simply drag over the Module we just created.

Environments allow us to reference other JavaScript modules, but instead of pointing to files on the operating system, we’re pointing to one or more other modules, via this Environment construct in the database.

Step 3: Create Martin’s new MLE Module

This time instead of referencing a file from the HTTPS URL, we’re going to copy and paste Martin’s code into the editor, giving our new module it’s name, and setting the Environment.

Step 4: Creating the Call Specification

MLE JavaScript modules are invoked via PL/SQL helper programs, these are called the ‘Call Specifications.’

Now Martin sets up a pkg with plsql functions for each of the 4 faker items, random_number, random_string, random_date, and random_employee.

For this, we can actually just take his PL/SQL package spec and put it into a SQL Worksheet and run it, like so –

Alternatively, you could use our MLE JavaScript Call Spec editor to automatically define a procedure or function for each of those programs, like so –

So I can pick the type of plsql wrapper, procedure or function, name the inputs, change up the outputs, in case case I want to RETURN a JSON, and of course we’ll show you the code if you’d like.

And if you’ve forgotten what your JavaScript function does, just click the name, and we’ll pop up a preview of your previous module handiwork –

Step 5: Start using our new Test Data Generators!

Like Martin, let’s get us a new employee, of the ‘female’ variety.

Ok, now let’s get 10 more employees. I’ll reshare Martin’s code here for convenience –

with lots_of_employees as (
        select
            case when mod(rownum, 2) = 0 then
                mle_faker_api.random_employee('female')
            else
                mle_faker_api.random_employee('male')
            end employee
        from
            dual
       connect by level
           <= 10
   )
   select
       jt.*
   from
       lots_of_employees e,
       json_table(
           e.employee,
           '$'
           columns (
               first_name      varchar2    path '$.firstName',
               last_name       varchar2    path '$.firstName',
               email           varchar2    path '$.email',
               phone_number    varchar2    path '$.phoneNumber',
               hire_date       date        path '$.hireDate',
               job_id          varchar2    path '$.jobId',
               salary          number      path '$.salary',
               commission_pct  number      path '$.commissionPct',
               manager_id      number      path '$.managerId',
               department_id   number      path '$.departmentId'
           )
      ) jt;

And running that as a script…

And now instead of adding 10 employees like Martin did, let’s add 2,000, in batches of a thousand.

One last party trick, generating JSDoc

It’s just a single click, and then you have some nice MarkDown to describe your JavaScript module.