APEX Connect 2020 – Day 1

This year the APEX connect conference goes virtual online, like all other major IT events, due to the pandemic. Unfortunately it spans only over two days with mixed topics around APEX, like JavaScript, PL/SQL and much more. After the welcome speech and the very interesting Keynote about “APEX 20.1 and beyond: News from APEX Development” by Carsten Czarski, I decided to attend presentations on following topics:
– The Basics of Deep Learning
– “Make it faster”: Myths about SQL performance
– Using RESTful Services and Remote SQL
– The Ultimate Guide to APEX Plug-ins
– Game of Fraud Detection with SQL and Machine Learning

APEX 20.1 and beyond: News from APEX Development

Carsten Czarski from the APEX development team shared about the evolution of the latest APEX releases up to 20.1 released on April 23rd.
Since APEX 18.1 there are 2 release per year. There are no major nor minor releases, all are managed at the same level.
Beside those releases bundle PSE to fix critical issues are provided.
From the recent features a couple have retained my attention:
– Faceted search
– Wider integration of Oracle TEXT
– Application backups
– Session timeout warnings
– New URL
And more to come with next releases like:
– Native PDF export
– New download formats
– External data sources
A lot to test and enjoy!

The Basics of Deep Learning

Artificial Intelligence (AI) is now part of our life mostly without noticing it. Machine learning (ML) is part of AI and Deap Learning (DL) a specific sub-part of ML.
ML is used in different sectors and used for example in:

• SPAM filters
• Data Analytics
• Medical Diagnosis
• Image recognition

and much more…
DL is integrating automated feature extraction which makes it suited for:

• Natural Language processing
• Speech recognition
• Text to Speech
• Machine translation
• Referencing to Text

You can find some example of text generator based on DL with Talk to transformer
It is also heavily used in visual recognition (feature based recognition). ML is dependent on the datasets and preset models used so it’s key to have a large set of data to cover a wide range of possibilities. DL has made a big step forward with Convolutional Neural Networks (by Yann Lecun).
DL is based on complex mathematical models in Neural Networks at different levels, which use activation functions, model design, hyper parameters, backpropagation, loss functions, optimzer.
You can learn how it is implemented in image recognition at pyimagesearch.com
Another nice example of DL with reinforcement learning is AI learns to park

“Make it faster”: Myths about SQL performance

Performance of the Database is a hot topic when it comes to data centric application development like with APEX.
The pillars of DB performance are following:
– Performance planning
– Instance tuning
– SQL tuning
To be efficient performance must be considered at every stage of a project.
Recurring statement is: “Index is GOOD, full table scan is BAD”
But when is index better than full table scan? As a rule of thumb you can consider when selectivity is less than 5%
To improve the performance there are also options like:
– KIWI (Kill It With Iron) where more hardware should solve the performance issue
– Hints where you cut branches of the optimizer decision tree to force its choice (which is always the less expensive plan)
Unfortunately there is no golden hint able to improve performance whenever it’s used

Using RESTful Services and Remote SQL

REST web services are based on URI returning different types of data like HTML, XML, CSV or JSON.
Those web services are based on request methods:

• POST to insert data
• PUT to update/replace data
• GET to read data
• DELETE to DELETE data
• PATCH to update/modify data

The integration of web services in APEX allows to make use of data outside of the Oracle database and connect to services like:
– Jira
– GitHub
– Online Accounting Services
– Google services
– …
Web services module on ORDS instance provides extensions on top of normal REST which support APEX out of the box but also enables Remote SQL.
Thanks to that, SQL statement can be sent over REST to the Oracle Database and executed remotely returning the data formatted as per REST standards.

The Ultimate Guide to APEX Plug-ins

Even though APEX plug-ins are not trivial to build they have benefits like:
– Introduction of new functionality
– Modularity
– Reusability
which makes them very interesting.
There are already a lot of Plug-ins available which can be found on apex.world or on professional providers like FOEX
What is important to look at with plug-ins are support, quality, security and updates.
The main elements of a plug-in are:
– Name
– Type
– Callbacks
– Standard attributes
– Custom attributes
– Files (CSS, JS, …)
– Events
– Information
– Help Text
Plug-ins are also a way to provide tools to improve the APEX developer experience like APEX Nitro or APEX Builder extension by FOS

Game of Fraud Detection with SQL and Machine Learning

With the example of some banking fraud, the investigation method based on deterministic SQL was compared to the method based on probabilistic ML.
Even though results were close on statistics Supervised Machine Learning (looking for patterns to identify the solutions) was giving more accurate ones. In fact, the combination of both methods was giving even better results.
The challenge is to gain acceptance from the business on results produced using help of ML as they are not based on fully explainable rules.
The Oracle database is embedding ML for free with specific package like DBMS_DATA_MINING for several years now.

The day ended with the most awaited session: Virtual beer!