Wednesday, 16 July 2014

Order APEX column based on hidden data

An occasional question in the forums relates to issues ordering a particular column. It's one of those things that will probably keep coming up, so it's worth having another reference out here on the web.

The basic example stems from the need to order data that might contain characters.

with data as
  (select '1'  vc from dual union all
   select '11' vc from dual union all
   select '2'  vc from dual union all
   select 'a'  vc from dual)
select vc
from data
order by vc;


Most of the time people would want this ordered numerically as 1,2,11, then characters before or after the numerics. There are a few SQL solutions, but we need something for APEX so when you click on the column heading - it orders as you expect.

Other sample use-cases might also be found
  • in ealier versions of APEX where LOV sourced column order by return value, not display value
  • any place where data is formatted in some way
  • any occasion where you want to order data conditionally
So another example is the blob file size supplied format mask mentioned in last week's post. Here data was ordered by data values - not logically from our human perspective.

One solution offered here by Arie Geller is to prefix the column value with a hidden column
'<input type="hidden" value="'||(estimate-actual)||'" />'||abs(estimate - actual) AS variance
I found another example from Denes Kubicek back in 2008.

The solution can be even simpler & lightweight than that - use HTML comments as a prefix.

And typically we'll need to left-pad our character data to the same length - this ensures ordering compares apples to apples.

I've applied this prefix on my blob file size format mask example, to allow ordering on the column I said wouldn't order so well. The prefix is essentially the untrimmed output of filesize_mask(), with the leading spaces replaced with zeroes - and the suffix removed.


Both solutions require the column to be displayed as "Standard Report Column". So if you are using text data it's best to also escape your content in the query using apex_escape.html()

Run the demo to see it in action.
Try ordering using "Size Mask" vs "Bad Order" and see where +1MB files go

The file names are scrambled using much simplified scrambling technique demonstrated here by Joe Lipman.
My full SQL can be seen at the bottom of the demo page.

-- Update: Be sure to read the comments on the post regarding Patrick Wolf's elegant solution of just putting the display version of the column in the column's HTML Expression.

Thursday, 10 July 2014

Returning BLOB file size

Occasionally I'll want some form of report noting file sizes of blobs in a database.

The solution is relatively simple, and I thought I'd write it up here for a place to copy syntax each time.

APEX users also have a handy table to verify this against (apex_application_files). Well, a synonym/view that ultimately maps to the core table wwv_flow_file_objects$.

It contains a doc_size column, which is no doubt evaluated at some point during upload of the file. For blobs in your own table you can use the supplied package DBMS_LOB to get the length of the file in bytes.

Multiply that by 3 orders of magnitude and you have it roughly in kilobytes - a digestable figure for most files dealt with these days (calc_size).
Add some pretty formatting and you have how Windows Explorer shows file sizes (calc_fmt).

Inside an APEX report you might consider the format mask "999G999G999G999G990" on 'calc_size' - without any trailing suffix so you can still order the results.
select id, filename
  ,round(dbms_lob.getlength(blob_content)/1000,2) calc_size 
  ,to_char(dbms_lob.getlength(blob_content)/1000,'999,999')||' kb' calc_fmt
  ,apex_util.filesize_mask(dbms_lob.getlength(blob_content)) size_mask
from apex_application_files
order by doc_size

/* UPDATE 2014-07-11 */
Carsten (@czarski) pointed out on twitter that an API that's available that returns a rounded off relative size with the appropriate trailing KB/MB/GB/TB.

It's not shown in the current 4.2 documentation - but I think it's probably just been missed rather than it not being supported. The APEX_UTIL package is pretty large should probably be broken up so little nuggets like this don't get missed.

He also mentioned a "FILESIZE" format mask, which isn't shown in the format popup (you just type it in) and also behaves differently to the API. Values under 1MB returned just numbers to 3 decimal places, and a 10MB file was labeled as 10KB - this was on

The only downside to the API formatting is as per calc_fmt, the ordering is impaired - by ordering on this column you will get results like:

So in my sample results run in APEX SQL Workshop I can see tiny files to progressively larger files - up to an APEX application export of about 1.6 mb & a PDF over 3mb.

Filenames redacted to protect the guilty

Note, this view can't be queried from SQL Developer (or your tool of choice) since the current workspace is part of the view. Martin has an interesting post to get around this sort of thing by defining an APEX session using PL/SQL.


Thursday, 3 July 2014

Thursday Thought - History & Creativity

My sister sent me this video not long after my daughter was born earlier this year - I finally watched it while cleaning up e-mails and I'm glad I did - I highly recommend you all take 2 minutes aside and just let these images of history wash through your brain.

