Friday Funny - Festival of serious yahoos

I don't care what you all think, this is my blog, and I continue to laugh at this SQL joke.

select column_value
from apex_string.split('YAHOO~SERIOUS~FESTIVAL', '~')
order by dbms_random.value

If you're a Simpsons fan, you may recall this clip

Yahoo Serious Festival - The Simpsons

If you're not aware, Yahoo Serious is an Australian actor from the movie of the same name. He made it onto Time magazine, then nothing.

But how nifty is that apex_string.split function? And it's cousin join?
Although recently I saw Connor use what appears to be a native option, one that doesn't require an APEX package. On the blog topic list...

Where would we be if we just believe?

As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. Patrick Wolf summarises it well (sorting, XSS, context switching).

I've been curious about this for a while myself, and what better way to verify the truth than do some science ;p

I thought about the recent experimentation I did with interpreted code, and recent client work I've done with bind variables, so I thought I'd not only compare timings with a simple test harness I use, but see what v$sqlarea had to say.

When considering what SQL to compare, I decided to use the simple bit of SQL I used for my AskTOM Office Hours demo app. I saw how quickly the embedded HTML expanded to fit repeated business rules, and why not use something simple. If any difference was to be seen, let's see if it shows up with something basic, just like Juergen suggested.

The first query is just a simple query on scott.emp, with an extra expression to determine if the row is 'special'. This information is used declaratively within APEX, so these calculations are absent from this testing. The complexity has been transferred, but simplified. That's the reason it's best practice.

cursor c_1 is
select /*+ qb_name(c1) */ empno, ename, job, hiredate
  ,case
   when hiredate < date '2000-01-01'
     then 'special'
   end last_cent
from scott.emp

So, let's just concentrate on the differences in the SQL we can measure.

The second query has the HTML tags embedded, repeated on columns where I substituted the column into the class attribute in the previous example. The code required expands quickly, hence the argument to keep the SQL and presentation layer separate.

select /*+ qb_name(c2) */empno
  ,'<b>'||ename||'<b>' ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||job||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||job||'</span>'
   end job
from scott.emp

The third query introduces context switching to PL/SQL due to the inclusion of htf.bold and apex_escape.html. This protects the output we'd otherwise have escaped using the relevant property. Or you could mitigate usage by sanitising data on the way in.

cursor c_3 is
select /*+ qb_name(c3) */empno
  ,htf.bold(apex_escape.html(ename)) ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||apex_escape.html(job)||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||apex_escape.html(job)||'</span>'
   end job
from scott.emp;

If we just compare throughput, there is a clear loser. Context switching between SQL & PL/SQL is just too much, though I would like to think in recent versions of the database, those functions could be enhanced with such devices as the UDF pragma?

iterations:50000
     3.98 secs (.0000796 secs per iteration)
     5.16 secs (.0001032 secs per iteration)
    33.09 secs (.0006618 secs per iteration)

I just run these queries lots of times to measure throughput. Tom Kyte wrote a more enhanced test bed called runstats, if you want juicier details.

-- and start timing...
    l_start := dbms_utility.get_time;

    FOR i IN 1 ..c_iterations
    LOOP
     for x in c_1
     loop
        null;
     end loop;

    END LOOP;
    l_end := dbms_utility.get_time-l_start;
    dbms_output.put_line( TO_CHAR(l_end/100, '99990.00') 
                       || ' secs ('||(l_end/c_iterations/100) || ' secs per iteration)' );

What about v$sqlarea?

select substr(sql_text, 1, 22)sql, fetches, parse_calls, buffer_gets, sharable_mem
   ,persistent_mem, runtime_mem, user_io_wait_time
   ,plsql_exec_time, cpu_time, elapsed_time, physical_read_bytes 
from v$sqlarea  
where sql_text like 'SELECT%SCOTT.EMP%'
order by 1

v$sqlarea results

These statistics validate the timings, and I think also validates this as a performance tip that belongs in Michelle's list.
While performance difference with/without HTML may be marginal in this case, the supplementary benefits make it clear best practice.

Include new APEX templates in an older APEX instance

Have you seen that super awesome theme in the new APEX version, then wondered how long it will it be before your site upgrades so you can actually use it?

What if I told you that your current version could be retrofitted to use that template?

I really like the look of the Content Row template, I think that's going to serve many developers good purpose.
Sure, I could create something similar now, but if I do as much as I can the same, then it should grease the wheels come upgrade time.

Content Row 18.2 vs 19.2

To make this happen, I

1. Downloaded APEX 19.2
2. Copied the /theme_42/1.4 folder to my 18.2 instance
3. Created the report template, including template options
4. Added the region component in my app

