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 - Aussie ex-pat bringing some Forms touch to APEX
Anthony Rainer - IR/IG/Accessibility, and various things JavaScript

Alon the bottom:
Joel Kallman - PM 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.

Monday, 29 January 2018

APEX Programmatic Spectrum

I listened to a session by Joel Kallman, and found myself thinking hard about this slide on the programmatic spectrum, diving into the concept of “low code” vs “high control”.

(From around 14 minute mark

Such a good representation of the spectrum of APEX components

I guess you might say tasks in the yellow end take longer to configure, and are difficult to maintain, particularly for someone new coming in, for instance:
At the green end, defining Authentication is very click-click-click, done. No code required.
Around the middle, using Authorisation schemes can also be declarative, but with a little tweaking, can give a decent level of control.
And to the right, for Custom authentication, while a typical package may look similar to the next, it’s driven with raw PL/SQL, but gives the developer high level of control.

Some other interesting claims in the seminar include APEX meeting the NIST needs of cloud computing from 2002. Remember, we didn’t even have Google Suggest until 2005.

And an application exported from 2008 still runs in 2017, many APEX versions later, looking and behaving the same.
As a developer familiar with the aims of the APEX upgrade process, this does not surprise me at all. What I didn’t consider was how much of a feat this is in the world of development.

Pretty impressive when considering version difficulties I've heard with products like JDev.
I realise that's comparing apples to toaster ovens, but it's a high bar that's set. Onya APEX team.

And the IDE is delivered via a browser :)

Friday, 12 January 2018

Data driven APEX icons

We have an application written with a heavily customised Theme 25 built for 10" tablets, and we feel the Universal Theme justifies the move, in part because of the surrounding ecosystem. Check out this forum discussion on the topic.

We've been looking through the packaged applications for applied ideas, and using the Universal Theme sample application as a component reference.

I came across a requirement where we had a list of items that indicated completion level out of 100.
Then I thought about a group of pie icons I saw in Dick Dral's Font APEX icon reference, and knew what I wanted to do.

All I needed to do was round a bunch of numbers to the nearest 5.

To solve such as problem, I usually start by giving myself a bunch of numbers to play with
select rownum rn from dual connect by level <= 100

Then defining that as a WITH statement, so I can refer to the derived column as often as I like.
with nbrs as (
  select rownum rn from dual connect by level <= 100
select rn
 , floor(rn/100 * 20) / 20*100 rnd
 ,'fa fa-pie-chart-'||floor(rn/100 * 20) / 20*100 icon
from nbrs
where mod(rn,5)=0
And I used a divide/multiply by 20 math trick to do the rounding.

We can then feed the result as the relevant icon into whatever region template we need.

I like pie.

Simple, yet effective.

Tuesday, 9 January 2018

Modify OracleJET Property at Runtime in APEX

OracleJET has attributes galore, but some are are (not yet) available to change at design time, so JavaScript code can be added to the chart attributes to set relevant attributes.
function(options) {
options.styleDefaults.threeDEffect = "on";

return options;
See my previous post about modifying these attributes on render.
We can also do this at runtime, perhaps as response to a button click, such as the 2D/3D button in the cookbook.

First, set a static ID on the chart, possibly one of the most common "advanced" properties I use.

Static ID property available on many components

Use this ID in the browser console at runtime to see what JSON was generated for the chart definition.
Any missing properties will use the default specified in the documentation.

Browser Console results

The documentation on these attributes is thorough, but I'd love some examples to help keep me moving.

We can modify the orientation property by supplying a name/value pair as a JSON string.
$("#p95_skew_jet").ojChart({'orientation': 'horizontal'});
Note the selector has is the static ID with a suffix: $("the_static_id"+"_jet")

It took a little while to find the correct punctuation for the nested properties, so this is really one of those blog posts I created so I don't forget. You've seen Alex's blog by-line, right?
$("#p95_skew_jet").ojChart({'styleDefaults':{'threeDEffect': 'off'}});
Not all runtime tweaks behaved as expected, however. The following property behaves as expected when setting on render, but at runtime it squishes the width of the entire chart.
$("#jet1").ojChart({'styleDefaults':{'barGapRatio': 0.2}});

Refreshing the chart region afterwards did not help in this case.
While looking for answers, I came across this post from Riaz describing similar customisations.

OracleJET JavaScript Customisation in APEX

I've finally got some regular hands on a 5.1 instance, and the shiniest tool in the box for me is OracleJET.

Some months ago I spent a few days learning about OracleJET and the knockout framework with Chris Muir. I doubt I'd ever get to that nitty gritty, but it sure is handy to know some of the finer details now that I'm using them in APEX.

I wanted to have a play with the funnel chart. I took an existing query, and quickly busted out a working chart.

Upon playing with the sample chart in the OracleJET Cookbook, I decided I wanted the 3D option.
In the case of this chart style, I think the subtle effect made a big difference.

The Cookbook is a great guide to what you can play with
I couldn't find the relevant attribute defined declaratively in APEX, but that's fine - I know all of them aren't mapped, and we have a special JavaScript section in the chart attributes to help us customise the content. This expands upon attribute selections, not replacing them like the custom XML did for AnyCharts.

The attribute help in the Page Designer is a good start, and always worth checking when playing with a new field.

Page Designer Attribute Help

I noticed the Sample Charts application did the same thing, but I figured I could take this sample from the help, combined with information from the impressive JET documentation, and try work out the JavaScript myself.

Not all properties are mapped to APEX attributes

This is what I came up with.
function(options) {
  options.styleDefaults.threeDEffect = "on";
  return options;

Only to find the customisation from the sample application looked same same, but different.
function( options ){
    options.styleDefaults = {
        threeDEffect: "on" 
    return options;
Turns out both are effective, once again demonstrating there's always a few different ways of doing the same thing in JavaScript.
I suspect the first option honours the object notation mentioned in the documentation, while the second assigns the value as a JSON name/value pair. I've seen similar behaviour in jQuery:


You can find other examples in your workspace with this query on the dictionary view.
select application_id, page_id, page_name, region_name, chart_type
from apex_application_page_charts 
where javascript_code is not null;
You may also enjoy changing series colours with similar treatment by Colin Archer of Explorer UK.
The German APEX community also has a thorough rundown of JET charting in 5.1

In my next post I'll explore how to play with these attributes at runtime, not just on render.