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.