The first two steps are all about making the relevant CSS available to your instance.

After I had the /1.4 folder in place, I thought I would need to increment the Theme folder, but that just introduced other issues.

Instead, I just referenced the ContentRow.css file in the pages I wanted to use it.
This is the path where you can find it, so it can be placed wherever you like on your older instance.
/i/themes/theme_42/1.4/css/core/ContentRow.css
Alternatively, you could include this for all pages in the User Interface attributes.

Then I needed to create the named column (row template) in my 18.2 application, details of which I just copied from a 19.2 instance.

Content Row syntax

I can even transfer the template options, so I started transferring from details on the page
You can also use this query to find instances within my 19.2 application.

select name, display_name, display_sequence, css_classes, group_id
  ,(select display_name
    from APEX_APPL_TEMPLATE_OPT_GROUPS
    where template_opt_group_id = group_id) group_name
  , help_text
from APEX_APPL_TEMPLATE_OPTIONS 
where application_id = 32532
and report_template = 'Content Row'
order by display_sequence

Then I created the template options in 18.2.

Add Template Option

And sure enough, they become available on my 18.2 region component.

Page Designer outcomes

Due to the differences in availability of template option groups, some manifest a little differently, but I personally prefer a one click checkbox to the more fiddly select list.

All that said, I'm not sure I'm entirely sold on the small screen format, though maybe thing will change when I get more realistic data in there.

Content Row - mobile

Now I'm sure when I change over to use the actual template when I'm in 19.2, I'll need to re-apply the template options - but Shakeeb likes the idea of retaining shared template options, so you never know.

This was all done in 18.2. If you're on an earlier version, your mileage may vary.

If you're not using the Universal Theme, then you're probably not going to get very far.

Interpreted code in APEX

A few years ago I posted a comparison between plugin code left in the source attribute, vs code that has been transferred to a PL/SQL package.

In the interests of good science, and I wanted to chat about it at next week's Office Hours, I wanted to repeat this test.

I had a little difficulty working out how I got the metrics, I think APEX debugging has changed a little since I ran the test. Instead I considered looking at v$sqlarea to assess performance.

Turns out I quickly found the relevant queries using the following SQL, which a case statement to help me identify the difference between rows each time

select
  case
  when sql_text like 'begin declare  begin wwv_flow_plugin_api.%' then 'API'
  when sql_text like 'begin declare function x return varchar2 is begin return null; %' then 'call dynamic'
  when sql_text like 'begin declare FUNCTION enkitec_sparkline_render%' then 'parse dynamic'
  when module = 'SQL Developer' then ' me'
  end which
  ,executions
  ,loads
  ,parse_calls
  ,disk_reads
  ,buffer_gets
  ,user_io_wait_time
  ,plsql_exec_time
  ,rows_processed
  ,cpu_time
  ,elapsed_time
  ,physical_read_requests
  ,physical_read_bytes
  ,lockeD_total
  ,pinned_total
  ,sql_text
from v$sqlarea
where sql_text like '%sparkline%'
order by which

When APEX invoked the code using an API call, it looked like

begin declare begin wwv_flow_plugin_api.g_dynamic_action_render_result := apx_plug_sparkline.render (p_dynamic_action => wwv_flow_plugin_api.g_dynamic_action,p_plugin => wwv_flow_plugin_api.g_plugin );end; end;

When APEX needed to parse the entire function, it looked like

begin declare FUNCTION enkitec_sparkline_render ( p_dynamic_action IN APEX_PLUGIN.T_DYNAMIC_ACTION, p_plugin IN APEX_PLUGIN.T_PLUGIN ) RETURN APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT IS

To do this test, all I needed to do was paste the PL/SQL back into the source attribute; I didn’t bother changing what was invoked in the callback fields.

After 50 page refreshes each in dev – parsing the code was at least twice as slow, based on CPU time.

I suspect the 'call dynamic' was the builder validating the code.

Basic glimpse

In an environment with more activity, I was able to compare the standard API call with a few hundred that included parsing the code.

Click/tap to embiggen

If I take 141312 CPU time, divide by 301, then multiply by 17778, I get parsing around 1.8x the amount time as using the API.
The same goes for elapsed time, while the PLSQL Execution time remains the same.

Plus there’s disk reads, an obscene amount of buffer gets, considering the execution ratio.

I tried a second plugin (nested reports), and while the CPU ratio seemed the same, the physical reads were over twice as high.

Remembering this is just placing the code in the box – I haven’t even referenced it.

Too many words and numbers? How about a graph.

If this seems a fair reason to reduce the amount of interpreted code you have…

