Saturday, 21 September 2019

Change label dynamically in Oracle APEX

I was developing with Oracle Forms for an awesome project when I first heard about Twitter, and it was described to me by Jeff as a 'micro-blogging' site.

I think I did what could be my smallest blog post, as a tweet. A micro-blog, if you will.

Here is the snippet that dynamically updates a label, in this case, a floating label.
$("label[for='P1_NOTE']").text('Happy Friday!');

Which could also be written as
$("P1_NOTE_LABEL").text('Happy Friday!');

This could be executed within a dynamic action, as a result of a change to some field on your page, perhaps to help instruct the user.

Happy APEXing!

Wednesday, 18 September 2019

APEX 19.2 Dark Mode CSS Extension

There have already been some insightful posts about APEX 19.2 Early Adopter, plus an interesting feed of features on Twitter, as they're discovered.

Sven has a great summary.
Adrian comments on markdown.
Mike is really plugging faceted search on twitter. I particularly look forward to playing with that one.

I couldn't resist giving the dark mode a test spin.

I immediately experienced the same contrasting issues from the original clean skin that 5.0 gave us, plus a few minor adjustments.

Spot the difference

Tweaks so far include

  • Region header
  • Delta icon
  • Text field background
  • Scrollbar width
  • Save message location (not seen)

I'm still trying to decide what the best contrast is to break through the darkness on the vertical scroll, but it's a good start.

As Mike Hichwa suggested, the experts may give this a twirl in 20.1.

You can find the CSS I use in this Github respository, but switching between the two modes will require manual interaction with the style extender, since there are no dark-mode classes to utilise.

I'm trying to lift my game a little, get more organised, and improve my markdown game.

Thursday, 5 September 2019

Paste from clipboard in Oracle APEX

Since APEX makes it so nifty, I've got a few pages simply used as query tools - handy to verify data during development.

I also use these pages to experiment with UX, and recently I thought I could save myself pressing ctrl+v to paste my ID into a field for lookup.

I knew we had done something recently for adding content to the clipboard, so I figured there would be a way to paste clipboard content into a field.

Sure enough, Dr Google had the answers, though it's bleeding edge. Good enough to experiment with.

For me, adapting this response to APEX meant setting the provided field name.

I added this to my JS global declaration.
async function paste(input) {
  const text = await navigator.clipboard.readText();

  $s(input, text);
And this when I clicked the button to action the paste, I nominate the item I want the clipboard data to be copied into.

An on-change dynamic action on this field would add a member to the collection, and refresh a number of regions that query the collection.

Don't forget to add Page Items to Submit

I have an example here.

I thought I noticed a potential odd behaviour with this, possibly due to the async action.
Therefore I've created an example with a few regions, and I'll try throttling the connection to see if that highlights the behaviour.

And so now I have this as a handy reference, I'll demonstrate some APEX collections and regular expression behaviour while I'm at it.

Monday, 26 August 2019

Oracle APEX Radio Group null option fix

APEX 5.1 introduced a template option that transformed radio groups into pill buttons.

I love this concept, it makes for a pleasant UI - an easier target for mouse clicks and finger taps.
I liked it so much I tried to do this myself prior to 5.1.

In 18.2 (fixed in 19.1), there's an issue with the way the null option presents for these radio groups - it's not in the same row.

Marko mentions a CSS fix for this problem in his post, but I find it has some side effects.
Stretched items are no longer stretched, and it ignores the Number of Columns attribute.

I went to inspect the generated HTML, and compare it to the 19.1 working solution, expecting to find some difference in CSS, but I quickly realised the null options is just generated in the wrong level on the tree.

You can also test this manually by using the in the browser tool to drag/drop that element to its siblings in the grid-row.

This means the problem can be solved with JavaScript when the page loads.
$('.apex-item-grid > .apex-item-option').each(function(){
This JavaScript identifies any radio options that are immediately below the grid, instead of being within the grid-row. Then for each instance, it moves that element to the next sibling, which is the grid-row.

Using appendTo() will push it to the end of the list.

And this solution honours the stretch option.

Is there a better way?
Other than upgrade to 19.x ;p


jQuery Cheatsheet

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