Faster APEX get the browser to work for you

Want to do some data-intensive heavy lifting? Where should we do it? In the Database of course. It is purpose-built to store and retrieve data in the most optimal way using algorithms refined over decades.

Even before an APEX page renders, the DB not only serves up the page, but also fetches your data for screen population – it is that intensive data crunching that the DB excels in. When we submit the page, it is the same story. APEX runs through all those PL/SQL server side conditions, validations and processes. PL/SQL is the language used throughout.

As an APEX developer, we have several opportunities to call our PL/SQL…

Take for example, a Secretary using an APEX screen to find the Doctor’s next available appointment date. The Doctor’s whole agenda is stored in the Database – so naturally we need to contact the database to return us the value. Let us consider two approaches:

Submit the Page

The APEX screen could have a ‘Find Next Appointment’ button and could submit the page, run the server-side function to fetch and return the date value. However, this would cause the screen to redraw, destroying client state (losing scroll position, etc.) and finally displaying the value… it is not a great experience.

Using a Dynamic Action or AJAX Call-back

On click of the ‘Find Next Appointment’ button, a click event calling a Set Value true action could invoke a PL/SQL expression to call a PL/SQL function. This is a better UX as the Secretary briefly sees a loading spinner next to the appointment date, within a second, the value is calculated and the screen populated without the page being submit and subsequently redrawn. If we turn debug on, we can see all these network trips to the database to fetch the value.

Bearing in mind that the user wants a near-instant result, good UX and given the fact that only the database can determine this particular value, I am satisfied the Dynamic Action/AJAX approach is the correct one.

However, what if the scenario is not data-intensive at all? I.e. it is not number crunching and in fact, we do not need the overhead of calling the DB at all – what if we could put the browser to work on some more lightweight calculations. In these situations, it can be even faster, no loading icon, no spinner and super-fast calculations.

An example is initcap… As we know, it is easy to initialise each word in a string in PL/SQL. However, we want to do this super-fast in the browser without touching the Database…

INITCAP('learning to fly');
// => 'Learning To Fly'

All we need to do is to find an equivalent call in JavaScript. With no DB, JavaScript is our go-to language. A simple google search “initcap for JavaScript” can reveal some scary answers when it is so simple (and tempting) to use PL/SQL. I will demonstrate how this call be achieved in a much simpler PL/SQL-like call

v.titleCase('learning to fly');
// => 'Learning To Fly'

Without a Database, the following list provides some areas where it is quicker to call JavaScript rather than PL/SQL.

• String manipulation: substr, instr, like, upper, lower, etc.
• Date Manipulation: Add_months, subtract dates, day of the week, etc.
• Currency: Conversions, cents, Currency Symbols, formatting, etc.

What’s more, all these examples combine with client side validations at page and item level, including the Interactive Grid!

I will describe these three in detail; however, you might want to follow along in the handy demonstration application I have made.

https://apex.oracle.com/pls/apex/f?p=27657

String Manipulation

For String Manipulation, I like to use Voca.js, which is pretty-much a total coverage for String Manipulations. In my example, I have a Key Release Dynamic Action, which updates the text block as you type therefore demonstrating the speed of this library. Using PL/SQL, it would take a brief amount of time to refresh the text block.

A comprehensive amount of functions includes:

• Case: Similar to like, upper, lower, etc.
• Chop: Return parts of a string, like substr, etc.
• Counting: Words, Chars, etc.
• Escaping: Escaping html, etc.
• Formatting: Similar to the %s substitutions in APEX
• Index: Similar to instr, etc.
• Manipulate: Similar to Replace, rpad, trim, etc.
• Query: provides testing for number/alpha/lower/blank, etc.
• Split: Similar to APEX_STRING
• Strip: Similar to apex.util.strip
• Util: use voca. Instead of v.

Date Manipulation

The ability to add/subtract dates in PL/SQL is amazing, if I wanted a fast alternative in the browser, I look no further than Moment.JS.

In my example application, I use moment.js to add a day to a date; however, I could add months, years, etc. and of course, this accommodates leap years.

Moment.js is quick to learn and provides a whole array of functions including:

• Formatting using similar to PL/SQL style format masks
• Finding Relative time, e.g. 5 weeks from now
• Calendar Time, e.g. Yesterday at 3:45 PM
• Local Support: e.g. USA MM/DD/YYYY date style versus practically everywhere else in the world

It also has date and time manipulation being able to apply various durations to a current time.

In my example application, I use it to validate a string to check if it is a valid date. Moment.js is a very, very handy JS library

Currency

Currency can be a little complicated in determining the correct currency symbol, whether the currency has sub-units like USD but not like JPN.

Dinero.js can provide some assistance with a variety of functions for the manipulation and formatting of monetary amounts.

In my example, I use a couple of features, one of which is a handy currency converter. What Dinero does is it provides a method of calling a third party currency conversion API (I just used the first one I googled) and returns it as a promise used to obtain the amount, currency symbol, etc. The second function I use is to format the units with a symbol.

In conclusion, I hope you find these interesting JavaScript libraries. These generic ones provide some PL/SQL-like functionality at a browser level, which are not too difficult to learn how to use.

It is always wise to be aware when trips to the database are required and when to use alternatives.

Author: Matt Mulvaney

Job Title: Senior Oracle APEX Development Consultant

Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.