Twice the work, for nothing.

... then how does this make you feel?

What's a buffer, and why do we want to get it?

It seems by removing the code from the source attribute of the plugin, we measurably reduce the amount of work the database does. Imagine if we reduce our PL/SQL usage throughout the application?

So I conclude

1. Use bind variables
2. Put your code in packages

On the PL/SQL you don't write when using APEX.

Fancy joining in on a discussion with PL/SQL and Oracle APEX community members from around the world?

I'm honoured join Karen Cannell and Scott Spendolini, to be hosted by Steven Feuerstein in the next AskTom PL/SQL Office Hours on December 3, 2019.

It seems a few people haven't heard of these "office hours" sessions, but they're worth a go - more than just your average webinar. And they're all recorded for later viewing.

Topic

This is just the PL/SQL sessions! Every month they have one for Oracle APEX, SQL, Spatial, JavaScript, JSON, Database Security, and even more.

Product segments

It's probably one of the richest learning resources Oracle currently produces, and I really need to tap into more of it.

If the timezone gods allow it, I recommend live participation, as you really do get your questions answered. The produce managers handle the chatline, ensuring questions get followed through.

I think Steven's ensuring we have a good 15 minutes for questions, because they always come through. They keep the chat transcripts, too. The one for Oracle Forms Modernisation was all about the questions!

And it's free - you only need to sacrifice your email address, though it's probably the best email feed I have. Concise, infrequent, except my forum feed, of course.

On December 3, in my 15 minutes of fame, I plan to (spark debate?) about "the PL/SQL you don't need to write". I delivered this as an APEX application, instead of whipping out the ol' powerpoint.

Join in next week, and Karen will start with some decent habits, and the other Scott will no doubt enlighten us about security, or some such.

Free training - from Oracle... well, in this case, delegated to some passionate volunteers. ;p

APEX Low code expressions

The following where clause expression would normally return false, therefore the query would deliver no rows.

select * from dual where null != 'X'

no rows returned

A null can never equal a value, and hence cannot be determined to be disimilar to another value. False is the expected condition.

Turns out when using it as what appears to be the  declarative low code alternative, it returns true – and renders.

So if you want to display something based on a field that may have a value you don’t want, eg:

This will display the component if P10_NULL is anything but "yuck", including a null value.

In my business case, there was an occasional value of "D" that I need to hide things for.

Instead of using (slower) PL/SQL expression
nvl(:P42_CAT,'x') != 'D'

or SQL expression (anything but = D)
lnnvl(:P42_CAT='D')

As Kim suggests, our brain is seeing something SQL-ish, when really it's a low code translation that probably nails most scenarios - and maybe it would be better re-worded slightly as "not equals"

Our low code alternative is not only faster, but simpler. It turns it into just another if statement, as opposed to a snippet of PL/SQL that's interpreted on the fly, which is why dozens of global page conditions using PL/SQL expressions will slow your application.

Tom Kyte's mantra was to aim to reduce to just a single SQL statement (or no SQL at all).
Perhaps APEX developers should advocate for declarative attributes where possible, only then maybe try an expression or query.

So you want to submit a conference abstract?

It used to be our annual Perth conference that got me thinking about abstract ideas, but now it's the Kscope abstract submission deadlines that get my boat rocking.

But every day is a good day to think about the topics I'd like to might like to share with to your local community.

I've also been trying to convince a collegue to do their first presentation, and the questions in the ODTUG submission page got me thinking, so I thought I'd share some of those thoughts.

So here are some of the questions in the Kscope20 abstract submission form.

This is arguably the best conference for Oracle developers, and we only constitute a portion of delegates! I selected the APEX Track.

But the Session Title is obsiouly a good place to start. It needs to be catchy, concise, yet inform the reader as to what to expect - even without reading the abstract itself.
What session headline would grab you?

I like the descriptive distinction between HOLs and Workshops

The title can lead to focussing your content, which is necessary. Scope creep is something that's been on my back these last few years, and a good title can nail a good portion of this down. The Perth conference form asked for 3 objectives, which is an awesome way to focus scope. (I don't think this should be confused with Martin's concept of the evil threes)

Searching for my first presentation topic took some time. Something I felt experienced enough to talk about, something that felt relevant, useful to others. Now I have such a large list of ideas splattered on a Trello board, if anyone is thinking about doing their first presentation but can't think of a topic, I'm sure we can eke one out, even without necessarily knowing your skillset.

The Session Type can also be a prompt for ideas. Presentations can often be categorised by style and format, something Penny & I explored one year in a session of our own.

