Friday 29 April 2016

APEX Survey Results: When did you start learning APEX?

Early last year I put the call out to #orclapex developers, asking them to fill out some questions in survey (using a packaged APEX application, of course).

The results helped contribute to a presentation I did Kscope15. Thank you to all those who responded. I thought I'd finally post some results, see if it can elicit further discussion, even of a casual nature.

Some of you asked me if I could post these results, so thanks for your patience and stay tuned on the 2015 Survey label in this blog. I'll aim for every Friday.

Now it must be said: this is an internet survey. It's not randomised and it's no doubt highly skewed towards the developer population that either read my blog or use Twitter. And even then it's only those who chose to spend the 10 minutes responding. Based on the free text responses I doubt it was gamed (unlike other polls), no surprises there.

When I closed the survey I had 192 responses, of course far shorter than the number of APEX developers out there, quietly coding away. Everyone, feel free to add your own thoughts to some of these results. It could be insightful and fun!

Q1: When did you start using APEX?

I was surprised there are so many respondents prior to 2007. Maybe this is a reflection of the demographic that chose to respond?

I started learning in 2008, with some experience with mod_plsql and heavy with Forms experience. I found it tough learning session state, and ultimately designing page flow in a web environment.

As others have mentioned, there have been a few features that I would have loved to have found earlier. My real kicker was Region Display Selectors.

What I'd like to know is, what made you start learning APEX? For me, I realised Forms was dying.

Thursday 28 April 2016

On CSS/jQuery Selector Performance

My post describing the use of a simple selector identifying page spinners was originally going to be about performance, then I learned something I found very interesting.

I likened what I learned to Tom Kyte's essay (Asktom->Resources->Presentations->FalseKnowledge.htm) on Correlation vs Causation. The essence was that things change over time, and we can't always trust authorities on the topic, and we must always test in our own environments. This aligns with skepticism in general, and here we are applying it to web development.

The following forum post described the subsequent code
I was going to suggest simply prefixing the provide class with the relevant HTML tag.
But I discovered a thing or two while drafting the post to explain my reasoning.

A while ago I incorporated much of my jQuery performance knowledge from this article
It was concise and it made sense. It also harmonised with other information I found on the topic. However, the post is now 7 years old and is probably worth re-testing it's assertions.

Consider the provided solution using .u-Processing. The period prefix means it's looking for a component on the page with the class u-Processing, as per in the definition of the spinner.
<span class="u-Processing" ...

If the component had an id, you could use the # prefix to reference the ID, much like using an unique* index. (*in the web world, the an ID is not guaranteed unique, but should be in best practice, particularly to avoid logic errors.)

As I had previously interpreted, the problem was that it needs to look at every single component on the page. If the selector was prefixed with the span, this reduces the workload on the browser by filtering the DOM tree to just the span tags.
And of those span tags, which have the provided class.

The reasoning seems sound, right? Part of it relates to native JavaScript methods available for locating content. Now if only there was a way to test these assertions...

