Wednesday, 19 September 2018

Remove duplicate from APEX collection

One of my favourite SQL analytic functions is row_number(), and I've used it in the past to identify, then remove duplicates.

In this case, I have an APEX collection that represents a session based view history of products/people/events, or whatever your users might be browsing.

I've created an option to consolidate that view history, and remove any record you might have opened more than once.

Collections are a little hard to play with outside of APEX, so I use the create session procedure in the OraOpenSource libraries to simulate an APEX session within SQL Developer.

exec oos_util_apex.create_session(120,'WESLEYS')

For my test case I simulate adding a few entries in my collection, varying a date column slightly.
apex_collection.add_member('TEST', 'Scott',  p_d001 => sysdate - 5);
apex_collection.add_member('TEST', 'Dmitri', p_d001 => sysdate - 2);
apex_collection.add_member('TEST', 'Lino',   p_d001 => sysdate - 4);
apex_collection.add_member('TEST', 'Scott',  p_d001 => sysdate - 3);
apex_collection.add_member('TEST', 'Joel',   p_d001 => sysdate - 2);
apex_collection.add_member('TEST', 'Sabine', p_d001 => sysdate - 4);
apex_collection.add_member('TEST', 'Penny',  p_d001 => sysdate - 5);
apex_collection.add_member('TEST', 'Scott',  p_d001 => sysdate - 6);
apex_collection.add_member('TEST', 'Jackie', p_d001 => sysdate - 3);
apex_collection.add_member('TEST', 'Joel',   p_d001 => sysdate - 1);

Here is a query that will use an analytical function to add a computed column that identifies the most recent entry for any name, and assign it a 1. Any subsequent entries for that name will get a 2, 3, 4 etc.
select seq_id, c001 name
  ,row_number() over 
    (partition by c001 -- look for duplicates in this set of columns
     order by d001 desc -- put records I want to keep first
     ) rn
     ,d001 dt
from apex_collections
where collection_name = 'TEST'
order by name;

In this case, seq_id 1, 5 & 6 represent the records I want to trim from the list.

Duplicate entries highlighted

Turn the query into an in-line view to filter out any records where the row_number() is not 1, as we are not allowed to have window functions in the where clause (ORA-30483).
select seq_id, rn from
 (select seq_id, row_number() over (partition by c001 order by d001 desc) rn
  from apex_collections
  where collection_name = 'TEST'
) where rn != 1;

    SEQ_ID         RN
---------- ----------
         6          2
         1          2
         5          3

Reverse the order by, and I'll keep the first entry instead.
Got more columns that signify uniqueness? Expand the partition by clause.

A standard aggregate query would be able to identify the names of those duplicates, plus how many you have, but no set of records with the unique identifiers to remove. Using min(seq_id) would be insufficient once you more than two entries.
select count(*), c001 name, min(seq_id)
from apex_collections
where collection_name = 'TEST'
group by c001
having count(*) > 1

  C NAME        MN
--- ---------- ---
  2 Joel         6
  3 Scott        1

We can fold the analytical query into a PL/SQL loop and remove the duplicates from my collection.
  << remove_duplicates >>
  for r_rec in (
    select seq_id, rn from
     (select seq_id, row_number() over (partition by c001 order by d001 desc) rn
      from apex_collections
      where collection_name = 'TEST'
    ) where rn != 1
  ) loop
      (p_collection_name =>  'TEST'
      ,p_seq             => r_rec.seq_id);
  end loop remove_duplicates;
end anon;

Where the resulting collection is minus the older duplicate.
SEQ_ID     NAME        RN DT        
---------- ---------- --- ----------
         2 Dmitri       1 2018-09-16
         9 Jackie       1 2018-09-15
        10 Joel         1 2018-09-17
         3 Lino         1 2018-09-14
         8 Penny        1 2018-09-13
         7 Sabine       1 2018-09-14
         4 Scott        1 2018-09-15

Of course, this is not limited to APEX collections, but it gave me a chance to play with more toys.

Monday, 17 September 2018

Hide region if no data found

I have a diagnostic page where I hide classic report regions that aren't relevant - ie, have no data returned.

Here I create a dynamic action that executes After Refresh of the relevant region.

Dynamic Action definition

The client-side condition evaluates the presence of the .nodatafound class within a classic report, which is present only when no records are returned.

Inspect element of region with no records

The dynamic action then shows/hides the region, depending on the result of the JavaScript expression:
$(this.triggeringElement).find('.nodatafound').length == 1

The triggering element is the region, so find the class within that region, count the result set, and compare to value 1.

Instead of the region itself, this could relate to other components only relevant when records are returned.

As Maxime pointed out in the comments, here I was focussing on classic report regions. The relevant classes you may be looking for include:

  • Classic Report (versatile): nodatafound
  • Interactive Report (IR): a-IRR-noDataMsg
  • Interactive Grid (IG): a-GV-noDataMsg

This can mean there is no need for a server-side condition to test for existence.

With this dynamic action, the region could re-appear upon refresh with results, making the page more interactive without requiring full page submission.

Friday, 14 September 2018

Add record count to collapsed region

I have a diagnostics page where I wanted to display how many records are in a collapsible region's title.

The following is a simple solution, but will only work properly if all records are displayed, and no pagination is used.
Otherwise, check this past post for alternative methods to get the number of rows in a region.