Maybe you have a collection of related tips, or you have a case-study to share where successfully utilised a nifty feature? Or some feature people haven't been talking about enough?

There is also a Sub-categorisation that varies by track, and helps align the content to relevant product sub-sets. There's an impressive amount of detail on these here.

Analytics / APEX / Database / Modern App Dev / ...

Naturally, at some point we need to write the abstract. The few paragraphs that inform the delegate about the story you're delivering. I've been stewing on mine for a while. Write a draft one night, review it another. What's being taught? Is it new? Is it compelling?

But the abstract is also helping define your scope. A simple aim, an interesting insight, and an awesome outcome.
What were your three main objectives? That's 10+10+10 minutes, with a few on each side - bang, you've got your talk. Now to divide & conquer. But remember, 10 minutes can disappear real fast.

Make sure there's no boasting or selling going on, that can be left for the notes for the review committee. I like this distinction. The Session Summary educates the delegates what you're talking about, the Notes educate the committee as to why you're gone with this topic, any maybe do some selling (of yourself) there. I also included my 3 session objectives, for a concise perspective.

For everyone, then for the selection committee

And don't forget to check grammar and spelling!

Who's on first? What's on second?

Clearly someone needs to be written as the primary presenter, but do you have a friend?

Seriously, you may have a colleague that contributed just as much to the topic, why not present with them? Well, there's probably a hoard of reasons, including the fact they may never have presented before - but if you're up for it, mentoring is rewarding for all parties. And starting to present can change your career trajectory.

Is this good or bad?

This first question about having presented to a live audience before gives the reviewers some context regarding your experience, but this year, after just over a decade of presenting, I got a different perspective on the second question.

"Over a decade" sounds a little too grandiose. I pick one, sometimes two topics a year, and present them at our local conference. Sometimes I might do one twice, perhaps in a second city, or at a local meetup. I don't get much re-use.

However, some conference regulars will use & refine the same session numerous times a year. I had the opportunity to do my APEX for Forms Developers session 3 times, and it wasn't until the third that I really felt I nailed it. I had trimmed enough chaff, reordered the odd slide, integrated feedback, and neatened the story. And this is even with practice sessions under the belt, but Martin's recent post on controlling the presentation monster was a good kick in the behind for some aspects of my preparation that has been lacking in recent years. Perhaps I've become a little complacent.

As for the "Have you presented this session before" question, initially I thought it was a virtue to consider my presentation as 'fresh' for Kscope, something other people haven't had the opportunity to see yet. Perhaps I'd forgotten, because I certainly valued doing my inaugural Kscope session in Perth first.

(There is a 90 minute option)

Session Length is typically one hour. If this seems long, don't worry - it can be filled. And as it turns out, trimming a 60 minute presentation that was already a tad long into the Australian 45 minutes took a lot of cutting. But good cutting, like cutting superfluous code.

And it reminded me how good it is to get a targeted, concise presentation topic.

I realised my second abstract could also work as a 30 minute Workshop, so I added to the review committee notes, and maybe improved my chances a notch. My fourth submission could also work as a fully fledged hands on lab, so ... why not?

There are a heap of regular speakers at these gigs, but as Opal points out, there are a good percentage of new speakers every year - even at a high demand conference like Kscope. .

And it's not just Kscope - there are a wealth of conferences around the world, ready to listen to what you have to say. Check out the call for paper schedule at Clocwise (built with love using Oracle APEX).

There are a few other questions on the form, and plenty of other considerations when it comes to submitting abstracts, but these few stood out to be and I felt compelled to share.

I'd also add that there are so many quiet achievers out there that have expert knowledge of various features, they just don't blabber on blogs about it. We've got to find those pearls.

Also consider taking part in the public voting for Kscope abstracts. It's an interesting way to get a feel for what a good and bad abstract looks like, and may inspire a new range of ideas.

So bada bing bada boom, once you have your slides nearly done - practice practice practice, and have a listen to this sound advice from Connor - it's a great help <ahem>.

• Honesty - I've heard this from Connor for years, it's so darn true.
• Expert - we're all subject matter experts on something.
• Love all the things - love the topic. love being there to share.
• Practice - matters.

I was lucky enough to have as an instrumental guide on my path to speaking. I've heard these messages in various forms over the years, and he really hits the nail on the head.
And now he's being an instrumental advocate, providing various levels of support for Oracle developers around the world - not bad for just a tech guy.

Seriously though, he will help you out, and you won't feel like you need to write a 400 slide monster.

"Connor, your deck is 90 slides."

"Yeah"

"But you've only got 5 minutes"

"Yeah"

"..."

Things I learned at a user group event

