You might have seen some demos of Live SQL from Oracle, however you might not know that there is a packaged application in APEX 5.1 called Quick SQL with very similar (if not identical) functionality that you can use locally.
Firstly – why the name difference between Live SQL and Quick SQL? Answer – I have no idea, perhaps to seperate the two applications in terms of one is a packaged application (Quick SQL) whilst the other (Live SQL) is a hosted application (by Oracle) that can be used by anyone with a web-browser regardless of if they have a local APEX installation or not. Also, since Live SQL is hosted publically and Quick SQL is shipped with APEX, it’s possible that Live SQL contains some newer features / bug fixes etc that may or may not be included in an updated packaged application in the future when APEX itself is patched (or 5.2 comes out).
For the rest of this post I’m going to refer to Quick SQL, however as far as I’m aware most of the functionality can equally be used on the public Live SQL site.
So what is Quick SQL? Well if you’ve spent any time with Oracle (or indeed any other RDBMS), you’ll have lost count of the number of times you’ve hand-typed DDL statements like –
create table foo ( id number not null, data varchar2(20) not null, constraint foo_pk primary key (id) enable );
If you’ve used Oracle for a while, you might have some pre-defined templates you use to make this task less manual, however it is still a pretty manual task (unless you obviously use something like SQL Developer Data Modeller).
What Quick SQL does is allow you to write out SQL is shorthand notation and it transforms that shorthand into the full syntax.
So, let’s install the Quick SQL Packaged Application
The list of packaged applications can be quite long, so I just filtered for ‘sql’
Follow the wizard steps and you should have it installed in under a minute, once you run the application and login using your workspace credentials it will ask you a couple of questions about Access Control (I just went with the defaults).
After hitting “Complete Setup”, you get to the main application, which at first glance might look a bit underwhelming.
That’s it, just two panes, one pane you can type into, whilst the other pain is for output. So let’s go ahead and type this into the left hand pane –
foo id data
type it exactly like that, as soon as you hit return at the end (or press the ‘Generate’ button) you should see the right hand pane become populated with output.
in my case, the output was
-- create tables create table FOO ( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY constraint FOO_ID_PK primary key, DATA VARCHAR2(4000) ) ; -- load data -- Generated by Quick SQL Tuesday June 06, 2017 10:06:13 /* foo id data # settings = { } */
You’ll notice a few things here –
• The ID column definition has some Oracle 12c syntax (the GENERATED BY DEFAULT ON NULL AS IDENTITY piece)
• It includes a comment on the input that was used to generate this output
• The DATA column was defined as a VARCHAR2(4000) by default
Let’s say instead of a VARCHAR2(4000) column, the data column should be a VARCHAR2(20) and also NOT NULL, just type this into the left hand pane –
foo id data vc20 /nn
which should give the following output (I’ve omitted the comments section for brevity)
create table FOO ( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY constraint FOO_ID_PK primary key, DATA VARCHAR2(20) not null ) ;
so the VC20 was interpretted as VARCHAR2(20) and the /nn is shorthand for “NOT NULL”, we can also use any length datatype, e.g. VC5, VC10 etc.
Ok, so that’s not the worlds most impressive demo for saving time, but lets look at another use-case. Let’s say a typical Master Detail scenario, try typing this into the left hand pane –
dept deptno /nn dname /nn emp empno /nn deptno /nn view emps_vw dept emp
this should produce the text
-- create tables create table DEPT ( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY constraint DEPT_ID_PK primary key, DEPTNO VARCHAR2(4000) not null, DNAME VARCHAR2(255) not null ) ; create table EMP ( ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY constraint EMP_ID_PK primary key, DEPT_ID NUMBER constraint EMP_DEPT_ID_FK references DEPT on delete cascade, EMPNO VARCHAR2(4000) not null, DEPTNO VARCHAR2(4000) not null ) ; -- indexes create index EMP_i1 on EMP (DEPT_ID); -- create views create or replace view EMPS_VW as select DEPT.ID DEPT_ID, DEPT.DEPTNO DEPT_DEPTNO, DEPT.DNAME DNAME, EMP.ID EMP_ID, EMP.EMPNO EMPNO, EMP.DEPTNO EMP_DEPTNO from DEPT, EMP where EMP.DEPT_ID(+) = DEPT.ID / -- load data -- Generated by Quick SQL Tuesday June 06, 2017 10:15:57 /* dept deptno /nn dname /nn emp empno /nn deptno /nn view emps_vw dept emp # settings = { } */
notice here that simply by indentation, Quick SQL has figured out it needs to generate a foreign key relationship between the two tables.
I have also created a view joining the two tables, in only 7 lines of Quick SQL markup I’ve output close to 40 lines of DDL, saving myself a huge amount of typing.
There are a huge number of Table level directives you can use, for example –
- /api Generate PL/SQL package API to query, insert, update, and delete data within a table.
- /audit Adds Oracle auditing, by default audit all on table name.
- /auditcols, /audit cols, /audit columns Automatically adds an updated, updated_by, inserted, and inserted_by columns and the trigger logic to populate.
- /compress, /compressed Table will be created compressed.
- /history Generate code to log changes into a history table for this specific table.
- /insert NN Generate NN SQL INSERT statement(s) with random data.
- /select Generate SQL SELECT statement.
- /rest Generate REST enablement of the table using Oracle REST Data Services (ORDS).
- /uncomment, /uncommented If Yes will cause generated SELECT or INSERT SQL statements to not be commented. Reference /insert and /select.
and also column level directives –
- /idx, /index, /indexed Will create a non unique index on the associated column.
- /unique Creates a unique constraint.
- /check Creates a check constraint with with comma or white space delimited values e.g. /cc Y N
- /constant When generating random data for a table set this column to a constant value, quotes will be added if needed.
- /values Comma separated list of values to use when generating random data. For example /values 1, 2, 3, 4 or /values Yes, No.
- /upper Forces column values to upper case.• /lower Forces column values to lower case.
- /nn, /not null Adds a not null constraint on the column.
- /between Adds a between check constraint on the column.
- /hidden, /invisible Hidden columns are not displayed using select * from table.
- /references, /reference, /fk Foreign key references e.g. /references table_name. You can also reference tables that are not part of your model.
- /pk Identifies single column as table primary key. It is recommended not manually specify primary keys and let this app create them for you.
So you can a huge amount of control over the output DDL.
However, remember earlier when I said the DDL used some 12c syntax – what if you’re still on 11g? Well you can control that via the settings, either by including –
#db: 11g
in your markup in the left pane, or use the settings button to change it. It’s worth also looking in the Syntax and Examples section as it contains (funnily enough) lots of syntax and examples that you can learn from.