Thursday, 22 August 2019

Customising APEX Validation Message

I'm an advocate for forum participation. This very post was a result of me learning something small just by watching the forums every morning.

In fact, quite a few of my posts spawn from forum activity, in part because future me can find my posts easier than forum discussions.

I wanted to share a collection of solutions for customising the notification message when a field is Required - turns out we have a few options.

Then I found myself playing with the Compatibility Mode - a feature that allows you to nominate specific application to behave as if they were on a particular APEX version.

This setting only relevant for specific, esoteric behaviours, but while I was playing with validation messages, I thought it would be worth including how things are different.

Application Attribute - Compatibility Mode

If I open Application properties, and set the Compatibility Mode to 5.0, then validations aren't tested until the page submits.

APEX 5.0 behaviour

This also demonstrates that if you use Shared Components => Text Messages, and translate one of the documented internal error messages, then you will see this translation in applications behaving has per APEX 5.0.

Application wide message translation

The translation also over-rides the data-valid-message custom attribute, as John Snyders suggests that's not coupled with translations.

data-valid-message="Customise me"

Message hack

The current (default) behaviour, appearing from 5.1, offers client side validations.
In this case we receive immediate feedback regarding the fields that are declaratively set to Required.
If I have a data-valid-message, it uses that.

Feedback before page submit

Note the translation haven't been applied, and a custom server-side validation hasn't yet applied.

If the first three fields have been satisfied, then the form will submit, and we can see any custom server-side validations.

Feedback after page submit

This means we can customise the message by creating our own server-side validation message - something we used to have to do before things got nice and declarative.

Server-side validation

So there are a some pros/cons for either of these solutions to customise validation messages, and it would depend on your APEX version. I wouldn't be surprised if this behaviour is further cleaned up over time.

See more

Basic demo
John Snyders - deep dive
Matt Mulvaney - other considerations
Nick Buytaert - the old way
Patrick Wolf - the really old way
Compatibility Mode - 19.1 Release Notes

There used to be a great article regarding compatibility mode in the German community, possibly by Carsten, but I don't think it's available anymore.

AUSOUG Connect 2019

Australian Oracle User Group (AUSOUG) is holding their annual conference series in Perth & Melbourne this year, again this year, and I'm lucky enough to be at both events.

If you can, hold these days aside for Connect 2019, meet an interesting mix of people, and talk with them about your experiences. Seeing & hearing what other people are up to can be invaluable.

  • Monday 14th October – Rendezvous Hotel In Melbourne
  • Wednesday 16th October –  Mercure Hotel in Perth

I'll be enjoying sunny Melbourne the weekend before, then flying back home for the Perth event.

Looking at the programs, I see a nice list of international visitors to compliment our ripper Aussie line-up.  This includes, and not limited to

On the flip-side, currently I'll miss out on seeing both Guy & Connor due to scheduling conflicts. I know I can find Connor at the bar later, but Guy has a flare for infotainment.

And if I'm not mistaken, Groundbreaker & ACE Program Director Jen Nicholson will also join us down under.

Oh, and this doesn't include all the amazing delegates, who always have a tale to tell, even if it isn't up on the stage.

Tuesday, 6 August 2019

What version is my RAD stack?

A common follow up clarification on forums is regarding the version of the relevant tool.
Questions relating to Oracle APEX could be impacted by the APEX version, the database version, and perhaps the ORDS version - in addition to what browser is being used.

The information on the RAD stack can be resolved in one (concatenated) SQL query.

APEX was easy, there is a simple one row view, which ultimately translates to a function returning a literal.
Same with ORDS, I didn't even bother with a scalar subquery when I put it all together.

The Oracle database version had a few options, each with nuance.
  1. v$instance is not available to everyone
  2. dbms_db_version package variables are not accessible to SQL, unless you use the WITH clause, which is only available in 12c. And I'm not going to write a query based on all_source. It also doesn't provide the version granularity I expected.
  3. v$version returns a few rows in the Express Edition. In Enterprise Edition, it is a different "banner_full" column that contains the dot release you really need - among other data
    Oracle Database 19c Enterprise Edition Release - Production
  4. I hadn't heard of product_component_version, but it's a sys view that does all the string processing you need on v$version, but has the same XE/EE column differences. It also only returns one row when not in Express Edition.