Yesterday I attended a friendly, informative workshop by Shakeeb Rahman and Christina Cho, members of the Oracle APEX product team.

Reflecting on the worthiness of attending such an event, I thought I'd list out all the things I learned & observed.

1. Everyone learns.
   It was observed that 'gurus' such as Trent, Lino, and myself were there, and maybe this might influence the level of detail content delivered. Sure, I like the details, and a particular pace, but I'm there to learn a bunch of things as much as the next person. My REST skills need a lot of work, it's always great to get a demonstration of features found in the new version. The APEX feature set has grown so much, I'm struggling to stay across it all. I attend these sessions because I know the value I can get out of them.
2. Watching Shakeeb build.
   The last session involved Shakeeb using Quick SQL to build and populate data in seconds, then transforming the default output to look like a feed like Twitter. He did it in about 30 minutes, only using CSS once, maybe because his perfectionism was showing ;p
   As awesome as it was seeing the showcase that is APEX in action with declarative/low code techniques, I also get a lot out of seeing Shakeeb's thought processes, and how he moves about the IDE.
3. REST Services.
   One of my attractions to this event was the workshop on REST services. I understand the mechanics, but have not had enough practice using the declarative features. Having a hands on lab where I not only receive instructions in person, but get to ask Christina deeper questions as I explore the workshop. This was invaluable to understanding the feature integration. I have a few posts in mind to help bake it in.
4. Data is important.
   Our client would have appreciated Shakeeb's opening remarks. While we all know data is important, it can't be iterated enough to those decision makers that may not have enough understanding of the value of databases.
5. APEX Longevity.
   I knew this one already, but I like the fact this slide has made it into Shakeeb's commentary. APEX has seen so many JavaScript frameworks come and go, with little resistance between versions. I think this is rarely seen in the programming world.
6. Faceted search looks amazing.
   This will a boon for creating interactive applications that people are used to even quicker.
   I'm interested to see how this responds to more complicated queries, but there seems to be options for everything. I look forward to this feature, as we already build similar constructs already.
7. The native Popup LOV looks even more suburb.
   They really have learnt from the two solid plugins that came before them. Based on the attributes I saw demonstrated, this has everything we need, and everything we didn't realise we should have. And this is iteration one. Awesome.
8. I don't need to rely on a CSS overlay to remove toolips
9. We can hide empty display positions in the layout editor
10. The undo/redo tooltip was restored to native HTML in 19.x, different to the rest (thanks to sharing the love on Twitter).
11. I was reminded of an auto-height template option for inline modals.
12. There is a template option for 'icon and text' button templates to hide text for mobile devices.
13. A component setting was introduced to transform application Switch items into the pill format - yet another application level optional upgrade between versions.
14. Syntax in the code editor for SQL Workshop and Quick SQL is now highlighted. I think this improves readability and is a form of inline validation for your syntax.
15. SQL Workshop Object Browser has a few more tables, including suggested sample queries for the tables - the same sort of group by queries that I use to help analyse what's going on in a table. The same queries that utilised in the faceted search analysis. Code & data re-use right there.
16. We can now REST enable tables right from the SQL Workshop, with the flick of a switch.
17. I was reminded Postman was a nifty tool for REST development.
18. We can jump to the create application wizard from a table. I can't remember how, nor find the option, but I can't remember everything from the day... I make these notes to help, but hey.
19. Data dictionary cache is a thing. Again, I can't recall exactly how it worked, but I've made a note to investigate later as I explore the faceted search facilities.
20. The spotlight search has been expanded, and there are other nifty shortcuts that already exist. I use some already, but I now know to keep aware.
21. Paged designer multi edit - I am looking forward to these improvements, which include the ability to change template options for multiple components at once - hallelujah! But I wasn't aware that some attributes were previously hidden during multi-edit mode, so this has eased.
22. The ability to change the application logo has received all the declarative nick-knacks we needed.
23. This application logo is separate from the one defined in the login screen, which as also received a wodge of declarative low code improvements.
24. There is now a pre-built template style for dark mode. While I was aware of this, it's always beneficially for me to see Shakeeb operate the theme roller, since I don't use it enough.
25. Theme roller facilities are more refined
26. The Content Row report template - I'm looking forward to this so much I've retrofitted the template into 18.2, for demonstration
27. All the interactive report dialogs have been re-written, with a fresh look.
28. Menus UI can be tweaked with a 'callout' nature. We'll see how that's adopted in general...
29. Inline regions have been given some upgrades. I've been playing with a page to experiment with these recently (post coming), so this event gave me the opportunity to ask some specific questions.
30. Inline popups can be located near the invoking component, documented in the UT application.
31. Lino showed me how to define inline region size with classes, instead of jQuery (to be included in post).
32. Lino is approachable regarding my growing list of AOP questions.
33. And as the session was beginning, I felt a little inspiration to solve a regular expression problem I was facing last week. Some interesting performance observations noted as I experimented further today, and had a regexp expert to review my work. (post coming)
34. Christina is another solid member of the APEX family. I've had the pleasure of conversing with quite a few members of the team now, and they're a great bunch.
35. Juniper berries are used to make gin - it was the drink'n'learn on the beer cap, in our afternoon wind-down.

