APEX Connect 2016 – Day 1 – SQL and PL/SQL

This year the APEX connect conference spans over three days with the first day dedicated to SQL and PL/SQL which are the basement of APEX and its close link to the Database.
After the Keynote about “Six months of ask Tom” by Chris Saxon who is filling in for Tom Kyte on the famous “Ask Tom” website I decided
to attend presentations on following topics:
– A Primer on Service Workers
– Managing the changes in database structures in agile project with Oracle SQL Developer Data Modeler
– Oracle 12c for Developer
– Oracle SQL more functionality for more Performance
– SQL and PL/SQL hidden features
– Code Quality in PL/SQL

Ask Tom:
What was highlighted by Chris Saxon is that the key to get proper support, is to ask well defined questions.
Most of the time they are lacking details and have a misleading subject.
The keys to ask a good question and increase chances to have a pertinent answer are following:
1. Provide a subject which summarizes the question (not something generic like ‘Oracle’ or ‘DB error’)
2. Provide a test case (table, data, code, error details)
3. Provide test data as insert statement
Ideally you can use Oracle Live SQL (https://livesql.oracle.com) to test your code and provide the link to the “Ask Tom” team.
Don’t forget: making their work easier, gives them more time to answer more question. So if you spend a little bit more time to submit a proper question, it will benefit to the whole community.
Also, easy questions can most of the time find an answer with our friend: Google 🙂

Service Workers:
What are service workers? Some would say, they are the future of the internet.
Basically they are Java scripts running between your web browser and the network.
So it’s a programmable network proxy, allowing you to control how network requests are handled.
Service workers have following steps in their lifecycle:
– Install
– Activate
– Wait (Idle)
– Fetch / transmit
They only work with HTTPS.
To learn more about service workers I suggest you to visit following blog:
http://www.html5rocks.com/en/tutorials/service-worker/introduction/?redirect_from_locale=de
Like every new component, they are not supported by all web browser versions. You can check compatibility from following web page:
http://caniuse.com/#feat=serviceworkers

Agile changes in database structures:
Oracle provides a very interesting tool for data modeling, for free: SQL Developer Data Modeler
(http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html)
So don’t hesitate to download it!
It a very powerful tool which allows you to manage versions of your data model by integrating Subversion.
That why it’s highly recommended to flag your data model with the same release number than the application code in order to keep track.
You can do following and more in order to manage you data model and stick to the changes required by the developers:
– Compare data models with different versions
– Compare data model with DB data dictionary
– Compare data model with DDL scripts
– Reverse engineer data model from DB data dictionary
– Reverse engineer data model from DDL scripts
– As a result of those comparisons, generate reports and align as required one or the other compared items
– Generate DDL scripts (which can be tuned by setting the preferences)
– Create design rules and transformation scripts
There is no excuse to live with a wrong data model.

Oracle 12c for Developers:
There are quite some new features in Oracle 12c which make the life of developers easier:
– Extended VARCHAR sizing
– Approximate count distinct
– Pattern matching
– White Lists
– Temporal validity
– JSON
– Data redaction
– Identity columns
I can only recommend you to visit the Oracle documentation to get the details of the use of those nice features.
https://docs.oracle.com/database/121/NEWFT/toc.htm

More SQL functionality for more performance:
There are some SQL features which allow you to lower your development efforts and improve the performance like:
– Invisible columns
– Archiving
– Hints/directives
– Use of result cache
– External table
– Use clause
– Row limiting
You are advised to visit the following blogs (8 parts in total):
http://db-oriented.com/2015/08/22/write-less-with-more-part-1/

SQL and PL/SQL hidden features:
I know you will be, but there are no hidden SQL nor PL/SQL features in the Oracle database.
All features are documented.
But some like the following earn to be better know and used like:
– DEFAULT on NULL
– MERGE
– DBMS_LOGGING package
– MODEL
– LINGUISTIC settings
– DBMS_ASSERT package (Prevent SQL injection)
– Application context for logging
The best advice is to have a look at the new features of every new release / patch of the Oracle database to know about what it brings and when it was available.