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

2

imageYesterday, 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.

image

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:

image

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:

image

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:

image

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:

image

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:

image

Here is a little piece of the API package body:

image

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

image

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:

SNAGHTML81f0e86

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:

image

image

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:

image

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

image

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

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.