So I ended up with the following, executed here in Express Edition.
col apex_version format a15
col ords_version format a15
col db_version format a15

  (select version_no from apex_release) as apex_version
 ,ords.installed_version                as ords_version
 ,(select version /* column name 'version_full' in Standard|Enterprise Edition */
   from product_component_version
   where product like 'Oracle%')        as db_version
from dual;

--------------- --------------- --------------- 
But then I decided it would look better as rows, executed here on an Enterprise Edition database.
col tool format a10
col version_no format a20

select 'APEX' tool, version_no from apex_release
union all
select 'ORDS', ords.installed_version from dual
union all
select 'Database', version_full /* column name 'version' in XE */
from product_component_version
where product like 'Oracle%';

TOOL       VERSION_NO          
---------- --------------------
ORDS       18.4.0.r3541002     
APEX and ORDS version is also available form within the App Builder, under Help -> About.

APEX Help -> About

I was curious as to how the WITH function would look, though I had trouble executing it.
with function ver return varchar2 is
    return dbms_db_version.version || '.' || dbms_db_version.release;
select ver from dual;
And it returns 18.0, when I expected the 'version_full' value of 18.3.


Forum question - How to check the database version
Marko - What is my current ORDS version
Martin - What is my current APEX Version

Tuesday, 23 July 2019

Oracle APEX Challenge Coin

Earlier this year, APEX technologist Adrian Png introduced an initiative involving an APEX Challenge Coin.

Reading up on the history behind the concept is rather interesting, with a strong military background. Something to create a sense of team bondage, to improve morale, and a way to determine who's round at the bar.
Though I couldn't help but think of two pop culture references - loosely in Pulp Fiction, and gruesomely in John Wick.

A lovely piece

The sequential numeric on Adrian's coins has no published meaning, though I know Shakeeb received coin suffiex with 42. There is talk of adding a recipient list to

Sitting among my laptop stickers

I humbly accept coin A0045 from Juergen, and it's an impressive little piece of metallurgy.

I consulted an ASCII table, and 45 could represent "E", "-", or "%", should it be dec, oct, or hex. Or it could just be part of that algorithm that produces alphanumeric keys that I've seen in the sample APEX applications.

Wiki also tells me 45 is the atomic number is for rhodium, some noble metal.
And it's a 'triangle number', because 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 = 45

And a frequent sight in our night sky, Pleiades is Messier object 45.

Anyhow, more community spirit from an already welcoming community


Monday, 22 July 2019

Kscope19 - What's not in the brochure

Kscope19 was my second Kscope.
It's a long way, but the trip is worth it.

Sure, we can all see the session list.
Delegates all see at least 5 sessions we'd want to see on each timeslot.
Those unable to come weep at the delicacies they are unable to see first-hand.

But what about what isn't in the brochure?

People often comment about the benefits of conferences beyond the seminar room. Let me tell you a story.

Well... turns out parts of this story actually is in the brochure, but some details made these examples particularly special, more than just an unexpected vendor schmooze.

Also remember, this was like a choose your own adventure. Like the session times, there are often concurrent activities to choose from, often aligned with the APEX/Database/EPM tracks.

Friday - Travel day

I left Perth on Friday morning. I thought if I landed Friday evening, I could fool my body into thinking squeezing more hours into a day was a survivable technique.

3 flights later, ~21 flying hours, I'm in Seattle. 4 hours late, check in not long after midnight. Food or bed? Bed won.

Saturday - Recuperation day

I spent the morning walking down to the famous local market on the pier, get a feel for the area, and treat myself to some local food. I haven't really seen mountains before, so I was hoping to experience a scenic flight over Mt Rainier and Mt St Helens, but unfortunately it was cancelled due to cloud cover.

So instead I had a few looseners with Connor, before registration in the afternoon. A couple of Perth lads who find themselves in Seattle at the same time.

