Tuesday, 12 November 2019

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. Well, the second one was written by the person they hired to no doubt integrate it. 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. Great job, Menno.
  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. All the interactive report dialogs have been re-written, with a fresh look.
  27. Menus UI can be tweaked with a 'callout' nature. We'll see how that's adopted in general...
  28. 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.
  29. Inline popups can be located near the invoking component, documented in the UT application.
  30. Lino showed me how to define inline region size with classes, instead of jQuery (to be included in post).
  31. Lino is approachable regarding my growing list of AOP questions.
  32. 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)
  33. 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.
  34. 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.

Monday, 4 November 2019

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.


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.

Thursday, 10 October 2019

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)

Monday, 7 October 2019

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

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;
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.

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.


Thursday, 26 September 2019

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.

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.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.

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

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

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 apex.world.

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 .t-Cards-item.is-active 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?

APEX Classic Report as Alerts

I love classic reports.
You can make your data look like anything, out-of-the-box, as previously highlighted by Christina and Carsten.

When you create a classic report, you can select different Report templates via the region attributes. I use the Cards and Alerts a fair bit, allowing me to produce data driven content, instead of whatever I type in the application builder.

This has been the general premise of APEX templates since the beginning. We define a query, and feed those columns to the template, where the values are substituted in.

Alerts Report Template
We can combine this template with the following query, containing matching column aliases.
  'Alert' as alert_title
 ,'Be alert, not alarmed.' as alert_desc
 ,null as alert_action
 ,'danger' alert_type 
 ,'fa-user-woman' alert_icon
from dual
This doesn't need to be from dual, it can be any query you like, as long as it contains those column aliases. Multiple rows will show multiple alerts, down the page. The alert_type can be any of the pre-defined template options for that attribute.

Over time these templates get upgraded, and now we operate with the Universal Theme, we gain access to improvements after verifying the theme.

For instance, APEX 5.1 introduced #CARD_COLOR# attribute to limit the manual CSS required to make it happen.

What the Alerts Report template still lacks is the ability to customise the icon (as at 19.1).

We can customise the icon with a declarative Alert Region, by setting the Template Option Alert Icons to 'Show Custom Icons', and specific the icon in the Icon field.

If we want to customise the output of a classic report displayed as an alert, we can use an After Refresh Dynamic Action that executes JavaScript on page load to rejig the HTML of the report.
// replace icon in template, since we can't do it in SQL yet
$('#acting .t-Alert').removeClass('t-Alert--defaultIcons').find('.t-Icon').addClass('fa fa-user-woman')

Here I find the region I gave a static ID of 'acting', then removes the default icons from the component with the t-Alert class.
It then finds the t-Icon class to extend with my chosen icon.
This transforms it to exactly what a declarative Alert region looks like.

This example was built for a report with only one record, but hopefully gives you an idea of the potential these type of events have. Check out similar example here.

Happy APEXing.

Sunday, 31 March 2019

APEX Web Service Request Limit

Every now and then there's a new setting worth mentioning.

The New Features page included news regarding the logging of
Requests to external web services from inside the database
Firstly, this only appears to log requests coming from within the APEX environment.
Requests from scheduled jobs will not be logged, as of 18.2.

Secondly, moving to the cloud can be ... a learning experience. Since we're now using Social Sign-in, web requests are up. We also re-configured directly listings and file fetches to use web services. Web services are great tools.

However, there's a new setting that needs guesstimating before you expect too many users to log in, otherwise you might spot the following error in your error logs:
ORA-20001: You have exceeded the maximum number of web service requests per workspace

Search for this message and you'll find Dimitri encountered it during his experiences with AOP. Pierre points also points out the setting in a forum post.

Found in the Internal workspace, under Security settings, Workspace Isolation, there is a Maximum Web Service Requests attribute. There is a workspace level setting under Manage Workspaces, Existing Workspaces, then choose your workspace.

Maximum Web Service Requests attribute

However, the instance level setting has a default of 1000. You may need to review that value if you have a reasonable number of users using web services within Oracle APEX.

Now while the item help states:
Enter the maximum number of web service requests that Application Express supports for each workspace in this instance. You can configure a more specific value at the workspace-level.
This forum response from Carsten states this applies to a rolling 24 hour window.

I immediately envisaged a JET chart in our monitoring application, so we could visualise how this figure changes over time - and to see where it peaks. After a slight hiccup, I now see this graph that tells me out limit should at least 4000, to allow wiggle room.

Plotting apex_webservice_log

The really thin bars were an accident that I ended up thinking better represents the data, as compared to being a line graph.

So as activity occurred during the day, as depicted by the green line, the rolling 24h figure in blue crept up. This was calculated using an analytic function that allows me to create a moving window on the data based on an interval of one day. And I'm summing how many at each moment, hence the sum(count(*)).
select * from (
    select request_date 
       ,sum(count(*)) over 
            (order by request_date  
             range between NUMTODSINTERVAL(1,'day') preceding 
                       and current row)  last_24hr
    where request_date > sysdate-3
    group by (request_date) 
-- must ignore first day in inline query
-- as cumulative range will be skewed
) where request_date > sysdate-2 
order by 2
The 'Hits' count in the y2 axis is rounded to 30 minutes, finding inspiration from these forum responses.
Without this rounding, the chart looked too much like a NY skyline, while the rounding smooths the staccatic nature of the data. Perhaps there's a JET attribute I could use instead?

What took me some convincing was the shape of the rolling average graph at night. I knew daily activity was essentially in the shape of a bell. I saw it in the y2 axis above, and I see it in the APEX activity logs.

Two days of APEX activity. Can you see lunchtime?
So I found myself building a test case with a few dozen rows representing the bell, just to check there wasn't necessarily a dip in the rolling data overnight, instead of flat-lining.

I'm was not entirely convinced, so I detailed a the test case in this post, looking closely at the range analytic function. Get some real "SQL-perts"? on the case. It's the same query as above, just with smaller, static data set.

And now I've seen the rolling activity drift down on Saturday mornings, I'm more confident with the graph outcome. It's almost poetry.

What happens on the weekend

I must say, playing with these time-based graphs has been a much easier experience in 18.x. I'm still getting the hang of which settings to use (particularly with dates), but at least I can just use the SQL I need, not extra bloat just to help the UI.
While I'm complimenting these charts, the database is also thankful we can now define the SQL once, and use the same source to plot multiple series.

Load test your applications, people.