Pick a column in the region and add a class. I chose "cnt".

Column attribute

To be selective, it's always handy to add a static ID to the region.

This is not an 'advanced' feature

Now we're ready to create an After Refresh Dynamic Action on the region, executing the following JavaScript, also firing on initialisation

$('#p99_share .t-Region-title').text('Sharing ('+$('#p99_share .cnt').length+')');

It will relabel region title with the amount of .cnt classes it could find in the region.

The final result looks like this:

Collapsible region with modified title

An alternative that skips the need for a static region ID would be

   .text('Sharing ('+$(this.trigginerElement).find('.cnt').length+')');

After Refresh Dynamic Action
Edit - Trent offered a more declarative example.

Relatively simple, but effective.

Monday, 10 September 2018

Enhancing the APEX Error Handling Function with Logger

Many, many moons ago, I created an error handling function for Oracle APEX, just like the original sample provided by Patrick Wolf for the 4.1 error handling feature. You'll probably find a strong correlation between the two events.

OK, now what?
We normally see this if an exception was propagated within a PL/SQL Dynamic Action.
In this case I thought it was time for a little upgrade.

I noticed the sample code, now part of the APEX_ERROR documentation, suggested including a reference ID, perhaps from some logging package.
Conveniently, we use such a package, a popular one among the PL/SQL community called Logger.

I glanced through the package specification, and for a moment I thought it was missing a function that returned the new log ID.

I found a procedure with an OUT parameter instead, so here I log some contextual information about the error.
    p_unit_name => 'error_handler' ,
    p_scope => 'apx_util.error_handler' ,
    p_logger_level => logger.g_error,
    p_extra =>
    p_text => 'apx_util.error_handler()',
    p_call_stack => dbms_utility.format_call_stack,
    p_line_no => null,
    po_id => l_reference_id
Now the user has some context to report. I figured the wording could be softened a little, too ;p

Standard error message, with reference

So if we were to execute the following SQL, we could see further details.
select time_stamp, module, client_identifier, extra
from logger_user.logger_logs
where id = 12292314;
The log message prior to this ID may also help to provide clues as to the problem.

SQL results, slightly redacted

I also wanted to add some convenience to the developer, so I added this same information to the error popup, but only when an active session is also present within the App Builder (or some special privilege present).

Immediate context for the developer

This is toggled by checking a built in substitution string (or my application item). I note this built-in was only documented from 18.1, but I believe it has been present for a while. It's certainly returns a value in 5.1.
l_result.message := 'We had a problem completing this request. '||
    'Please contact IT Support'||
    ' for further investigation. Reference: '||l_reference_id
    ||case when v('F_SEC_DEV') = 'Y' -- anyone with privilege
    -- or has builder open (from oos_util_apex.is_developer)
    or coalesce(apex_application.g_edit_cookie_session_id
               ,v('APP_BUILDER_SESSION')) is not null then
      ' Dev only: '||
      ||' ~ '||p_error.component.type
      ||' ~ '||p_error.message
This brightened the day of some of my colleagues.

Wednesday, 5 September 2018

Client Side Dynamic Actions using jQuery Selectors

Consider a data entry page where it might be nice to capitalise the first letter of a person's name, for a number of fields.

I understand I may be anglicising a problem that contains minutia, but focus instead on the thought processes and options we have available using Dynamic Actions.

Let's say we want to create a dynamic action that responds to change on any of those name fields, then runs some JavaScript to apply sentence-case to the name value, before the user submits the page.

We can do this declaratively just using the mouse, APEX will construct a comma delimited list for you as you select page items from the list.

Declarative item selection

But that's not the only way we can nominate components on a web page. The example above might create a selector for those items that is a comma delimited list of IDs.


When we have a look at the underlying HTML defined for these items using Inspect Element, the selector would locate these fields based on the id="P18_FIRST_NAME"

Inspect Element to see underlying details of page components

Alternatively, we could use classes. This is how web developers of any ilk build their web pages. They make up a string that means something to them, then associate some behaviour with it.
You don't need to "define" a class anywhere, but it pays to ensure it's unique, and follows a standard.

In our case, if we could add a class to each item, then we would only need to list one class in the dynamic action.
In some circumstances, this style of coupling behaviour could be more advantageous.

To make "initcap" appear as a class, as shown in the item as highlighted above, add the string to the 'CSS Classes' in the Advanced section of the item properties.

APEX Page Builder makes this task quicker, thanks to behaviour inspired by Oracle Forms - well, some of us ex-Forms programmers will recognise it as such.

If you select multiple items, you can change some properties in bulk.

Over in the properties, we can modify CSS Classes attribute for all three items at once.

Multi-item select in Page Designer

Note the 'Value Placeholder' attribute - this is different for each field, so the delta symbol is shown with the attribute value blued-out. I love this concept.

So back on the dynamic action, we can change the 'Selection Type' to jQuery Selector, and reference the class with the relevant syntax - prefixed with a full-stop, as opposed to the hashtag for IDs.


Dynamic action, only when item value all lower case

We also didn't want this behaviour to apply only when all the letters are still lower-case. If the user wants to enter "von Braun", I won't overwrite their particular usage.

So note the client-side condition, emphasis on the 'client'. Most conditions on APEX components are server-side, which generally means they are evaluated during page render - do we or do we not include this button/region/item? Dynamic actions have client side conditions to decide whether to apply the True actions, or the False actions.

