APEX Component Settings for Switch

While I point out a 'hidden' setting for Switch items in APEX, I want to comment on two related item types found in Oracle APEX => radio buttons and checkboxes.

1) Radio button

I really think the standard Template Option for radio groups should 'Display as Pill Button'

It really provides a simple and effective UX when a small set of options are on offer. Touch devices can select the option as if it's a button, and mouse users only need one click, compared to a select list requiring two. And the click doesn't need to be precise for those tiny radio group circles. I wish the application builder adopted the pill button approach more often.

And it's real easy to hook a dynamic action on change of the radio group, perhaps to refresh a classic report with data limited to the selected option. (Don't forget to add page items to submit!)

2) Switch

The Switch item was the answer to a number of methods of deploying an "iPhone-like" on/off switch.

I actually wrote an (internal) item plugin to do just this, but I much prefer using the native option.

A frequent question relates to how switches are rendered. If you don't see what you expect, have a look in Shared Components -> Component Settings.

Application -> Shared Components -> Component Settings

Personally I prefer the 'Pill Button' look, for reasons above. I find the APEX Application Builder seems more washed out with all the Switches in the properties bar, compared to the more softened On/Off pill buttons. If I could change this at the builder level, I would.

The offerings will vary depending on your APEX version, and if the application's Universal Theme has been refreshed. 

Application level settings for plugins can also be found in Component Settings, in addition to built-in APEX feature tweaks. These can something you choose to review after an APEX version upgrade.

3) Checkbox

It seems the humble HTML checkbox will always be extant, regardless of the pain it brings to tabuler forms. However, it suffers the same precision requirement as the native radio group selections - though better deployments allow selection using the label as well.

Go with the Switch (as a pill).

What do you think?

Validate data type within SQL

For all those pushing data around, especially dirty data, this one is for you.

Today I was preparing to process data I loaded from a spreadsheet.
A simple filter was required - to ignore the header row, had it been included.

I'm lucky enough to be working on 19c, and I remembered that a reasonably new function should help me out with all many of data loading issues. With a quick scan of my favourite reference manual, I found VALIDATE_CONVERSION.

For example, this gives me 'ORA-01722 invalid number' because of the header row I failed to exclude.

select c.*
from my_data_load c
order by to_number(seq);

But without the to_number, the order returns incorrectly.

SEQ
-----
1
10
12
140
2
Order

Sure, we could say
where seq != 'Order'

But this tool will have more than one use

select c.*
from my_data_load c
where validate_conversion(seq as number) = 1
order by to_number(seq);

SEQ
-----
1
2
10
12
140

Recreate this result using

select * from (
select 'Order' seq from dual
union all select '1' from dual
union all select '2' from dual
union all select '10' from dual
union all select '12' from dual
union all select '140' from dual
)
where validate_conversion(seq as number) = 1
order by to_number(seq)

And see typical return values (0 or 1) for conversion attempts using

select
   validate_conversion('1' as number) num1
  ,validate_conversion('2' as number) num2
  ,validate_conversion('1b' as number)  num_not
  ,validate_conversion('01-01-2001' as date) date1
  ,validate_conversion('30-02-2000' as date, 'dd-mm-yyyy') date2
from dual;  

      NUM1       NUM2    NUM_NOT      DATE1      DATE2
---------- ---------- ---------- ---------- ----------
         1          1          0          0          0

It's one of a few tools I'm using to make data loading life easier, and processing data in sets using SQL, not looping & context switching within PL/SQL.

The kicker, turns out this has been available since 12.2.

It turns out the usage of validate_conversion in PL/SQL will give the compilation warning PLW-06009. And so does the alternative to check if this returns null:
to_date('z-z-2001' default null on conversion error, 'dd-mm-yyyy')

More examples available from
LiveSQL
Tim Hall
Oren Nakdimon
19c Documentation