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>

SQL Analytics in every day APEX

Looking for way to apply analytical functions to your APEX applications?

I had a classic report where I wanted to dynamically source the column headers from counts in the database (values in brackets).

The ability to do this has been a feature of APEX for a while, but this was the first time I did it in APEX 5.0.

Customise headers via Region attributes

With Connor's recent spate of analytics videos,  I thought I'd mention this use case where LISTAGG() was perfectly apt.

A basic query like this will return a grouped count.

select count(*), catgy
from some_categories
group by catgy

  COUNT(*) CATGY_CODE
---------- ----------
         5 CATGY1    
         5 CATGY3    
         1 CATGY5    
         7 CATGY2    
         1 CATGY4    
         1 CATGY6    

 6 rows selected 

But the string needs to look like
Rep:Catgy1 (2):Catgy2 (4):...

So I need to transpose those rows into a colon delimited string. Here's how you can do it with SQL analytics.

declare
  lc_hdr  varchar2(512);
begin  
  select 'Rep:'||listagg(initcap(catgy)||' ('||count(*)||')' -- just build a fancy string
                        ,':') within group (order by catgy) -- concatenated by ':', listed in order of catgy
  into lc_hdr
  from some_categories
  group by catgy;

  return lc_hdr;
end anon;

An alternative may be bulk collecting the results into a PL/SQL array, then using apex_util.table_to_string(), but not when the problem can be solved with simple SQL ;p

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, not multiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations

SELECT 'SCOTT' login_id 
  ,'X' alpha, 'Y' beta
  ,10 catgy1
  ,20 catgy2
  ,30 catgy3
  ,40 catgy4
  ,50 catgy5
  ,60 catgy6
FROM dual
/

LOGIN A B     CATGY1     CATGY2     CATGY3     CATGY4     CATGY5     CATGY6
----- - - ---------- ---------- ---------- ---------- ---------- ----------
SCOTT X Y         10         20         30         40         50         60

 1 row selected

Trouble is, I needed the categories described as rows, not columns. So I wrapped the original query within an unpivot, commenting how the syntax represents the translation.

select login_id, alpha, beta, catgy, quota -- new columns
from ( -- existing query
  select 'SCOTT' login_id 
      ,'X' alpha, 'Y' beta      
      ,10 catgy1
      ,20 catgy2
      ,30 catgy3
      ,40 catgy4
      ,50 catgy5
      ,60 catgy6
  from dual
) -- end existing query
unpivot
(
quota -- new column: value
  for catgy in -- new column translating previously separate columns to discrete data
    ( -- and the column -> data translation listed here
     catgy1 as 'CATGY1' 
    ,catgy2 as 'CATGY2'
    ,catgy3 as 'CATGY3'
    ,catgy4 as 'CATGY4'
    ,catgy5 as 'CATGY5' 
    ,catgy6 as 'CATGY6'
    )
)
/

LOGIN A B CATGY       QUOTA
----- - - ------ ----------
SCOTT X Y CATGY1         10
SCOTT X Y CATGY2         20
SCOTT X Y CATGY3         30
SCOTT X Y CATGY4         40
SCOTT X Y CATGY5         50
SCOTT X Y CATGY6         60

 6 rows selected 

Relatively easy! Hope it helps one day.

You can see the results from these statements at livesql.oracle.com

Talking SQL Analytics with Connor

In about 22 hours I'll be talking with Oracle Developer Advocate (or the southern/eastern hemisphere AskTom adjunct) Connor McDonald about one of my favourite part of Oracle, being SQL Analytics.

If you haven't seen any of his YouTube videos recently, this will be a good chance to catch up and see how easy and practical SQL analytics are, and how you end up with simpler SQL.

I like to think of them like Excel calculations you can put in your result set.

Webinar registration details available here. It starts Tue, Mar 1, 2016 6:00 AM - 7:00 AM CST, or 6am for us Perth people.

Yes, my line has been tested and as long as you only hear my voice it should be cool. Connor apparently lives in a neighborhood more favourable to technology.