$(this.triggeringElement).val().toLowerCase() == $(this.triggeringElement).val()

Here I've referred to the item being triggered using this.triggeringElement, mentioned in the inline help for this particular attribute. Wrapping that expression with $().val() gives me the item value, or I could have used apex.item().getValue. jQuery built in .toLowerCase() does what you would hopefully infer.

As with all things programming, there are many ways to cook an egg, and this goes for setting the value. Here I've define a Set Value action that uses a JavaScript Expression, as there is no need for a round trip to the database server.

True Action - apply change to item value

This expression uses a function I found on Stack Overflow that I was happy with


I placed in an application level JavaScript file, included via User Interface attributes of the application.
// Turn mcdonald into McDonald
// only run/apply if current string lowercase
// $(this.triggeringElement).val().toLowerCase() == $(this.triggeringElement).val()
function toProperCase(s)
  return s.toLowerCase().replace( /\b((m)(a?c))?(\w)/g,
          function($1, $2, $3, $4, $5) { if($2){return $3.toUpperCase()+$4+$5.toUpperCase();} return $1.toUpperCase(); });
Most of the JavaScript usage I have in my applications these days tend to be one line, or is some form of expression, so commonly used there is only a small handful.

Don't let a little JavaScript scare you away from enabling useful interactivity for the end user. Dynamic actions do most of the work for you :p

Thursday, 23 August 2018

Pseudo Radiogroup in APEX Report

I'd be surprised if you've ever tried to put a radio group in a report, but if you've ever attempted it you might come across a post from Vincent Deelan.

When it comes to checkboxes and radio groups, the nature of HTML haunts us.

So it turns out it's possible to do the same task within a report, and we can build it so we don't even need to submit the page. It really just depends on how you want the page to interact. The following example simply logs the rating selected, rather than a rating being stored as an attribute of Emp.

This is no doubt even easier with Interactive Grids, but I'm not there yet, and I'm sure others aren't either.
And it's fun using Dynamic Actions to make applications more interactive, and revive draft blog posts from a good 18 months earlier. (I've got a few of these...)

I built a traffic light style report once before, but leveraging off the Universal Theme made this effort so much easier.

Consider the template options for creating a pill button group using standard button components.

Button Template Options

If you create three buttons, all with first, inner, last button sets respectively, you get something that looks like this:

Pill buttons
I refer to them as such because if you use the Inspect Element tool on these buttons, the template option classes are named as such
Check out the UT button builder for more examples.

So to utilise this in a report, I'll combine three manually constructed buttons, levering other existing classes.
select EMPNO,
  '<span style="white-space: nowrap;">'
    ||'<a href="javascript:void(0);" data-emp="'||empno||'"'
    ||' class="high t-Button t-Button--success t-Button--simple t-Button--pillStart">H</a>'
    ||'<a href="javascript:void(0);" data-emp="'||empno||'"'
    ||' class="medium t-Button t-Button--warning t-Button--simple t-Button--pill">M</a>'
    ||'<a href="javascript:void(0);" data-emp="'||empno||'"'
    ||' class="low t-Button t-Button--danger t-Button--simple t-Button--pillEnd">L</a>'
    ||'</span>'  rate   
from scott.emp

If you remember to Escape Special Characters for the rate columnm you'll see something like this

Now you could slice and dice the dynamic actions in a number of ways, but here I created one for each type of button. So on click of the relevant class I associated with each button type, it will get the value from the data- attribute, put it in a hidden item, then insert a record.

The Set Value action would set some hidden item using the JavaScript expression
The subsequent PL/SQL process would submit this item to session state, and insert the empno/rating selection into the log table. The third action refreshes the Rating region so we can see the new data.

We can make the buttons more pill-like but adding a border radius
span a.t-Button {border-radius: 10px;}

To dull the colours a touch
span a.t-Button{border-color: lightgrey; box-shadow: 0 0 0 1px lightgrey inset !important;}

It would only take a few more lines of jQuery to turn this into a status, leaving the active button highlighted. Plus a slight tweak to the SQL to match the relevant record.

Obviously I had a demo of this, but this draft was so old I've forgotten where I put it ;p

Wednesday, 1 August 2018

APEX 18.2 Statement of Direction

I've been thinking recently it's been a while since I remember seeing a revised Statement of Direction, and sure enough I see news of an update for 18.2.

It was back in 2015 that I last made my own conjecture about what each statement means (without the benefit of listening to as many conference sessions) and look at some of the outcomes now!

  • IG - well, I'm still a little late to that party, but missing out on early cuts & bruises ;p
  • Master detail detail - I was training people last week and it occurred to me this is another feature I don't really work with, but maybe should give a go.
  • New charting engine - well JET is going really well! Though I'm still experiencing teething issues with the data densification.

