Friday, 6 May 2016

APEX Survey Results: Which versions have you experienced?

Following up from the results on when you started learning APEX, here is question 2 from my 2015 survey.

Note that questions such as this offered the respondent to choose multiple selections, hence a count much higher than 192.

Q2: Which versions have you experienced?

I think it's fair to say that most of the 2.x respondents might also be part of the third that said they started prior to 2007.

I started in 3.x, and still have a client to this day on that version, despite the fact it's no longer supported.

No doubt if we asked the question today, the responses for 5.0 would be far higher. And we could almost ask about 5.1 and the goodies it will bring.

On reflection, I don't miss 4.x. Page Designer in 5.0 has been fantastically more productive. I also think the Universal Theme is a great idea that I haven't sunk my teeth right into yet. The upgrade to 5.1 should really put it's new life cycle to the test. I think this post will come in handy.

Thursday, 5 May 2016

Puzzle: What's my birthday?

I was lucky enough to attend a 'Let's Talk Oracle' session from Canberran Richard Foote today diving into AWR.

After an early dad joke and before we got into his AWR collection, we gave this brain teaser.

This hurt many heads, including mine, and I also picked the wrong answer. Richard reports our group did well though, compared to other cities. Nice work to the Twitter peeps that also gave this a go.

My very first pass got me to July 18, but let the brain simmer a little further and you realise the rabbit hole is longer than expected.

Then I arrived at July 16 because I wasn't scribbling anywhere and my brain obviously couldn't plan enough moves ahead. The answer slapped me in the face soon after Richard crossed out May. I eliminated 18 and 19 and thought there was a nice looking date sitting by itself.

I knew a friend would eat this up and he vehemently concluded the answer was Aug 16, so I just had to break it down so I could send him my working.

Spoiler alert: the true answer appears at the end of this post.


Bowie and Ziggy see this list

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

Bowie told Month
Ziggy told Day

Fair presumption: Each other knows they only know a portion, because Bowie states Ziggy doesn't know both pieces of information.

If May, there is potential Ziggy knows unique number, so Bowie couldn't be certain Ziggy doesn't know.
If June, days are non-unique, so Bowies knows Ziggy couldn't be certain either way.
If July, same possibility as May
If August, same uncertainty as June.

So from Bowie's perspective, we are left with these possibilities, those months with unique numbers

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

Ziggy states at first he didn't know, so unique numbers can be eliminated.

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

All other days have duplicate, so Ziggy still uncertain of month.
Now Ziggy knows Bowie was uncertain with just month, Ziggy shares that knowledge and eliminates same months.

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

It's down to three uniques, because Ziggy states he now knows, and 14 is not unique.

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

Now Bowie says he also knows. He still can't tell between the two August days

May 13 May 15 May 19
June 13 June 14
July 16 July 18
August 14 August 15 August 16

June 13 must be the answer.

Richard's explanation

When the question went through the media rounds


Wednesday, 4 May 2016

APEX Page Numbering

Today while perusing posts in the #orclapex hashtag, I came across this from Christina Moore.
She hasn't been blogging long but I love her style and format. I found this one on classic report templates a great resource, though I wish the Oracle team would produce content like this to accompany the documentation, or as an extension to

Anyway, back to the tweet. The post she links is about planning page numbers. It had me intrigued because I do like sourcing menus with SQL direct from the APEX dictionary. My long standing top post includes a SQL pattern I use regularly to populate APEX menus.

With respect to Christina, I would like to nitpick with some points, or at least offer my perspective.

Planning page numbers

I consider myself to be a page number pedant, and why not? I'm sure we all named our Oracle Forms .fmb files in such a way to have a useful order in the folder, even though a search facility was available.

Why not do the same for APEX page numbers? I like to group my generic popups together in the lower end. Admin pages surrounding 101. Then segments of the application are grouped in 20s, 30s, 40s etc. A report/form combination is handy together since the next/previous page buttons are useful when jumping between the two, as Christina also describes.

I've even found use across applications where pages with similar functions share the same number, making it even easier to jump to the page I need. Christina mentions this when copying pages. Consistency is always a winner.

Page Grouping

Unless I've been really serious about page segmentation, I couldn't see myself applying a condition with a bounded range of page numbers such as
:APP_PAGE_ID between 100 and 200
I find this akin to hardcoding, something Steven Feuerstein could rabbit on about for days. I typically use Page Groups for times I need to identify a set of pages, in part because you're essentially documenting the pages by categorising them. Inherent, inline documentation that can be found by the next developer.

Using page groups also lends itself to building menus out of SQL, potentially in a hierarchical manner.


I also find the use of sys.dual superfluous in cases like this, to a point where it's like rubbing hair the wrong way.  All that's required is a simple PL/SQL expression (as shown above), and declarative if possible.

The Alternative

Instead of defining a process to number your pages, you could just let APEX find the next number for you. For playpen applications or those under a hundred pages, that's probably fine. Even then there will come a day you regret not forming some assemblance in your pages.

(Bert Myers)
Organisation is only human. Such abstraction has enabled our species to plan crops, track livestock, leap ahead in our understanding of the universe. Heck, nature has been doing it for over 500 million years.

... too profound?

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;
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.

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.