Using QuickSQL to generate table DDL and sample data in Oracle Database 23ai without APEX

Oracle recently announced that QuickSQL, previously exclusive to Oracle APEX (APEX), was generally available on GitHub. This is great news for anyone interested in quickly generating a database schema using the popular shorthand notation but doesn’t have access to APEX. Although you get a different developer experience than you get with APEX, there is still a lot of value in using the GitHub release in your projects.

In addition to the examples on Github, it is possible to use QuickSQL inside Oracle Database 23c on Linux x86-64 as a stored code unit. If you are interested in the how to do that please read on.

What is Quick SQL?

If you haven’t seen Quick SQL in action, you really should: it’s a beautiful tool for any database developer, and its usefulness certainly isn’t limited to APEX. Here’s an example of QuickSQL in APEX 23.2:

Using the shorthand notation, it is straightforward to create database schemas, including parent/child relations. The QuickSQL grammar used in this article is documented as part of the GitHub project.

In addition to creating database schemas it is possible to let QuickSQL generate dummy data matching the tables’ data types and referential integrity constraints. Now I’m coming full circle with my previous posts on generating sample data for Oracle Database 23c:

QuickSQL takes the entire experience to the next level.

Using QuickSQL inside the database without APEX

Thanks to Multilingual Engine (MLE) powered by GraalVM it is possible to import the QuickSQL module as a schema object, provided you are using Oracle Database 23c. This article assumes that you are familiar with In-Database JavaScript; if not, please head over to the official documentation set to get you started.

Loading the module into the database

As with all MLE modules, you must ensure you import the ECMA Script Module (ESM). The file to import is found in quicksql/dist/quick-sql.js.

> Please refer to the project’s GitHub project site for more details about its license and use implications. The article assumes your legal and IT Security departments (as well as any other party) agreed that using the module in your code is safe and compliant with your license. Using a 3rd-party code in your application typically requires the completion of specific compliance steps, which are outside the scope of this article.

I used sqlcl in previous articles, this time however I’d like to highlight a feature in Database Actions: creating a module from a URL.

Connect to Database Actions and log in to your account, then hit the MLE JS tile to enter the JavaScript code editor. Next, click on the download JavaScript module from the web button as per the screenshot, and enter the module URL in the dialog.

I picked the latest module from the repository. At the time of writing this translated into version 1.1.4. The module code now appears in the editor. Save the module in the database by giving it a name (like quick_sql) and hit the save button.

Call Specification

Before you can use a MLE module in SQL or PL/SQL, you need to expose it using a call specification. In short that’s a PL/SQL code unit without a body – instead, it links the code unit’s name to a function in a JavaScript module.

Creating the call specification is straightforward in Database Actions. Once the module appears in the tree view on the left-hand side of the screen, right-click on QUICK_SQL and select create -> call specification. Complete the wizard as per the following screenshot:

Generating DDL from QuickSQL

Everything is now in place to generate some DDL! The example shown in Figure 1 can be represented in the following anonymous PL/SQL block:

declare
    l_quick_sql_text    varchar2(32767);
    l_ddl               clob;
begin
    l_quick_sql_text := q'~
departments /insert 2
    name /nn
    location
    country
    employees /insert 4
        name /nn vc50
        email /lower
        cost center num
        date hired
        job vc255

view emp_v departments employees
~';

    -- use the MLE module to translate QuickSQL to DDL
    l_ddl := quicksql_to_ddl(l_quick_sql_text);

    -- provide the result
    dbms_output.put_line('converted DDL');
    dbms_output.put_line(l_ddl);
end;
/

Switch to the SQL worksheet, enter the PL/SQL block and hit the execute button.

And voila! The MLE module converts the QuickSQL shorthand notation to “proper” SQL DDL and DML commands. This is the resulting script:

-- create tables

create table departments (
    id          number generated by default on null as identity
                constraint departments_id_pk primary key,
    name        varchar2(255 char) not null,
    location    varchar2(4000 char),
    country     varchar2(4000 char)
);


create table employees (
    id               number generated by default on null as identity
                     constraint employees_id_pk primary key,
    department_id    number
                     constraint employees_department_id_fk
                     references departments,
    name             varchar2(50 char) not null,
    email            varchar2(255 char),
    cost_center      number,
    date_hired       date,
    job              varchar2(255 char)
);

-- table index
create index employees_i1 on employees (department_id);




-- triggers
create or replace trigger employees_biu
    before insert or update
    on employees
    for each row
begin
    :new.email := lower(:new.email);
end employees_biu;
/


-- create views
create or replace view emp_v as
select
    departments.id           department_id,
    departments.name         department_name,
    departments.location     location,
    departments.country      country,
    employees.id             employee_id,
    employees.name           employee_name,
    employees.email          email,
    employees.cost_center    cost_center,
    employees.date_hired     date_hired,
    employees.job            job
from
    departments,
    employees
where
    employees.department_id(+) = departments.id
/

-- load data

insert into departments (
    id,
    name,
    location,
    country
) values (
    1,
    'Sales',
    'Hujmihuf',
    'IC'
);
insert into departments (
    id,
    name,
    location,
    country
) values (
    2,
    'Sales',
    'Fagorufa',
    'IN'
);

commit;

alter table departments
modify id generated always  as identity restart start with 3;

insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    1,
    2,
    'Katherine Foster',
    'tivluvus@memuali.sa',
    30,
    sysdate-39,
    'Specialist'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    2,
    2,
    'Johanna Sanders',
    'agi@vafafden.io',
    10,
    sysdate-10,
    'Consultant'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    3,
    2,
    'Leona Flowers',
    'zimabzu@ar.bg',
    92,
    sysdate-25,
    'Evangelist'
);
insert into employees (
    id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
) values (
    4,
    1,
    'Elsie Kelley',
    'woika@zojpus.bb',
    74,
    sysdate-53,
    'Manager'
);

commit;

alter table employees
modify id generated always  as identity restart start with 5;

Note the insert statements!

Summary

This article concludes the mini-series on generating test data for Oracle Database. I could make the point that it’s as easy, if not easier, to use the features available with Oracle Database to generate sample data.