[In brochure] Seems good to get registration out of the way Saturday afternoon, instead of Sunday morning. I see a few people I haven't seen since my last Kscope 4 years ago; and meet a few I've also met online - match some twitter handles/faces to actual human beings.
After a few awkward greetings, people are made aware of an APEX meetup in a nearby establishment for dinner & refreshments.

The APEX Meetup provided a few lubricating ales, and a darn good curry. The ice was broken, and a team of APEX groupies from around the world were primed for a non-stop conference. People gradually dispersed, and it was time for bed.

Screw you. jet-lag, I do what I want.

Sunday - Symposiums

[In brochure] Sunday symposiums. If there is one day you can do at Kscope, do this day.

Here's where I made an ironic rookie mistake. I thought I'd attend the newbie information session, making sure I was getting full value out of Kscope, and not missing anything obvious. Turns out I forgot receiving an invite for drinks at a speaker reception on 35th floor, held at the same time. Not in brochure, just a card my jetlag ignored during registration.

[In brochure] Welcome Reception, first chance to really explore the exhibition hall.
[In brochure] Geek Game Night - popped in for a bit, but not quite the mood a small group of us were chasing.

But we found ourselves back at the pub we were at the night before, making more conference friends. An amazingly diverse group of people, and the conference hasn't even officially started.

Monday - Bring it

Again, while the first two items were technically in the brochure, there were a few surprises I was not expecting... hence... they.. were.. surprises.

[In brochure] Keynote Session.

I appreciate magic, and the role it plays in critical thinking. I'm not so much a fan of the flashy illusionists, so I wasn't sure where Jason Latimer was on that spectrum.
I really appreciated the message he had for our society, looking to inspire wonder in children with STEM. Honestly, I was moved - I'll be looking to consume more of his content.

The facial expression says it all. Not magic, just cool physics, and talented slight of hand inspiring wonder.

[In brochure] APEX Demo night

Firstly, we had an announcement from Maria Colgan, stating APEX is now part of the autonomous database. At first I felt like Roel, thinking big deal, but over the week it become very clear that it was more than just its presence - Oracle really are promoting Oracle APEX is the development tool you need in the cloud to build your own solutions. It's mentioned on Oracle's home page, Lauren Cohn was recording testimonials illustrating how APEX helps rock our world, and we're seeing it permeate the media, though still in a sponsored manner.

Secondly, we had an APEX app for with something to demonstrate could sign up. Of course we did.
Not only that, instead of first come first served, audience members could pledge a donation to their demo of choice. These donations will go towards ODTUG fundraising for Pike Street Markets food bank. I was pleasantly surprised to see Connor & myself at the top of the list.

Mine was a demonstration of the monitoring page I regularly use, as I've raved about here.
Connor put an APEX UI over a PL/SQL engine for a chess game.
The other demonstrations got many of us thinking about the relevant concepts shared. What an engaging event.

No need to stop there, a sponsored event is afoot. Not only do I see a bar, and some hot food, it's in a pool hall! I'm in my element.

After a few chats with people from all around the world, I finally unleash a few rounds of pool. Apologies to the fella I knocked out while flexing my pool legs, but I was itching to get some nice clean shots in - it had been a while.

A deep conversation about humanity and the pursuit of technological & cultural advancement later, and it's time for bed.

Tuesday - ACE Day

For me, Tuesday turned out to be an "ACE day".

After my first talk, I had a rare hour downtime where I didn't mind having a break and missing the next set of talks.

Instead I made time at the exhibition hall to do a couple of small recordings with Bob Rhubart for the Oracle Groundbreakers.
First I did at "Two minute tech tip", and didn't even need the full 120 seconds to rave again about APEX workspace activity logs..
Bob also interviewed me about APEX, but I'm not sure those are available yet.

I think this was also when I spent 5 minutes with Lauren spruiking the benefits of Oracle APEX - I particularly look forward to these, though the cameras were a little more imposing than I expected, compared to our initial conversation. I'm possibly not made for TV.

I was humbly invited to participate as a panellist at the APEX Lunch and Learn. I found myself next to giants like Scott Spendolini, a smooth talking sponsor of many of the evening soirees; Dimitri, founder of AOP; Peter, founder of FOEX; Vincent, the PWA guy; and me. I felt a little out of place, but I think the panel members were also conduits for the entire audience. There were plenty of individuals in there capable of answering the interesting questions raised, and some of them certainly did. You can also find these types of people on the forums, it would be great to see more participants.

