Wednesday 25 March 2015

Why I can't do webinars

This is why I can't do webinars from my current home.

Ookla Speed Test Result

The upload speed is no doubt way too slow to cater for voice and screencast. Unfortunately I also seem to be in quite the dead spot for 4G wireless connection.

Download speed might be enough to support the streaming services recently launched in Australia, but I know ABC's iView does stutter occasionally so I think I'll still have to wait for the NBN. I'll probably have moved house by then...

Thursday 19 March 2015

Season's readings

Have you discovered the OTech Magazine yet?

This magazine is a brainchild of Douwe Pieter van den Bos and is now five quarterly issues young. The "Winter 2014" is a bumper issue this time, plenty of reading during various downtime that's typically afoot. The season in particular deserves quotes, since outside my home it's currently 39C.

I hope to publish something again next issue, should I find the time between a couple of other projects. It's worth the subscription.

Note: I found this post in my drafts from late December. The next issue is due shortly where I've submitted an article on Pivot. upgrades to 5.0 this Friday

There was movement at the station, for the word had passed around
That the APEX version 5 will become available.

I've jumped the gun before, but this message is taken direct from the site:

No doubt you've heard the news already, but I had to chuckle a little today as I did about an hour's work on a 3.2 environment. After playing with APEX 5 EA3, it was like jumping back into the 90's and using a 3.5" floppy.

APEX 5 will be an amazing leap forward in browser based development, but Joel suggests a few changes to be aware of.

Word on the street is APEX 5.1 will have a much shorter release cycle. This isn't the only post I've seen where Joel suggests more features will be just around the corner.

Thursday 12 March 2015

All commenters now welcome

I've finally bitten the bullet and disabled the Google+ commenting mechanism from this blog. I promised this in my 2014 annual review, and I figure I might as well not wait.

When Google announced they would integrate Google+ with blogger, it sounded like a good idea at the time. However it required that commenters needed a Google+ account as opposed to having a number of authentication options.

Unfortunately this means the many comments made since turning it on will disappear into Google+ land, and won't be visible from the relevant post. There's plenty on the topic out there, so much so I've lost whatever discussion I had with someone about it.

It's a shame because some comments were particularly useful, with clarification coming from the APEX team regarding functionality in coming releases. It does mean the bugs that came with it also disappear.

- I get email notifications for comments - big win
- Post list now shows how many comments in each post - it's the little things
- Last comment found was March 2013 - 2 years into the G+ ether

Google+ comments are still available in web cache ... for now. According to this site I think these comments may disappear from cache after three months, I'd be happy to be enlightened otherwise.

Googling a URL with the cache: prefix will open the cached version immediately

Opening cached version of webpage using Google

I thought the Wayback Machine may also help but it doesn't seem to have archived any individual posts.

At the end of the day I'll be happy if this encourages more people to provide feedback. It's a great way to learn, particularly when I make mistakes in my posts or something mentions a better way.

Perth Breakfast Meeting - APEX5 + JSON + Food

Do you live in Perth, Western Australia?
Do you use Oracle Application Express?
Are you interested in seeing how easy it is to extend the product?
Keen to see APEX 5 in action?

Please tell me you're free for breakfast on Tuesday March 24. You'll be out by 9am feeling glad you came. Visit the event details page for the abstract, it's inspired by a book I'm currently writing ;p


AUSOUG: Add Awesome Visualisations to APEX using JSON

Where: Oracle Perth Office level 2, 66 Kings Park Road, West Perth WA
When: Tuesday, March 24, 2015 07:30am - 09:00am
RSVP: By Tuesday 17th March , using the buttons below or emailing

Yes, I'm attending!

No thanks

Feel free to bring your laptop. Most of the session will be me hands on using APEX5, creating a page before your very eyes.

And yes, there will be a flavour of science in my Prezi intro.

Wednesday 11 March 2015

Adjusting Interactive Reports - learning from mistakes

Sometimes I come across code where it's obvious someone has taught themselves APEX, then worked through a problem to come up with a solution - but they went a long way.

Today's situation required data in an interactive report to not wrap the output. I thought it'd be worth sharing because a few lessons might be demonstrated along the way for newer developers.

Here is the long way:

1) Add this to the Page Load property
This will work to a point, until the user start filtering the IR. And it affects all td page elements, not just those in the report.

So now the developer (name withheld to protect the innocent) does the following:
2) Create a hidden item P1_IR_ID

3) Create on new instance computation to fetch the allocated report ID
SELECT interactive_report_id
FROM apex_application_page_ir
WHERE application_id = :APP_ID
AND   page_id        = :APP_PAGE_ID
The computation point will rarely fire because this is executed typically upon landing on the login page - at least the first APEX page visited before logon.

4) Create a dynamic action to reapply the original jQuery.
Event: After refresh
Selection type: DOM Object
DOM Object: &P1_IR_ID.

With a JavaScript action has per the page load. The dynamic action scope was Dynamic to ensure it was reapplied after any partial page refresh (PPR), but I think this would be superfluous to an "after refesh" event.

I admire the ingenuity here to identifying the report. This would have been easier solved with a static region ID being allocated to the region, eg: p1_report, then using jQuery selector #p1_report.

Here's the easy way.

Define the following CSS, either inline or within your supporting .css file.
.ir_nowrap td {

Add "ir_no_wrap" to the region's CSS classes.

However, my theme 25 IR template did require an additional adjustment, because I found the original template had two class attributes, and the second one was ignored and my class wasn't being used.

I changed the first line from this"
<section class="uIRRegion" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="#REGION_CSS_CLASSES#">
to this:

If you like this sort of example to learn from, then I bet you'll enjoy Peter's (coming) APEX Worst Practices presentation.

Wednesday 4 March 2015

LISTAGG to a CLOB, avoid 4000 chr limit

Thanks to twitter I found this post by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.
ORA-01489: result of string concatenation is too long

Update 2019: Connor now his this code in his Github.

I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search after the fact.

A little tip if you do find it - don't attempt to copy the code from the translated version of the page. It's amazing how many syntax issues were introduced by the translator, and logical issues that I wouldn't have noticed had I not performed a diff after things went wrong.

For instance, the "aggregate" keyword here vanished:
CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob

Thank's Carsten for a useful solution using custom aggregate functions, an element of PL/SQL I'm yet to master.

It's certainly faster than the XML solution, here is the throughput difference from 100 iterations of each.
-- listagg_clob
3.34 secs 
.0334 secs per iteration
-- xmlagg
18.75 secs 
.1875 secs per iteration

The simplest solution, however, would be to set up an RESTful service using ORDS.

Another workaround involves a CASE statement.