And I reckon there might be a few I left off. The Q & A included some conjecture about 20.1.

Brisbane & Sydney also get the pleasure enlightment musings of Connor.

Not bad for a user group event, eh? 

It's a shame some people struggle to get support from their employers to attend such events, and more of a shame we don't see more students getting engaged. How do we reach them - the next generation?

I'm glad they came, and I'm glad I went. There are some growing APEX markets around here, and some with lulls. If we don't get the visits, the next round of students will not eventuate. It's not just the people who attend the workshops, but the flow on effect from those individuals who report back and share information with other team members. It's certainly what I did with my colleagues. Who knows who's ears could be listening in on that positive feedback.

Do you want to learn about database technology?

Do you live close enough to Melbourne, Brisbane, Sydney, Seoul, or Tokyo to attend a software development roadshow for a day in November?

Are you interested in how easy it is to build data driven web based applications?
Perhaps you're a student of the programming world?

Your local user group is hosting some visitors from the US that I think are worth listening & engaging with, in person. And if you're not a member of the user group, it's only $50 for the day.

https://blogs.oracle.com/apex/oracle-apex-apac-tour-2019

50 bucks.

For a day of learning, engagement, and networking with industry peers.

I can't emphasise that networking element enough, particularly for those still formulating their career goals & focus.

It's been a while since I was a graduate, but I relished the information I gathered at the local user group events. I was recently privvy to witnessing a member of the next generation of technology professional help shape her career at our conference.

It's worth investing a day in yourself, to help inspire your work tomorrow, to help nurture your love for your job. Whatever the stage of your career.

Warning, tangent ahead.

While I now find myself a regular speaker at such events, I also still value attending them, even with the advent of regular, engaging, recorded online technology seminars with experts and those that build the product.

That photo? That's Oracle product manager David Peake helping out. That's what these communities do - get you within one degree of separation of the product itself.

I can't keep up with all the new gadgets being made available in my development tool of choice.
I certainly need more practice with REST technology, and a hands-on-lab with the development team would really help.
We're also about to level up with our version, so another revision of all the new kit will be super handy.

And Shakeeb is a really slick presenter. It's not just the technology I learn about when watching the sessions by Shakeeb, or Connor & Steven, or many other seasoned (and some new) presenters.
And I'd like to meet Christina, add to the list of the APEX team I've met in person.

Seeing them in person is something else. Nothing beats having a face-to-face conversation with someone about ... anything really. And to then establish that rapport, that human-to-human contact with other people. It can remind us to be humble. A reminder that we all make mistakes; we all continue to learn; and most importantly - none of us know everything.

We see regular personalities on the speaking circuit, or those bloggers out there that pump out their experiences online. These are only a small percentage of all the developers out there, working hard, empowered by education, utilising online resources, leveraging off forum conversations. Sometimes it feels like they seem to know so many things about the product, but really, most of them are just making notes, and thankfully publishing them in a manner for us all to benefit. Don't let the imposter syndrome get you down.

And let's not forget the friendships that are forged at such events. Yes, you're allowed to have friendships at work, it helps keeps smiles on our faces when times are tough.

So. 50 bucks, unless you're already a member - then it's free!

Learn about how to get your FREE Oracle instance.
Learn how to use a low code development tool to build apps.
Learn how to source data from web services.
Learn what's in the latest version of the product.
Have an engaging session where you have the platform to ask anything.
And do it - if you have the chance to ask a burning question, odds are there is at least another person nearby with the same question.

Thank you, Oracle for your growing engagement with the community, and thank you to our local user groups for helping facilitate these visits, and most importantly, thank you to the community - actively producing content, and those who drive up the SEO by quietly read all the content.

OGB Appreciation Day : Oracle Reports Server Queue Monitoring

Today is a day to celebrate what's great about our Oracle 'Groundbreakers' community. Our favourite stuff, what we're learning, horror stories, or just a little thanks.

I missed the second year because of holiday prep - so I decided to beat that this year by writing this the day I saw Tim's call-out on Twitter. Especially since I'm flying out to Melbourne for AUSOUG Connect, plus a little R&R.