As for APEX 18.2, we now see:

  • Improved workspace provisioning wizard - not a big drawcard for myself, but I can see how some fresh attention in this space could be warranted.
  • New side-by-side master detail page type available in create page wizard - as I just noted, I need to experiment with recent master-detail development, to perhaps integrate with regular design patterns.
  • New dashboard page type available in create page wizard - Now I’m guessing this is just a wizard to help build an appropriate page – full of components already available to us, but used in an effective way. Either way, I'm interested.
  • Improved warnings with REST workshop to prevent loss of custom definitions - well, that sounds useful.
  • More comprehensive JavaScript API documentation - even more? I've barely had a chance at using the new set. I hope it comes with more examples. I think I saw a glimpse of Shakeeb's /ut application the other night with SQL examples inline for classic report variations. +1 from me.
  • Ability to update Font APEX stylesheets and font files independent of Oracle APEX releases - Maxime already showed us this was possible, and easy. I'm glad behaviour like this is enabled by the APEX team. 
  • Installing sample datasets now enables the creation of a complete sample application - well, that sounds intriguing.
  • EMP / DEPT sample dataset now available in different languages - sometimes I'm grateful my first language is English, but sometimes I really wish I was natively bilingual.
  • Updated productivity and sample apps - seems to go without saying now, but I haven't farmed them for ideas for a while.
They've always come through pretty well with these lists, plus a whole bunch of other extra stuff we find along the way. I also found this nugget from Oliver.

After recent experiences I thought I'd add a few examples of what I'd like to see, no doubt forgetting/neglecting some other needs & wants, includes:
  • IR - support for saved IR (& IG?) are still hidden away a few layers deep in a task menu. Saved IR are still inseparable from app_id. We could use some love here.
  • Excel2Collection - considering the efforts made in assisting Access & Forms users to migrate their information to APEX, perhaps it might be wise to integrate such a simple method of transferring Excel workbook content to a table in the database.
    The ORDS solution hung around for a while, and I find the data loading wizard somewhat clunky. 
  • PWA - Vincent recently published a comprehensive guide on turning APEX into a PWA. This technology is early days (certainly in my learning bubble), but I think APEX would benefit from internal support in regard to further penetrating the mobile market.
  • Native Super LOV - this one from Menno is great, but surely it's time this was baked in?
  • Dynamic Action support for inline modals - there are a few lines of JavaScript I repeat often that I'm sure could be replaced by declarative actions
Anything else? I've made wishlishs in the past (5.x, 4.2), but looking back they seem a little mundane.

Wednesday, 11 July 2018

Oracle Forms Migration

This post is one of a series on what I learned while not at Kscope18.

I realise that's a month ago now, but the mind still ponders, and I've had these lined up for a while. Just got busy with a deployment, as you do. Went nice and smooth though, APEX sure does make staggered deployments easier with build options. Underrated feature. /tangent

Anyhoo - every now and then someone on the forum asks about the Oracle Forms migration tool. I've noticed it a bit recently, I guess that's a good thing.

Oracle Forms Migration. I guess what I'm learning here is that sometimes I'm a little shocked they keep at it, but I guess you've got to play the odds. And I haven't heard what was said with this slide.

I'd like to start and alternate list:

1) Setup APEX. Of course. OracleRAD will make this a breeze.

2) Create a schema that will serve as your conduit to your existing data. Privileges become additive, adding another layer to protect your data, which may include information not for the relevant APEX application.

3) Analyse what needs to change, what's broken, what's required.

Important note - workflow in a browser will be different from Oracle Forms.

While the components, capabilities, and IDE are astonishingly similar, I would never bother converting Form->Page. You'll spend more time cleaning when you could be doing more productive building.

The web universe just works different to Forms. It offers different opportunities. For instance, it's easier to build walk-through wizards for data entry. This can be important in touch device world, offering interaction with buttons instead of virtual keyboard is a plus.

Our applications now tend to generally require less keystrokes for information to be added, it's more of an interaction of buttons.

4) Import your Forms code if you must, but only for the ability to annotate existing code while you build the fresh interface. You may get better mileage by analysing straight from Oracle Forms.

The spectrum of code you'll need to ditch/refactor/keep will vary depending on the quality of the legacy application.

Continue analysis of business rules. Are they fully known? stale? Does workflow need improvement? Maybe concessions were made during the Forms development.

5) Enhance - not always necessary, but always good to add polish. But this notion of RAD? Yep, you get pretty darn good apps without even trying to add polish. And polish with a few Dynamic Actions go a long way.

And with APEX plugins, polish comes in big, free tubs. And yes, you should embrace plugins.

6) Test. Of course. And with clicky people. People who click about for the sake of clicking stuff. They're the people that are going to find those left field holes.

7) Train. While you can never replace face-to-face explanations, hopefully your application will explain itself.

8) Rollout, and enjoy applications that aren't easily obstructed as new versions of APEX arrive.
Applications that run fast, derived directly from business data, data delivered to the right people, with a natural history of who visited what and how long it took them.

We've got a keeper.

Monday, 18 June 2018

Free Oracle Learning Tools

This post is one of a series on what I learned while not at Kscope18.

Would you like to learn something from the Oracle technology stack?
Here's a slide probably in a bunch of Oracle employee decks.