Well on you can set up such test cases and run them against different browsers. Similar websites are available for SQL ( and JavaScript test cases (

[Note: was alive when I started drafting this post in March 2016, but has been down for a few weeks in April? ]

In regard to browser performance, someone had already prepared an example based on the performance rules described.
And the results on my laptop in 2016 painted a somewhat different picture.

Chrome / Linux / Laptop

And true to Tom's essay, when I tried the same tests across different browsers on a different platform, the results are widespread.

But like anything it really depends on the page you're running it on. I did find the test case a little contrived since it was based on a very small portion of HTML, so I attempted the same type of test using HTML extracted from a typical page generated from APEX to see how many entries it's actually sifting through.

To give you a sense of scale, I ran some jQuery statements in the browser console of an even bigger page, one including a region selector so plenty of regions & reports.

This returns the number of elements on the page you could identity with a jQuery selector. In my case it returned about 3000.

There are only 148 spans.

And only one spinner.

Considering the amount of operations/second the browsers are capable of, these numbers may seem small, but everything adds up. It's worth keeping up with better practices to trim performance where ever possible.

So the lesson I read out of this is if your developing APEX applications and you have basic jQuery mechanics, or even just use jQuery selectors in your dynamic actions, then perhaps stick with a readable, logical option that you're comfortable with.

Either that or I still have a lot to learn about jQuery performance ;p

If anyone has good, current resource recommendations on the topic, I'd be happy to hear. Particularly if it relates to use in the #orclapex environment.

Thursday 21 April 2016

Improving PL/SQL performance in APEX

One of the simplest tuning techniques to encapsulate PL/SQL used in APEX within packages, minimising the size of anonymous blocks. This applies to any PL/SQL within the page, including computations, processes, plugins, dynamic actions, validations, shortcuts and dynamic PL/SQL regions.

This change can make a big impact in the execution time of PL/SQL as it's processed at compile time instead of being parsed and compiled at runtime as dynamic PL/SQL.

Plug-ins can be wonderful black boxes and consumers may not care how it works or looks under the hood. I have a few other views on the use plug-ins that I'll share on day, but this post looks at a way you can always improve it's performance by shifting the supplied code into a PL/SQL package.

Example of freshly imported plug-in in APEX 5.0

You (re)move the code that's supplied and defined as a procedure in your own package, perhaps apx_plugins.

Then modify the "Render function name" to prefix the call, ie: apx_plugins.render_save_before_exit

I gathered performance data on two plugins using compiled vs dynamic code. The following graph shows the relative difference in rendering time between the two using 11g, where the red dots display how many data points I used.

APEX Plug-in Performance - Dynamic vs Compiled PL/SQL
This particular information was garnered from debug log execution time.
select message, avg(execution_time)
from apex_debug_messages
where message like '%sparkline%'
group by message;

Update: I have used Oracle's instrumentation to get better figures

The recommendation to move this code can be found in (At least) the help text for plugin PL/SQL and documentation.

Moving code to packages will also overcome other limitations such as how much code fits in the attribute, though I think this boundary should only be reached in extreme circumstances. The Excel2Collection plug-in is an example. The author had to compress the PL/SQL code to make it and be packaged as a plug-in.

Encapsulating PL/SQL also provides more opportunity for re-use, reducing chance of defects and
allowing more granular version control.

I would like to think Oracle Forms developers have been doing this for years, in part to help minimise network traffic.

If I'm not mistaken, the same concepts can be applied to complex SQL, moving them into database views.

Daniel Hochleitner has also blogged on this topic.

Friday 15 April 2016

Training: jQuery and Dynamic Actions in APEX

Sage Computing Services are happy to announce a new 2 day course for Oracle APEX developers.

jQuery and Dynamic Actions in APEX

This course is designed for APEX developers who know their way around the APEX builder but want to build more interactive & user friendly applications that are also suitable for touch devices.

Upon completion attendees will have a great understanding of the communication between the browser and the database, using dynamic actions effectively and including jQuery suitable for database developers.


Attendees are expected to be reasonably familiar within the APEX development environment.
Experience with APEX 5.0 is an advantage but not essential.

All but two exercises are independent of APEX version, so while the course will be taught on 5.0, you can transfer your new skills to 4.x.

Understanding of CSS & jQuery is not required. Attendees will be taught how this syntax is used from a database developer's perspective.

Register Interest

We will schedule a course in the Perth CBD prior to financial year end (June 2016), date to be announced. More course details are available here.

Please contact me (Scott at or Tweet/PM me @swesley_perth to register your interest.

If you're based in another Australian capital city and have a group of 4 or more, we can come to you. If you're in the Asia & Pacific region, I'm sure we could work something out with any of the courses from our catalogue. I'd love to make new friends in our region.


Tuesday 5 April 2016

On asking questions on APEX forums

I'm a regular contributor to the Oracle APEX forum and something I'd like to note is the amount of questions that possibly wouldn't need to be asked if one had consulted the help of the attribute in question.

For example, here someone asked about referencing page values in the Email built-in process.

I managed to interpret the question two different ways on reading it twice, so I would also plead with people asking questions to supply code. If you've ever asked a question at AskTom you might appreciate the value of a good test case in providing context, and the ability for someone willing to help out to quickly investigate the problem.

APEX questions can be a little harder since often you can't just provide a set of SQL commands. This is where supplying an image can truly paint a thousand words. If I had even just the middle section of this screenshot from APEX, I would have known exactly how to answer the question.

APEX 5 Page Designer Attribute Help

The same type of help is also available in 4.x by clicking the attribute label.
APEX 4.x Attribute Help
And if you're still using the Component View in APEX 5 it will be in the same place, but I recommend you give the Page Designer a go - I find it far more productive with far less clicks and jumping between pages.

So next time you have trouble figuring something out, check out the inline help.

While the help descriptions have improved in APEX 5.0, the feedback option is still "Coming soon", so perhaps this will be made available during the 5.1 release.

At least we had another outcome of this question, Jeff learned about a type of process he wasn't familiar with ;p.

I have a presentation in mind on this topic, but in the meantime, here is Exhibit A on asking questions