QuickSQL – Saves you time

One of the new features of APEX 5.1 definitely changed my way of creating DDL on Oracle databases. With the Quick SQL packaged application you only need to write minimal lines of markdown text to describe tables, triggers, audit tables and many other objects. This low code development concept can save you so much time, and it is even faster than the other tools available, which specialise in this job.

To use Quick SQL, you do not need to be an APEX developer or even have APEX installed on your environment. You can simply navigate to QuickSQL.oracle.com and login using your Oracle account (or sign up for one). Therefore QuickSQL is suitable for all types of Oracle Developer, from Oracle Forms to backend PLSQL Development, it’s worth investigating further. However, the best about installing the QuickSQL packaged application on your APEX workspace is that you can modify it yourself and improve the application to fit your needs.

It is also worth mentioning that the theme of this blog is not to make a tutorial on how to use the Quick SQL tool, but instead to highlight the really great features. If you want to know more about how to use it, the APEX official video below provides a helpful introduction in only 2 minutes.

Here are some of the features I like:

Uploading spreadsheets

This is useful if you need to import spreadsheet data into your database quickly. You only need to cut and paste data into the worksheet area, and the SQL conversion describes the create table and insert statements for you.

Creating Tables

It is extremely simple to learn and understand the Quick SQL language used to create tables. For example, when you type words such as name, location, address, description the tool suggests that they should be varchar2 columns.

Even the size of the column is predicted. For example:

• description = varchar2(4000)
• name = varchar2(255).

Syntax

Taking a closer look at the features available, I really liked how the following syntax is supported:

• Shorthand suffix to define column attributes
  /indexed suffix to create indexes;
  /fk  [table name] to foreign key;
  vc20 to define a varchar2(20);
  /nn for a not null column;

• Automatic addition of a primary key column called “ID” for each table. If this isn’t what you want, then you can add /pk to any other column.

• Automatic detection of foreign keys.

• Multiple levels of indentation, you can create parent, child, grandchild table structures simply by indenting.

• Automatic indexing of foreign keys.

• No need to include underscores in table or column names. i.e. If your column is named “first name” it will become first_name varchar2(255).

• Columns ending in _YN will automatically have a check constraint generated that restrict the domain of acceptable values to Y and N.

You are also able to manage the Quick SQL settings for triggers, history tables, prefixes, audit columns, schema prefixes and more. There is even an option to have QuickSQL generate a PL/SQL table API package, all without writing any extra code.

APEX Enable

If you configure the settings to automatically create audit columns and switch APEX Enable option to Yes, the audit log trigger will be generated getting the user from the APEX session, not the database user.

After you have designed your schema and are ready; you can copy and paste the generated code to SQL Developer or run it on the Oracle APEX SQL Scripts area. You can save your script within the Quick SQL Application or on your repository.

You don’t have to worry about saving your syntax – at the end of the script, there is a comment with your Quick SQL code and the settings that were applied when the script was generated. For example:

— Generated by Quick SQL Thursday, August 17, 2017, 15:33:06
/*
departments /insert 4

name /pk
Location
Description

employees /insert 14

name /nn vc50
email /lower
cost center num
date hired
Job

# settings = { API: true, auditCols: true, APEX: true }
*/

Summary

I have mentioned some great features, but of course, there are some improvements that could be made in the future versions of the application. For example, running the created script directly from Quick SQL or saving the settings by an individual user. Don’t expect to be able to use the Quick SQL tool to replace software such as Oracle SQL Data modeler, but what it will do is – save you time!

Author: Rodrigo Mesquita 

Job Title: Oracle APEX Development Consultant

Bio: Rodrigo is an Oracle ACE and APEX Developer Expert certified by Oracle and is an experienced software engineer with emphasis in analysis, design and development of bespoke Oracle applications utilising development tools such as PL/SQL, APEX and Forms for a variety of international businesses. Rodrigo speaks regularly at Oracle community events and enjoys creating APEX plugins and writing blogs.