I think this collection represents the commitment Oracle is making to the developer community, in part thanks to the developer advocates team. Upon seeing these listed together, and considering the amount of work done to keep raising the bar, I've learned Oracle really are serious about engaging with the greater community.

  1. - one of the packaged apps given it's own URL. Construct your DDL/DML really fast, if you're happy to learn more markdown syntax.
  2. - it's ridiculously easy to start playing with the Oracle product at this site. Recently I wanted to check some behaviour with a JSON query in the database version above what we had available, and I was able to confirm a database upgrade would solve the problem we had.
  3. - I spend 5-10 minutes a week playing 3 quizzes, targeting PL/SQL, SQL, and database design. Sometimes I learn something new, something re-enforce something I'm familiar with, something I'd forgotten, or something interesting the database can do but I might never use.
  4. - surely we've all been here, playing with the most up-to-date release of the best feature of the a great database.
  5. - I learned all my best habits from here, and highly recommend anyone ask a question. Especially anyone learning (hint - that's all of us). It's also a great place to learn how to ask a question.

These are all included in the APEX shortcuts page, along with another learning tool that could fill that 6th spot (besides 18c XE) - the forum. Here I do what I used to do at AskTom - read anything comes through; start listening to topics I need/want to learn about; and here I also get to contribute where/when I can add some of my knowledge & experience. Or if I have a bookmark to a solution someone previously documented.

At the forum you'll find a collection of volunteers willing to help you with your APEX/Oracle questions. Remember, "the #orclapex community is like no other".

ps - we could probably also add the Oracle Learning Library.

Thursday, 14 June 2018

Community Recognition at Kscope18

This post is one of a series on what I learned while not at Kscope18.

Some well deserved people have been recognised by the Oracle ACE program. One in particular I noticed was Daniel, creator of many practical APEX plugins.

Other mentions include, but not limited to, Adrian Png, Maxime Tremblay, Kai Donato, Moritz Klein, Becky Wagner, Eugene Fedorenko, Opal Alapat.

People like these really help keep our community strong, as Monty says,
"The #orclapex community is like no other." 

And Juergen Schuster, createor of, received special recognition from the APEX community.

It was a pleasure to meet this man, and I really need to catch up on his APEX podcast.

To everyone, a very Australian good onya!

The faces of Oracle APEX

This post is one of a series on what I learned while not at Kscope18.

The faces of Oracle APEX.
Well, some of them, at least. There are more listed in this picture, but it's harder to see. The one of Shakeeb comes across a lot better on his Twitter profile.

I've been lucky enough to meet many of these people, but here they are. If you see them in person, feel free to thank them.

Across the top, with my impression of their major facet of contribution:
Marc Sewtz - various integrations
David Peake - Product Manager, an Aussie ex-pat also bringing some Forms touch to APEX
Anthony Rainer - IR/IG/Accessibility, and various things JavaScript

Alon the bottom:
Joel Kallman - Director of Development, and all round nice guy
Carsten Czarki - tamer of web services using SQL
Hilary Farrell - hard to get a face to that name online, but you may have thanked her Charts demo app.
Shakeeb Rahman - all things UT and design.
Patrick Wolf - I believe we can thank him for Page Designer. Probably had a big hand in plugins, too.

Seriously, what a product!


ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.

Which means this
date '2018-06-10'

Is the same as

And you'll never want to type the latter again.

I learned this little chestnut as a trainer of SQL, but what I didn't pick up, or have since forgotten, is this

timestamp '2018-06-10 14:33:41'

Thanks again, Connor, for adding to this thread.

ANSI dates
Here's some more info on ANSI dates. It's not lazy, Tanel, it's efficient ;p

Learning is a lifetime pursuit.

Oracle XE 18c and #OracleRAD

This post is one of a series on what I learned while not at Kscope18.

Oracle XE (Express Edition) 18c - which is more than just a free, limited db.
Combined with the notion of #OracleRAD, you've got a mongo killer ;p


Quite a few people had a shot of the XE 18c feature list.

Impressive as that is, when you start putting it into context of the sort of things that can now be achieved with the technology available today, it's mind boggling.
And Connor knows just how to amaze us the potential of this technology, making amazing concepts sound just within reach.

But I think the best way to think about it is how this mini-stack adds up.

We've got a free Database; with a middle tier that can handle all our web services needs (REST), while still using SQL; and APEX in the middle, giving us access to pretty much every capability the web offers today, where the productive IDE lives within the browser. Simply, wow.

I'd like to end this post with this tweet I saw just before Kscope18 began

This XE 18c will be magnitudes more advanced than XE 11g, and I think I'm understanding the true potential of this. I can't wait to find a stream of Mike Hichwa expressing his passion for this release.

Things I learned while not at Kscope18

Yep, as much as I wanted to be at Kscope18 this year, my abstracts were not accepted.
And it's a long way.
And I'm still waiting for this.

So this is a small collection of stuff I learnt only from Twitter - just by keeping an eye on the #kscope18 hashtag. Anyone can do his, even if you're not on twitter. Try that link and see.


Seriously, Twitter is an effective tool in keeping up with the Oracle APEX product. Plenty of ACEs on Twitter, all dishing out interesting infomation. If you don't like consuming it direct, has a great filtered feed.

Some of these short posts may look like those horrible "articles" which are just a collection of tweets with a little commentary, where they still call it journalism.

These next few posts are different to that, honest ;p

And I'd love for anyone who was actually at the event to extend/correct information I've interpreted.
It's still going on, too. We're in the relative future in Australia, so I'm still seeing tweets come through as I write this. And I've been concentrating on #orclapex.

If you still want to just see some sessions, some are already streamable, and I understand many more will be available at some point for members on the ODTUG website, just like previous years.

It seems last year I had similar inspiration, and I saw this tweet a few days before Kscope began.

Tuesday, 15 May 2018

Transposing data using UNPIVOT

A couple of years ago I posted a method to remove nulls from a report using the Value Attribute Paris - Column template.

Here's an example of how we might utilise the region, within the breadcrumb region position.

Note - some values may have been adjusted from this screenshot for their protection.

Any nulls were shown as a tilde, then hunted down and eliminated with some jQuery that executes after refresh of the region, and/or on load of the page

Cool idea, can be improved.

And when the JavaScript was placed on one line, it seems so innocuous. It works, so what? What's the harm?
$('dd.t-AVPList-value').each(function(){if ($(this).text().indexOf('~')>0) $(this).hide().prev().hide()})

The trouble with my original method is that it's executing jQuery after the page is rendered. This represents extra work that could be eliminated. The same justification was present in Oracle Forms, as Post-Query triggers were not preferred.

And of course the applies to DML being applied to the database. Sure, you may need some conditional processing and apply a zillion single updates, or you could write some elegant SQL to do it within one update.

The other problem is that the Universal Theme responds to this change with some content movement that can frustrate the user - the body bubbles up to meet the removed content.

New and Improved Solution

Side-note: how can it be new and improved?

In this case I've been talking about columns from a relative simple query, as simple as selecting from scott.emp.

If we can transpose these results, like a magic wand you can do in Excel, then we could just use the Value Attribute Pairs - Row version of the template, and not worry about any jQuery that manipulates the page after it's rendered.

We tranpose columns by surrounding the existing query with a simple UNPIVOT.

select * from (
    select to_char(empno) empno
     ,to_char(sal) sal
     ,to_char(comm) comm
    from scott.emp
    where empno = 7788
    --where empno = 7654
 for name in (
    (empno) as 'Emp No'
   ,(ename) as 'Name'
   ,(job) as 'Job'
   ,(sal) as 'Sal'
   ,(comm) as 'Commission'

NAME       VAL                                     
---------- --------
Emp No     7788                                    
Name       SCOTT                                   
Job        ANALYST                                
Sal        3000   

4 rows selected.

... query executed with other empno

NAME       VAL                                     
---------- ---------
Emp No     7654                                    
Name       MARTIN                                  
Job        SALESMAN                                
Sal        1250                                    
Commission 1400

5 rows selected.

Notice the result using an emp with a commission shows more rows. The page will only render the data supplied so there no dynamic action required.

Default ordering seems to honour the order of elements in the FOR expression.
Datatypes of columns must match up, hence the to_char around the numeric columns.

Update - I saw Mike on the forums suggest that "transpose" implies a matrix, and offered a combined unpivot/pivot.

tl;dr steps

  • surround the existing query with unpivot
  • add any datatype conversions necessary to make columns the same
  • change report region from pairs with column to pairs with row
  • remove declarative column ordering
  • remove the dynamic action that finds the tildes

If you want to modify how something presents itself on an APEX page, there are other options to explore before jQuery
  • Template options
  • Conditional SQL, manifesting as HTML expression in a column
  • CSS solutions trump JavaScript.
Simplify, man.

Monday, 14 May 2018

Filtering outliers from Oracle APEX activity logs

Last year I described a simple test case that described how to remove outliers from a fictional dataset using the STDDEV() analytical function .

I want to follow this up with a practical case using one of my favourite data sets - the apex_workspace_activity_logs that record who opened what page, in what context, and how long it took to generate.

I've been keeping an eye on the performance of a particular page, after making a few performance adjustments to some conditions. Unfortunately, we had an unrelated anomaly that pushed average pages times quite high for a short period. Needless to say, this set of outliers transformed my beautiful performance indicating lines to a boxy bell curve.

Oracle APEX page performance data with extreme outlier

A great feature with Oracle JET is the ability to hide certain series, on click within the legend.
In this case I just wanted to ignore the MAX line for this post, which in this chart forms the secondary y-axis.

OracleJET Region Attributes - rescale
Our clients really enjoy this particular feature (so do I), so thanks to the JET team for building such a device, and the APEX team for integrating it.

This graph shows results where I modified the query to filter the outliers, on demand.

Performance graph with outlier removed

Looks like the adjustments to the conditions worked! The trend is downwards.

I tried a few variations to control the switch, but this seemed to perform the most predictably, although I'm not happy with the hardcoded number.

select [aggregate stuff]
from (
 select [all columns]
,case when :P23_IGNORE_OUTLIERS = 'Y' then
  -- only bother calculating when filtering them out
  stddev(elapsed_time) over (order by null)
end as the_stddev
from [activity logs]
where [time/page is desired]
-- only when elapsed time less than 2 standard deviations gets 95% of your data
where (elapsed_time < 2*the_stddev )
I have a generic example of this on I pay attention to these aggregates for our performance reports
  • Median - what most users are experiencing
  • Average - a typical user experience, influenced by extremes
  • Moving average - general trend of visits, spread over a few days. An attempt to normalise local events
  • Max - what's the worst some people are experiencing?
Here are some other activity log queries you may find interesting.

Happy graphing!

Tuesday, 24 April 2018

Logger Snippets for VS Code

Snippets are a common feature in text editors, allowing you to essentially paste in a snippet of code from some library somewhere.

Snippets appear for selection as you start typing, and the editor will paste in the entire content of the snippet.

Snippets in VS Code

VS code snippets also have the ability to focus the cursor, ready for typing.

It's very closely related to code completion concepts, applied in VS Code with Intellisense. Vito on Twitter suggested perhaps these should be incorporated into intellisense instead of snippets. I think perhaps that might be a personal choice.

I found following the guide useful in getting started

And I thought I'd start with the Logger template, since I've finally converted.

I quickly ended up in the deep end, but swimming well. In a few strokes, I can add this entire block of text, with the three instances of the procedure name highlighted, ready to type over. That's pretty cool.

This took 5 keystrokes

I figured a few variations of the entire text would be useful, so now as I type "log", I can tab, then start typing my log text.

 The first few commands at the start of a procedure form another snippet, with the parameter name ready to type.

If I'm going to logger, I'm saving some typing...

I extended this to include two parameters, and as you tab out of the first, the second two 'tabstops' are highlighted.

With all the magic going on by Adrian Png et al, I was a little surprised someone hadn't already published something for Logger.
If you're keen, here's my snippets so far. I haven't really thought too hard about the names yet, they're pretty much as I've smashed them out.
Others on Twitter suggested they might send through a few examples they use, I was going to wait for some more inspiration.

 // Place your global snippets here. Each snippet is defined under a snippet name and has a scope, prefix, body and
 // description. Add comma separated ids of the languages where the snippet is applicable in the scope field. If scope
 // is left empty or omitted, the snippet gets applied to all languages. The prefix is what is
 // used to trigger the snippet and the body will be expanded and inserted. Possible variables are:
 // $1, $2 for tab stops, $0 for the final cursor position, and ${1:label}, ${2:another} for placeholders.
 // Placeholders with the same ids are connected.
 // Example:
 // "Print to console": {
 //  "scope": "javascript,typescript",
 //  "prefix": "log",
 //  "body": [
 //   "console.log('$1');",
 //   "$2"
 //  ],
 //  "description": "Log output to console"
 // }
"logger_proc" : {
 "scope" : "plsql",
 "prefix" : "logger",
  "procedure ${1:proc_name}("
    ,"p_param1_todo in varchar2)"
    ,"\tl_scope logger_logs.scope%type := gc_scope_prefix || '${1:proc_name}';"
    ,"\tl_params logger.tab_param;"
    ,"\tlogger.append_param(l_params, 'p_param1_todo', p_param1_todo);"
    ,"\tlogger.log('START', l_scope, null, l_params);"
    ,"  ..."
    ,"  -- All calls to logger should pass in the scope"
    ,"  ..."
    ,"\tlogger.log('END', l_scope);"
    ,"when no_data_found then"
 ,"  logger.log_error('No data found', l_scope, null, l_params);"
 ,"  raise;"
 ,"end ${1:proc_name};"
  ,"description": "Logger procedure definition"
,"logger_log" : {
   "scope" : "plsql"
  ,"prefix" : "logger"
  ,"body":[ "logger.log('$1', l_scope);"]
  ,"description": "Logger single line"

,"logger_log_val" : {
  "scope" : "plsql"
 ,"prefix" : "logger"
 ,"body":[ "logger.log('$1'||$1, l_scope);"]
 ,"description": "Logger value"

,"logger_scope" : {
  "scope" : "plsql"
 ,"prefix" : "logger"
 ,"body":[ "gc_scope_prefix constant varchar2(31) := lower(\\$\\$plsql_unit) || '.';"]
 ,"description": "Logger scope prefix"

,"logger_start" : {
  "scope" : "plsql"
 ,"prefix" : "logger"
 ,"body":[ "logger.append_param(l_params, '${1:param_name}', $1);"
          ,"logger.append_param(l_params, '${2:param_name}', $2);"
          ,"logger.log('START', l_scope, null, l_params);"]
 ,"description": "Logger start lines"

,"logger_updated_rows" : {
  "scope" : "plsql"
 ,"prefix" : "logger"
 ,"body":[ "logger.log(sql%rowcount||' ${1:param_name}', l_scope);"]
 ,"description": "Logger updated rows"

,"logger_updated_end" : {
  "scope" : "plsql"
 ,"prefix" : "logger"
 ,"body":[ "logger.log('END: '||sql%rowcount||' updated', l_scope);"]
 ,"description": "Logger updated end"

,"logger_variables" : {
  "scope" : "plsql"
 ,"prefix" : "logger"
 ,"body":[   "\tl_scope logger_logs.scope%type := gc_scope_prefix || '${1:proc_name}';"
            ,"\tl_params logger.tab_param;"
 ,"description": "Logger variables"

,"apex_url" : {
  "scope" : "plsql"
 ,"prefix" : "apex"
 ,"body":[   "f?p=&APP_ID:&APP_PAGE_ID:&SESSION.:&REQUEST.:&DEBUG.::::"
 ,"description": "APEX URL"


The trickiest bit was escaping the dollar signs for plsql_unit. Thanks Stack Overflow.

Perhaps this set of JSON could be transformed into the Intellisense format. That would make an interesting SQL exercise...

Next step, start organising my own stuff on GIT, instead of blog posts and other documents.

Monday, 23 April 2018

On Switching Code Editors

Ever since I joined the industry, my preferred text editor was TextPad.
It's a solid tool, similar to Notepad++. I probably underutilise it, but I started to see some people to interesting things with more modern editors.

Day-to-day, I work with two monitors, so I've been trialling software literally side-by-side for a while.

With some help from my nearby colleague, I tried a little Sublime (freemium), and that peaked my interest when contemplating the jump. Atom was also emerging at this time, which also had an impressive list of pros & cons. I wanted to give them all a fair go, but it can be tricky when you're busy, you know, programming.

I've ended up using Atom for a number of months now, even after moaning on twitter about it a few times. I wouldn't be surprised if it was over a year. I like the taste it gives for a modern editor, and it really intrigues me how it was built (JavaScript driven), but I think therein lies the downside. It's improved, but it doesn't scale well - for large files, or large lists of files.

Recently I've been hearing mostly good things about Visual Studio Code (free), especially for/by APEX developers, but also from the community in general. The young blood in the 2018 Stack Overflow survey regarded it highly - certainly far more than the 7.2% recorded in 2016.

2018 Stack Overflow Survey - Editors of choice

So I finally thought right, I've got to give VSC a good crack. There's stuff here I need to learn. Stuff that can make me more productive.

I printed a keyboard shortcut cheat sheets, since that's really what's going to make me productive. Luckily, like Toad & SQL Developer, Atom & VSC share a lot of shortcut commands.
And this thought in the back of my mind, perhaps from Editor wizards Adrian Png or Jorge Rimblas, that you should commit to the tool instead of using an extension to bridge shortcut commands.
and I wanted some new wins that would exhibit immediate ROI over the other editors.

I wanted to compile from my editor.

I realise that's what SQL Developer is kinda built for, but we also need dedicated editors for other document types.

I tried making this happen a few times with Atom, but I'm not sure it really liked our network structure.

I got this done in about five minutes with help from Morten.
No hiccups found, and I tweaked it slightly to use a "recent_errors" view I already had defined.

And with the extra time & excitement I had, I had a lot of fun with snippets - which this post was supposed to be about. I think I'm going to like snippets. Something I know Textpad had but I never got going with, but VSC is making the whole experience feel good.

I even find the syntax highlighting pleasing to the eye, so I think this is going to be a welcome transition to VS. Though I haven't played with CSS/JavaScript yet.

Using extension xyz.plsql-language

I understand it shared some framework concepts with Atom, so I'll probably get more familiarity with JSON while I'm at it.

"User Settings" is a place a developer can truly call home. While I yearn for a GUI, I see the advantage & flexibility with this approach. Though I'm sure it could be GUI-fied a little further.

Change default on left, added & overwritten by right.

I know a few actions I still do regularly in TextPad, so we'll see how things go on day two.
- searching across files
- basic macros for repeated text commands, though possible circumvented with the advanced multi-edit mode.
- compare files. I'm not sure why I still do this in TextPad.
- when I just want to see files in white background, with really familiar highlighting.

And with this momentum, I really need to restore my love for SQL*Plus with SQLCL.

If you want some more reading, I also came across a couple of articles that helped validate my experiences, and consider the move:
Switching from VSC to Sublime
Best Text Editor?


Monday, 12 March 2018

Emulating touch devices for Oracle APEX

In my Exploring AJAX presentation I had a frame that mentioned the ability for the Chrome browser to emulate the behaviour of a mobile device.

Open up the browser tools, I use F12. I spend a fair it of time in the Elements + Console tabs of these tools.
Then find the relevant icon, that will no doubt change location in future. Ctrl+Shift+M does the trick when the browser tools are open.

This mode allows you to change the type (size) of the device you're emulating; the orientation; and the network speed. Even the cursor changes to a shaded circle, to more closely simulate a (thicker) finger press.

Second icon from the left - follow the blue arrow

If you open your APEX login page when the emulator is already turned on, the user_agent will be spoofed accordingly. You could use Christian Rokitta's adaptation of categorizr to determine if you are on a 4" device, and populate an application item. That way, if certain regions don't fold well into small devices, you could conditionally replace them with more appropriate regions.

For example, I had a badge list of 5 values that didn't squeeze well into 4 inches, so I replaced it with a simpler value attribute pair region when my IS_MOBILE application item was set.
Note: don't forget to use declarative conditions where you can.

The 5.0 Universal Theme actually does a good job rendering regions responsively. Issues usually arise when there's simply too many columns to fit. I'm very much looking forward to the 5.1 OracleJET charts cathing up to to the touch behaviour. AnyChart prior to HTML5 doesn't adjust well.

Also available is the ability to throttle the connection. No doubt you have a pretty fine connection where you're developing, but the users on devices out in the field might not have the best connection. This setting allows you check your application's behaviour as if it were on a slower connection, highlighting any asynchronous issues, or lag that affects the user experience.

Nothing is ever the same as the real thing, so while it's a practical unit testing tool, you should still thrash out mission critical devices on the real McKoy.

I hear whispers that APEX 5.2 is going to potentially shake up the User Interfaces a little, with the removal of the jQuery Mobile User Interface. Perhaps there will be accommodations here to help our applications transition between screens, without needing dedicated pages?
I haven't seen any of the sessions delivered by the Oracle team talking up 5.2, so I'll have to wait and see.

Update -  I wrote this last paragraph a while back, though I don't think the early adopter covers any changes in this regard.