This year again the APEX connect conference spans over three days with mixed topics around APEX, like JavaScript, PL/SQL and much more.
After the welcome speech and the very funny and interesting Keynote about “PL/SQL: A Career Built On Top Of PL/SQL – 25 years of Coding, Sharing, and Learning” by Martin Widlake, I decided to attend presentations on following topics:
– Oracle Text – A Powerful Tool for Oracle APEX Developers
– Make It Pretty! MIP your APEX application!
– REST Services without APEX – but with APEX_EXEC
– Microservices with APEX
– SQL Magic!
– The UX of forms

PL/SQL: A Career Built On Top Of PL/SQL – 25 years of Coding, Sharing, and Learning:

Martin Widlake shared the story of 25 years development on Oracle from version 6 to the newest 19c.
The most important to retain from his professional journey is that “Good developers are made by other developers” and “Everything you learn will have some return sometime in the future”. That means sharing is the key, keep yourself curious and never stop learning, even things that are not yet obviously useful.

Oracle Text – A Powerful Tool for Oracle APEX Developers

That feature is embedded as a standard in Oracle databases since 1997 when it was named Car Text. In 1999 it became Intermedia Text and finally Oracle Text in 2001. It allows to index text based fields of the database as well as files in BLOBs, allowing much faster and easier search of text patterns (words, sentences, …). We went thru aspects like syntax, fuzzy search, snippets and lexer.
As search combinations require usage of specific operators and delimiters, which are cumbersome for end users, there is a useful package written by Roger Ford that allows to convert simple “Google” like requests into the right format for Oracle Text: PARSER download
His blog will provide nice information about it and Oracle documentation provides all details to the usage of Oracle Text.
You can find further information on following Blog:
Oracle text at a glance

Make It Pretty! MIP your APEX application!

The business logic is the heart of the application but the UI is its face and what users will judge first.
There are some rules which to Make It Pretty (MIP).
First of all it needs to fullfil user needs by either:
– stick to company brand rules
– stick to company webpage design
– stick to user wishes (can be drafted with template monster
Technical and non-technical aspects need to be considered.
Following Design rules help to improve the UI:
– Be consistent
– Make it intuitive
– Be responsive (give feedback to users)
– Keep it simple (not crowded)
– Fonts: max 2 per page, 16px rule (verify on fontpair.co)
– Color rules (verify on contrast checker)
– Have imagination
– Know your APEX universal theme

REST Services without APEX – but with APEX_EXEC

APEX is based on metadata to store definitions and PL/SQL packages support the engine.
That means APEX metadata support can be used outside the APEX application in any PL/SQL code.
One particular APEX PL/SQL package is APEX_EXEC introduced in APEX 18.1. It allows to abstract the data format (XML, json, …) in websources in order to be able to use data as it would come from any local table. It also takes care of pagination from web services to make the data retrieval transparent. But in order to make use of that package an APEX session must first be created to initiate the needed metadata. Fortunately this is made easy since APEX 18.1 with procedure create_session from the apex_session package.
The next version of APEX 19.2 might integrate websource modules with interactive grid.

Microservices with APEX

APEX can be compared to microservices by looking at the characteristics:
– Scalability
– Fail-safe
– Maintainable
– Technology independent
– Database independent
– Small
And it mostly matches!
But APEX also overrules the microservices drawbacks:
– Complex architecture
– Complex testing
– Migration efforts
– Complex development
To have a behavior close to microservices, APEX applications have to make use of web services for the data management and the interfacing with any kind of other services. This allows to clearly separate data management and applications. ORDS allows to enable REST at schema and also object level within APEX. Caching also needs to be considered based data change frequency to lower the lag time of data handling.

SQL Magic!

Since Oracle 12c the database provides the json data guide which allows easy json data manipulation like any standard table data. This comes also with new views like user_json_data_guide.
Oracle 11g introduced Invisible columns that hides columns from table description as well as standard “select *” but not specific select statements. This can be used to deprecate columns or add new columns without breaking existing applications with “select *”. Even though “select *” should be avoided in applications.
Oracle 12c also introduced polymorphic table function that can be used with pipelined tables to create views allowing to pivot and transpose tables whatever number of columns and rows they have.
All those features are very useful and should be used further.

The UX of forms

User eXperience (UX) rules to be applied in forms go beyond APEX. The aim to:
– Reduce cognitive load
– Prevent errors
– Make it user friendly
The rules are following:
– One thing per page
– Put field labels above rather than side to
– Replace small dropdowns by radio buttons
– Use Interactive data lists for long dropdowns
– For login pages, be specific on username type (name, e-mail) and password definition rules
– Avoid * for required fields but rather flag Optional fileds
– Adapt field size on expected data length
– Use smart default values
– Use entry masks
– Use date picker
– Define check before you start pattern to guide users and reduce form length
All that will improve UX.