Tuesday evening started with a dinner for the ACEs, giving us the opportunity to converse face-to-face, since we're all actually in the same place. And despite what you may think, conversation is rarely about tech. With such diversity around the table, we couldn't waste it with what we've been talking about all day!

At 44°N, Seattle surprised me with the amount of brightness at 8pm. Apparently the next opportunity that beckoned was another sponsored event at a local hangout called Spin City - an underground place full of ping-pong tables!

So there I found myself, playing pong with Steven Feuerstein - and the night has yet to find the most surreal moment.

After a getting it out on the ball for a while, I found a drink, a chair, and someone new to talk to. Turns out she was a local of Seattle, and part of the APEX development team. A distant traveller meets conference local. What a hilarious yarn.

At some point we start to get ushered out of the establishment, and the local suggests "let's go to my apartment". After multiple assurances to the question "are you sure?", and a few head counts to ensure nobody was left behind, we found ourselves with tremendous views of Seattle from the common area on a 40th floor. Soon followed with pizza & booze, and blurry photos.

Thanks Jason
Now that was surreal.

Day 1 completed with a game of pool with the German FOEX lads.

Wednesday - Party Night

A plan made the previous night was the play hooky on the first session and go to breakfast at a great local eatery. Best darn eggs benedict I ever had, washed down with the best darn orange juice I ever had. My companions had a combo that involved the fruit bar, where the fruit looked picture perfect. Thank you to the Oracle dev team for that meal, and more conversations that weren't about Oracle.

Note to self - ensure you have enough battery power during your talk.

[In brochure] KScope Party
A few more sessions later, and it was time for the party. The ODTUG Kscope Wednesday night event is not just any event, they really go all out. When I found out they encouraged fancy dress, I got my procrastinating bum into gear and spent about $40 of my colourful Australian dollars on what I thought was a comfortable, but fun pop-culture costume choice - Forrest Gump.

How's this accent?

The cover band were sensational, wandering through a museum of pop culture was a treat, and the dance floor was thoroughly lubricated. The after-party was too many people in such a small place for my liking, but the security check at the door gave me a laugh when turning my licence upside-down to check authenticity.

 And a sensational way to release the brain fry that was the previous 4 days.

Thursday - Not quite over

Yes, there is still half a day to go, and a chance to have the afternoon to soak up the local city.

[In brochure] The final morning was also live streamed, and I liked the format of having multiple people covering the essentials of their niche topic.

There is also a closing session where a few awards are announced, and you see further evidence of a clearly successful succession plan the ODTUG crew have for future board members, to ensure the quality that is the Kscope conference is sustained. Colour me impressed.

I'm sure this conference is like no other, and I'm thankful for not only the opportunity to speak, but to be dedicated participant for every waking hour. I've got 25+ hours to sleep on the way home, right?

I had the afternoon to kill, I ensured my flight times facilitated me a little more of Seattle, to help make up for not getting to do that scenic flight.

I walked to the Space Needle. I figured I couldn't be a real space nerd without heading back to take a closer look. The local food court seemed progressive, and inclusive, lunch was awesome, and I explored the Chihuly glass garden while I waited for my ride up the needle. The garden was stunning, not only visually, but the smells of the flowers, and the sounds of the local buskers mastering their stringed instruments.

I needed a few more of my Australian dollars in the souvenir shop on the way out...

Finally, it was time for my journey to the airport, where I managed my first bit of catch-up sleep.

Sydney fog delayed my arrival home with a lovely diversion to Brisbane. I was due to arrive before the local footy game began, but alas. At least I wasn't travelling with a child this time.