If you think the first half had an air of familiarity, you might be thinking of this video from Carl Sagan - I certainly was.

Now consider this was a project by a 17 year old at high school. This is the kind of creativity technology kids are creating at school now - awesome.

Note in the about section of the video he states he doesn't own rights to the images or song - if you think laws can sometimes strangle creativity then you might be interested in this TED talk by Larry Lessig.

If you like the video, and like the concept of looking back through time as life on this planet evolved - there is a great segment in one of my favourite fiction books that explores this concept - The Light of Other Days, by two science fiction greats Arthur C Clarke & Stephen Baxter.


Wednesday, 2 July 2014

Two milestones, one offer

I stumbled across a blogging milestone while fact checking what I was going to open this post with - for me this month represents 5 years of blogging.

In that time I've learnt heaps, met new people, changed blog name, visited other cities to speak, and dived deep into APEX after a long time in Oracle Forms, published a video series & was recognised as an Oracle ACE.

Turns out Packt Publishing are celebrating an even bigger milestone - 10 years of publishing. To celebrate they are offering all their eBooks & Videos for $10 each.

Hurry along - you've got until the 5th of July to get my video series "Oracle APEX Techniques" for a steal.

I'm keen on publishing again - I have a topic in mind, it's just a matter of time!

All I can offer for now in celebration of my milestone is to continue to share my passion for Oracle & science ;-)


Wednesday, 25 June 2014

For those not at Kscope14

Geographical, monetary or otherwise - some of us simply can't make it to the conference every Oracle developer should do at least once, or so I've heard.

For those of us stuck at home we do have some options.

1) ODTUG offers a LiveStream of selected presentations. 

I stayed up past my bedtime to watch Patrick Wolf introduce the APEX 5.0 Page Designer. I look forward to watching the recording of Shakeeb Rahman on Beautiful UI in APEX 5.0. I've heard great things on the twitterverse about it, and I want to learn more about the Template Options feature.

2) Keep an eye out for ODTUG webinars.

Unfotunately Martin D'Souza had to cut his Kscope14 trip short, so his presentation on Logger will hopefully appear in the reasonably near future - among other quality the tends to come by.

3) Search for the #kscope14 hashtag

Even if you're not on Twitter you can still view a bunch of information coming out of the conference using the hashtag #kscope14. Despite all the ribbing hashtags get (thank you, instagram) they are a great indexing tool in Twitter. There seems to be plenty of socialising and beer drinking going on, but plenty of tech news coming out as well.

4) Attention Perth residents

If you happen to live in my home town of Perth, at the Oracle offices on morning of the 23rd July I'll also be presenting on the APEX 5.0 Page Designer - helping to jumpstart the learning curve to the new release and show the local Oracle Forms community how much of an advantage they'll have with the new APEX environment.

If all goes well, I hope to meet many Kscope regulars next year in Florida!

Thursday, 19 June 2014

Oracle APEX 5 EA2 ready for consumption

After last night's teaser I thought I'd try again to see if EA2 was ready, and it is!

and it looks darn sexy

Confirmed in Twitter by some big-wig APEX dude at Oracle
I've had bit of a play and it it's very impressive - plenty more polish has been added since EA1.

I particularly the Template Options feature for managing templates - this should help make customisations to our applications more consistent.

And a "Universe Theme" set to number 42? I see what you did there...

Good onya APEX dev team, this will be the most productive version of APEX so far!

Wednesday, 18 June 2014

APEX 5 EA 2 is almost upon us

KScope14 is just days away from kicking off and hours after I realised the live stream of Patrick Wolf's APEX 5.0 Page Designer presentation was actually at a reasonable Perth hour (9:30pm), I found out I wasn't going to be home to watch it - darn it!

Luckily in this age of the internet there are other reasons to get excited. Tonight while I continued to write my Prezi on how APEX 5 will make us former Forms developer's even happier - I couldn't log in and I found the login page has been updated to what looks like a very sleek Flat UI.

Flat UI is a modern design that has many merits, but I always wonder if it's just another passing phase. Here is another interesting article that includes a chat about the limits of Flat UI, and also includes a great pictoral comparison of Flat vs skeuomorphism.

I thought it might be worth plugging the contrasting designs into google trends to see what's going on, but I don't think it reflect the true history of skeuomorphism.

I'd say the searches on skeuomorphism are probably related to describing what the former technique meant in relation to Flat UI.

Anyhoo, APEX 5 Early Adopter 2 is on it's way, and I'd say expect no stone left unturned!