I knew I had a few worthy ideas queued up, but I pondered for a moment on the most recent idea I noted.

So this year I'm going a little meta, or recursive, if you will - I'm acknowledging one of the posts from the original year. Oracle Reports Server Job Queue Monitoring. Thanks, Garth.

I wouldn't call this a duplicate post, rather a re-inforcement of this blog-fest, and I add some APEX spice.

I'm not entirely sure how I came across Garth's post, but I'm certain if it wasn't there, I wouldn't have realised I could transform that trusty but transient report queue servlet into table data in 3 easy steps.

A reliable page, but a little dated

And once they're in the database, we can APEX the shit out of it.

Now we can fold this data into awesomness

As Garth details in a second post, and Doc ID 2098657.1, it's a relatively simple matter of

1. DCL to define table owner
2. DDL to define some objects
3. Minor Report Server configuration

Now we can query rw_server_queue to see how many of which reports were executed each day by who.

Why would we want to do this? It's 2019, why am I blogging about an Oracle Reports feature?
So we can analyse reports usage in the legacy system before migrating to AOP, of course!

You can also find the relevant SQL here, to make it easier for some of us to find & utilise.

So that's now two #ThanksOGB references in one, illustrating how worthy participation in such an even can be.

Past posts

2016 OTN - Dynamic Actions
2017 ODC - (dog at my homework)
2018 ODC - APEX Workspace Activity Logs
2019 OGB - Oracle Reports Server Queue Monitoring
2020 OGB - (quite possibly something that also shares a precursor to Oracle Reports)

So you want to learn Oracle APEX?

Are you involved with Oracle? Perhaps your a DBA, or a PL/SQL data master, possibly know a bit about Oracle Forms, and you want to learn what all the fuss is about regarding Oracle Application Express (APEX)?

My best elevator description? It's a low code development tool that lives in the browser. Yep, the IDE is a web page that allows you to add SQL, PL/SQL, modify a few attributes - and you've got a data driven web based application that runs on any device. Easy for any Oracle technologist to learn.

This is not a new tool, it's been around since 2004, conceptually even before then.
We've built applications that have lasted for years with minimal inteference. They've outlasted other popular frameworks, and they're still within touch of the present. Still doing the basics of the web, in a clever, interactive way.

AskTom has been using this technology before it was called HTML DB.

It's also being mentioned out there, in the world beyond our bubble.

And it's hitting the news for the right reasons - helping natural disasters.

And helping people take action regarding climate change.

This all started from Kscope19, and it appears momentum continues to grow.

So do you want to board this hardy vessel?
The community is strong & passionate.

Use this as your starting resource
http://apex.oracle.com/shortcuts

Sign up for a (no obligation) workspace on apex.oracle.com.

Your web page is your IDE now. Lucky, the modern browser is a nifty place to be.

That's APEX. You paste SQL queries / PL/SQL API calls in a box, change some attributes, and there you go. Simples. You have data driven web applications, ready to use on any device.

As for skillset, I have a few JavaScript one-liners I use regularly, sometimes I embed some simple HTML, and I have a mechanical understanding of CSS - which is just queries against your page content.
You know how a web page works? You know Oracle stuff (SQL, stored procedures)? You'll be fine.

If you have Oracle Forms experience, maybe check this out.

Empty the cup.

Try the documented tutorial
Install some packaged applications, check out how certain features tick.

Looking for your source code? It is just meta-data in some tables, check out
select * from apex_dictionary;
and
select * from apex_applications;
See how far the rabbit hole goes.
SQL developer has some dedicated tools for the job.

Try the Maze Runner presentation by Jorge & Jackie, to find your feet.
Want to know what components are available? go to apex.oracle.com/ut
Charts are particularly awesome: apex.oracle.com/charts
Got a question? Try the forums.
Like a conversation? Try Slack
Happy to chip in for some help? askMax.
Want a place to visit, learn & keep in touch for 5 minutes every day? Try apex.world
They have a page titled "How to start as a developer".
Also check out the plug-ins page, to fill any gaps in the builder.
Steady blog stream available here.
Do you like to listen? There are currently two active podcasts.
If you like books, I particularly recommend the collaborative efforts.
If you like videos, I have a series, and plan another. Caleb Curry also has our back.

Need to load spreadsheet data? This. And this.
Want to talk to web services? Find Carsten.
Do you really need to produce a PDF? AOP (APEX Office Print) is your best option.
Moet dingen vertalen? Translate APEX
Want to get a little funky with your page interactions? Check out Maxime's award winning demo app.
I recommend you learn not what you can do for Dynamic Actions, but what dynamic actions can do for you.

