Rapid generation of Oracle DDL scripts for Tables, PL/SQL APIs, Sample Data

Yesterday, at the Oracle ACE Directors Product Briefing, I received a gift. It is called QuickSQL. And it is a free online service that generates SQL DDL and DML scripts. The gift in this case was the knowledge about this service – I was not aware of it.

Go to http://quicksql.oracle.com. Try it out. If you have a need for a quick set of database tables for a demo or a prototype – with sample data, constraints, audit columns, change history, an ORDS based REST API – you can leverage QuickSQL to generate all the required DDL and DML scripts from a just a few lines of YAML and some declarative settings, specifying what you want to have generated.

For people like me who keep forgetting DDL syntax, this is really convenient as well.

And of course this service brings back memories of Oracle Designer and the Database Design Transformer and DDL Generator – with their abilities to take declarative definitions and turn them into concrete and enriched code. And the TAPI triggers and packages.

Typing this code snippet:

is enough to get you 400 lines of DDL and DML code, generating two tables with several constraints, a view, two PL/SQL packages, insert and update triggers, and DML statements to create valid sample data.

It is so easy to get started with, you do not really need a blog article to get you started. Perhaps a few examples to convince you.

The indented definition shown above is entered into the worksheet in QuickSQL:

Whenever a new line is entered in the worksheet – or the generate SQL button is pressed – the generated code on the right hand side is refreshed.

Let’s look at a few of the things QuickSQL generates for us.

Two create table statements are created:

Some of the noteworthy elements:

• audit columns in DEPARTMENTS – because of the /auditcols directive. Triggers are created as well to set the values for these columns
• the foreign key column DEPARTMENT_ID to reference the master DEPARTMENT from the child Employee; because the employees table is defined indented under the departments table, it is interpreted as a child
• the foreign key constraint itself, tying the child table to the master
• a check constraint from the /check directive

Here is the DML trigger for insert or update of the departments table:

The API that is generated for the departments table because of the /api directive – bringing back memories of the TAPI package generated by Oracle Designer:

Here is a little piece of the API package body:

The view specification – view emp_v departments employees – resulted in this create view statement:

This brings me to a little improvement suggestion: I believe the ANSI SQL JOIN syntax is superior to this WHERE clause based syntax – if for no other reason than for readability and a clear separation between the filtering logic in the view and the join instructions. I would suggest QuickSQL generates the following join instruction:

from departments

left outer join

employees

on (employees.department_id = departments.id)

Finally an example of the generated DML statements to load sample data into the tables:

The /values instruction for the country column (/value EN, NL) is used to randomly select values for country from that set of values. For other columns, QuickSQL has a collection of data values or value generators to create valid, somewhat meaningful and largely random data.

The lists of table and column directives that are understood by QuickSQL are shown here:

As you can see, there are many things that QuickSQL can do for you.

QuickSQL also comes with a number of predefined sample sets of tables – to make it event easier to quickly generate a prototype database schema:

For each of these samples, an ERD is available to quickly offer an overview:

Note: you can save your own models – for later reuse, fine tuning – and share those models with others – by sharing the generated URL to the saved model.

I am quite happy with this little gift. Great work from Oracle.

Resources

Documentation on QuickSQL: https://apex.oracle.com/pls/apex/f?p=8675309:HELP:108937483794028::NO::: 

This does not run the SQL, you can run SQL on livesql.oracle.com or on an Oracle database cloud service or on a local Oracle database.

A quick demo video is shown here: https://youtu.be/BCs2jWkdVFg