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.

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
https://community.oracle.com/thread/3908020

.u-Processing
{
display:none
}

I was going to suggest simply prefixing the provide class with the relevant HTML tag.

span.u-Processing
{
  display:none;
}

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
http://www.artzstudio.com/2009/04/jquery-performance-rules/
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.
span.u-Processing
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 jsperf.com you can set up such test cases and run them against different browsers. Similar websites are available for SQL (livesql.oracle.com) and JavaScript test cases (jsfiddle.net).

[Note: jsperf.com 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.
https://jsperf.com/jquery-class-vs-tag-qualfied-class-selector/47
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.

http://jsperf.com/apex-tag-vs-id-vs-class

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.
$('*').length

There are only 148 spans.
$('span').length

And only one spinner.
$('.u-Processing-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.

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
http://www.grassroots-oracle.com/2019/11/on-interpreted-code-in-oracle-apex.html

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.

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.

Prerequisites

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  sagecomputing.com.au) 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.

Scott.

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
https://community.oracle.com/thread/4168286

Analytics in APEX Charts - Moving Average

Consider a chart with a trend that might be quite jagged across data points (blue line).

What if you would like a smoother version of that line - a moving average, if you will (red line). This stabilises the results, like looking at climate vs weather.

Oracle APEX Line Chart - 2 series

It's fairly easy from a SQL point of view - in this case it's another column in the original chart query

SELECT null lnk
  ,diff   label
  ,count(*) AS qty
  ,round(avg(count(*)) over (order by diff rows 
                             between 3 preceding and 3 following)
        ,2) AS moving_avg
...

The third column uses an analytical function to calculate the average of the 6 surrounding counts at any given point on the x-axis. The "rows between" syntax is often left to the default, which is everything up to the current value (see post comments), based on the order provided. In this case it explicitly specifies the 'window' of rows to average to be only nearby data points.

Note this report looks a lot cleaner with display setting 'Marker' using 'None', as opposed to 'Circle'.

Nice, simple way of providing the user information that can be easier to read & interpret.

About CSS Selectors

Recently I saw a simple, accepted answer in the forums that tempted me to provide a small extension to the provided answer. This has since spawned two blog post ideas, here is the first (here is the second).

Background

The following question asked how to hide the spinner from a particular page full of small reports refreshed on a timer.
https://community.oracle.com/thread/3908020
The answer was some basic CSS, which could be placed in a variety of locations depending on desired scope.

.u-Processing 
{ 
display:none 
}

The answer is clear, but doesn't show any working. I'm sure many people out there would like to know how to arrive at that answer themselves.

About CSS

If you don't really know what the above code really means, let's start with the basics. CSS allows you to identify an element on a web page, then change one of it's attributes. The selector identifies the component, and the attributes are listed within the brackets. The described example identifies the processing spinner and hides it, but how do we build the relevant syntax ourselves?

I think CSS selectors can be likened to queries against the database. You want to identify a specific component, then change it.

You can invoke the spinner in order to identify it. A function is described in the Oracle APEX API Reference, which can be invoked on demand in your own applications.

You can try this on the login page to apex.oracle.com by opening the browser console window (usually F12) and typing
apex.util.showSpinner()

Invoke the spinner on any APEX page from the browser console

The spinner will display and you will also see output relating to the component itself. If you right click on the spinner and Inspect Element (Chrome et al) you can see more details about the component and what properties it currently has.

You can modify these properties directly and immediately see their impact on the page. You can un-check attributes to disable them, or change their value, often from a discrete list of option. Sizes can be increased/decreased with the arrow keys. I do this all the time to test sizes.

A good reference for these attributes can be found here
https://developer.mozilla.org/en-US/docs/Web/CSS/Reference

Using Selectors

You can identify the spinner using a variety of selector expressions, just like you could find a particular record in the database using a variety of where clauses. Bear in mind some will work faster than others.

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

Note I'm referring to the parent span of the .u-Processing-spinner that's highlighted in the image. Setting this to display none will only hide the spinning icon, not the surrounding shaded circle.

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)

The element tab can help in determining the required selector. The strip at the bottom shows the path, a form of which can also be retrieved by right-clicking the parent span in the HTML code and selecting 'Copy CSS path' (or Copy Selector, depending on browser version), which may provide a more 'specific' selector, but not necessarily what you need or what.

The spinner may be in different locations on the page depending on context. Performance is another issue, but that's for the next post.
Specificity is important since any given component on the web page could have attributes from a variety of sources, so there is a precedence called CSS Specificity.

Getting what you need

The selector doesn't need to be complicated, just specific. Try it out by either searching for the selector in the code window, or seeing what it returns in a jQuery command entered in the console.
eg:
$('.u-Processing')

Look for a class or ID on the web component you want to manipulate, test out it's uniqueness (so you don't hide anything you shouldn't).

.u-Processing

Then add the attributes you want to set within brackets, multiple attributes are separated by semi-colon.

.u-Processing { display : none; }

This code could then be placed anywhere within APEX that accepts Inline CSS, such as the page attribute. Or it could live in a .css file and associated with your applications.

Want More?

If you want more examples of what you can do with CSS and jQuery in APEX, you may consider my book, jQuery with Oracle APEX. </shameless_plug>