For those fiscally minded, I have three comments

  1. Submit an abstract. Connor advised me many moons ago to start speaking, "it will take you places". You no longer have to convince someone to pay for your entry. The need for diversity is everywhere, so you'll always find new speakers. An estimated 42% of Kscope19 speakers were new.
  2. While my own boss was sponsoring my flights, accommodation, and meals, I barely paid a thing in between land & leaving. On the rare time Kscope, or one of its generous sponsors, or an Oracle credit card wasn't paying, it was hard to grab the tab.
  3. The value of the conference is beyond the entry ticket, beyond the sessions from a plethora of passionate subject matter experts, many of which are particularly talented presenters, but also in the people meet; the conversations you have; the problems (and solutions) you all share. Finding normalcy on the other side of the world.
    Also, how often am I going to find myself in Seattle? Or Boston, Nashville? A life experience in itself.

So, there were 7+ hours worth of sessions, and there is also 7+ hours worth of networking each day, if you can eke it out. Difficult to put a true value on it, but personally meeting people and having normal conversations certainly seems to enhance the nature of future online interactions.

Presentations I didn't see, or too dazed to properly comprehend at the time? They are recorded, and will be available after post-production.

Boston? You betcha, I've got abstract ideas already to help make it happen. Probably with family this time. Travelling with a 6 year old should be a breeze, right?

And choosing between sessions is hard, how am I to choose from all of this?!

Thursday, 18 July 2019

Lessons from Kscope19

The vague afterglow of Kscope continues. After the long trip home, I had a few days to give the brain a debrief, make some notes & ideas, before heading off for a family holiday.
Now after a Strange Things 3 binge, I wanted to finally bust out some posts I've drafted on Kscope.

I had the privilege of two sessions at Kscope19, and I also found myself sharing the APEX Lunch & Learn panel with some gurus.

Thanks Jayson

While my sessions are available in PDF format through the conference app, the format doesn't translate well. like any Connor PDF, so below I have links to the original format

I've been using Prezi for a while, but I try not to be the nausea inducing Prezi, rather a controlled Slides type, with room for emphasis. I might give Slides a go for next year.

Oracle APEX for Forms Developers

Lesson 1 - Length. 
I knew it was long, I thought I could still create a fast paced morning. Too much content. I know how I need to cut, but doing is always a different story.

It's such an interesting topic, with many ways to approach the transition.

Managing Multiple APEX Applications

Lesson 2 - Preparation
As a speaker, I always have various contingencies in place. For instance, I have my presentation on a thumb drive, and it's also available via URL. I also have spare batteries for my clicker.
When I landed in SF I realised I forgot my power adaptor. The hotel kindly sorted me out on the first night, but sure enough, 5 minutes before I start, I realise my foreign power adaptor was a good 10 minute brisk pace away. 1 hour reported left on the laptop battery, while the CPU fan was busting out decibels? Nice try, how about asking any audience members for magical adaptors before attempting to start?
(Thanks again to the gentleman that sorted me out near the end)

Lesson 3 - Organisation.

It's not all about adequate amperage - I have good content, handy snippets, useful sample applications - that all should be arranged in GitHub. Gotta get organised.

I also have word documents for each presentation that I turned into a PDF for the conference app.
PDF? It's 2019, I should have this on GitHub, with some Markdown cheatsheet stuck to my monitor with my conference swag.

Lesson 4 - Serendipity

Debriefing Kscope has spawned new ideas.

I'm aiming for Boston, and I also like to aim high.


Wednesday, 24 April 2019

Highlight 'current' card in APEX

Earlier today I posted a solution that required a bit of jQuery to execute after refresh of a report.

Avoiding post-processing is always nice, so here's an example that is resolved during render.

I would like to highlight the 'current' card in this classic report:

Cards Classic Report

The SQL that dynamically defines these cards includes the following column, with some decider over which record is deemed the current selection.
,case when :P17_ITEM = 'SOMETHING' then 'is-active ' end AS card_modifiers
Then we can include some CSS relevant to that card group, identified here by Static ID 'my_cards_r'.
#my_cards_r div.t-Card
  {background-color : green;}
This solution should also reflect appropriately after any refresh of the card region.
If you define a declarative List region, it also uses the is-active class when the list entry is current, so this CSS could also apply.

This is different to the #CARD_COLOR# substitution string, that drives the colour of the circles where your icon/initials are.
Here I used:
,'u-color-12' card_color
Do you have a preferred solution?