Want a second opinion on this list? Try this Github, because it adapts.

Want to get serious about delivering your app? Get a piece of an 'always free' tier, and check out Dimitri's guide. Or you could build you own stack with the free Oracle XE.

Note, the preferred pronounciation is a-pex, not app-ex. I blame my accent for any mispronounciations ;p This guy knows.

It's also "APEX", not "Apex". Possibly as not to be confused with SaleForce 'Apex', which if I recall is something pretending to be Java. "ApEx" is as painful as camelCase.

You generally need to prefix any searches with "oracle apex"

Or just ask a question on Twitter with the hashtag #orclapex. Someone is bound to point you in the right direction.

Note that even those without a Twitter account can browser activity in this tag. I recommend getting involved, it's an active zone for the Oracle team.
You might see it as #orclAPEX, which not only helps get it right, but helps #a11y

Let me know if you think something else belongs here.

#letswreckthistogether

Thursday Thought: Look all around you

A current pleasure of mine after getting home while the sun is still pleasant is to take my (currently) nine eighteen month 5 year old for a stroll in her pram. It's been a while since I drafted this, time to tidy up the backlog...

We were lucky enough to live opposite some bushland, big enough where you can do a 30 minute loop and enjoy a mostly native tree/scrub area. There are obvious trails that are no problem for a pram, but still give plenty of bumps for the young one to practice her vocals.

Through the right combination of events - since coincidence has such amazing potential, I decided to walk down a dead end trail I rarely walk through and enjoyed it so much I had to share.

We recently had some short but hard rain - a nearby corridor of suburbia experienced some freakish hail. This made the ground a little firmer, and soon after some maintenance vehicles travelled this particular trail. That left it ripe to preserve footprints in such a way that I'm sure left a tale that I'm far from being able to fully interpret.

It made me think back to our ancestors and their reliance on the ability to interpret such information in a way to live longer. To know how long since predators passed by, and how long ago; follow prey in the right direction; recognise footsteps from strangers.

All I could do was recognise big dogs, small dogs, kangaroos - though it's been a while since I've seen them. I saw smaller prints that could be rabbits, cats, or both. A few bike tracks, human shoe prints over the tyre tracks - all in a variety of orders.

The dog tracks were erratic, kangaroos had an obvious direction - and where very far apart! I also considered what I couldn't see - any snake trails. I know plenty of other reptiles live in there, there's no reason to think I couldn't find any dugites or maybe even tiger snakes if I know where to look.

Earlier today as I left work the first thing I saw in the sky was a contrail that split the sky. The tropospheric winds must have been relatively still because the start of the trail was still quite defined. I enjoyed a little giggle at the chemtrail conspiracy theorists that think it must be poisoning day - they don't happen very often in Perth thanks to local conditions.

As I arrived home the perspective had changed, and by the time I was on my walk the vapour dissipated into such an amazing pattern, covering the setting sun.

I thought it best to try describe the nature I enjoyed this afternoon rather than post a few cool pictures. Partially because I thought my writing skills might benefit, and more profoundly - I think contemporary society needs to remember to take time out from technology and let the brain do it's work behind the scenes. Process the information of the day, enjoy the current surroundings, think about the most bizarre succession of thoughts that take you some place far from the present - or one of the favourite things I heard a former martial arts teacher say: find your thoughts, then lose them.

If you enjoy occasional ramblings like this that might provoke a few related thoughts in your own noggin, you might also enjoy Steven Feuerstein's personal blog -> feuerthoughts.blogspot.com, though he seems to have another major project on the go since I drafted this post.
You can also try using the Thursday Thought, Science and/or the Friday Fun labels on my blog.

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.

Change floating label dynamically #orclAPEX
$("label[for='P1_NOTE']").text('Happy Friday!'); pic.twitter.com/rwNd9scY04

— Scott Wesley 🌌 (@swesley_perth) September 20, 2019

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!

APEX 19.x 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.
https://github.com/swesley/orclapex-builder-css

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

Edit: Since I'm currently doing most of my work in 18.1, I forgot 19.1 introduced dark mode, not 19.2.

That said, I think Vita - Dark style added to the 19.2 Universal Theme offers better contrast than the page builder itself.

19.2 Dark UT style

Why do the users get the good stuff? ;p

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.
paste('P105_EVENT_ID');

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.
https://apex.oracle.com/pls/apex/f?p=32532:22

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.

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.

.apex-item-grid.radio_group{
  display:flex;
}

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).prependTo($(this).next('.apex-item-grid-row'));
});

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

References

jQuery Cheatsheet

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.