Speakers looking for New Year's Resolutions?

If you're a semi-regular speaker looking for a new year's resolution, how about learning this little ditty?



I never opened the clip of Daniel Radcliffe doing the same song not long ago on Jimmy Fallen - I don't really watch his movies I skipped by, but I did get baited by something on Facebook mentioning the reporter's (Kim Powell) a capella warm-up method.

Turns out it's a 1999 rap by Blackalicious, lyrics for your practicing pleasure. I don't know if this is the original video clip, but it's a rather creative and would have been cutting edge for it's time.

Anyone up for doing this prior to Kscope15? ;p

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions within a SQL statement was relatively easy.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(event_no)
FROM   events
/

However a slight adjustment is required for DML. The documentation suggests that

• If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

but does not give any examples, which I think is unfortunate - but thanks Tim for getting us started ;-)

UPDATE /*+ WITH_PLSQL */ events e
SET e.org_id =
  (WITH
     FUNCTION inline_fn(p_id IN NUMBER) RETURN NUMBER IS
     BEGIN
       RETURN p_id;
     END;
   SELECT inline_fn(e.org_id)
   FROM   dual);
/

Without the hint Oracle returns

ORA-32034: unsupported use of WITH clause

but I was getting

ORA-00933: SQL command not properly ended

What clued me in was the brief highlight SQL Developer makes over the statement before it executes. For me this paused at the return statement within the function.

I happened to be using one of the pre-built Oracle Developer VMs to play around, and it turns out the one I'm using has SQL Developer 4.0.0.13 supplied.

That particular version doesn't seem to be aware of this bleeding edge feature. I vaguely recall seeing this mentioned somewhere probably in the vicinity of thatJeffSmith fellow. I tried it in the command line SQL*Plus and it worked fine against the 12.1.0.1 instance.

It does ring a clear bell for once upon a time circa 2006 working on Oracle 9i or 10g when I sent an email to a colleague containing a SQL statement including a WITH clause.

He didn't have success in his Oracle 8i SQL*Plus windows client either... oh how I miss thee.

Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as

create materialized view apx_application_pages
as select * from apex_application_pages;

Then you can add an index on application_id and page_id, then you have the perfect substitute for your menu SQL.

In our development instance containing all sorts of experimental applications - apex_application_pages returns about 1500 rows - but it's a relatively complex view. Using the snapshot instead of the supplied view makes the following difference in throughput when running our query 50 times (on dev)

    33.93 secs (.6786 secs per iteration)
     0.17 secs (.0034 secs per iteration)

Just don't forget to refresh it when your application grows.
exec dbms_mview.refresh('apx_application_pages');

A simple concept, but it can make a very positive difference when done right. It's also applicable to a number of other related scenarios.

Chrome Device Mode and Mobile Emulation

Recently a colleague suggested I take a fresh look at the mobile emulation button in Chrome's developer tools.

Apparently this was upgraded earlier in the year and it's a shame I didn't have this a year ago!

Chrome Developer Tools Mobile Emulation

Some immediate advantages I see for APEX development:

• Specify the device you'd like to emulate from a decent range of contemporary devices
• This viewport can then be easily scaled to suit whatever monitor you're using
• Apply network throtttling to simulate access on a mobile network
• Use the emulation tab to define a media type to render as - I've been using 'print' to test how certain pages look when printed
• The cursor changes, indicating the behaviour will emulate finger touches on a touchscreen
• touch & drag emulated perfectly - I removed touchpunch from my page and sliders stopped working as expected without jQuery mobile
• scrolling drifts instead of static desktop movement (happy to improve terminology here)

Full details on how to use these features are available here: developer.chrome.com

Tomomi also has a great write up on the topic.

Nice work Chrome, nice work.

ODTUG from afar

I would like to extend my apologies to those people who persevered just now with our attempt to bring to you my presentation on APEX 5 Page Designer thanks to ODTUG.

Due to the massive time zone differences, the appropriate time on my end (6am) means I connect from home - where I'm limited to an ADSL connection. My location is even a bit of a dead zone for my smartphone's 4G plan which is normally better than my home internet speed. I considered just tethering the laptop to my phone, but I don't think it would have been an improvement.

After testing the audio last month it seemed things would be fine as long as I didn't use Prezi (not powerpoint), which hogged the connection with the visuals updating - but obviously the gremlins had other ideas today.

Blame him or Tony Abbott

Melissa from ODTUG has suggested we try record it another time so you don't miss out. In the meantime you can view the slides yourself here, you just don't get to enjoy my Australian accent attempting a few jokes - particularly at the start.

Next year I plan on attending Kscope in Florida, where I've submitted a few abstracts. I think we'll hear who's been accepted next month - but I look forward to meeting so many people I've collaborated with online in some form.

Stay tuned!

Perth 2014 Professional Learning Event

There's a little over a week left to this year's major Oracle user group event in Perth, so attention all locals (or even east coasters) - if you're interested in picking up new skills & ideas - you haven't got much time left to register!

This year it's called a "Professional Learning Event" instead of a conference and the program reflects it - plenty of hands on workshops mixed in with normal sessions. Considering it's easily half the price of a typical training course, you'd be crazy not to go - particularly since there are plenty of side benefits to events like this.

Here's where you'll probably find me (if I'm not at the Sage Computing Services booth, of course):

Update from Oracle Openworld - this panel discussion sounds like a nice fun but informative intro to the conference

Data Visualisation - Chris Muir - data visualisation is a growing interest with a world of possibilities, and even though it's ADF based I hope to pick up plenty of ideas.

Flashback Data Archives - Björn Rost - I haven't found myself with my of a need to use flashback gadgets, but Penny suggested I give this one a go. After seeing Björn present last year I'm sure it will be informative and enjoyable.

jQuery for PL/SQL Developers - you certainly don't have to be an APEX developer to attend my session. If you've always wanted to learn jQuery but struggle to make the leap, give this a go. I'll be making regular breaks from the presentation content to get hands on so bring your laptop or tablet.

Building a building App - Branka & Craig - I was heavily involved in this project and it should make for a really interesting case study - an APEX application built specifically for a 10 inch tablet, and the journey it took to get there.

Building maintainable applications in APEX - Jeff Kemp - I'm always keen to see some thoughts on best practices from other developers.

Mobile mumbo Jumbo - Chris Muir - I'm sure Chris will offer some thought provoking insights into the current state of play from Oracle's perspective.

12c for Developers - Connor McDonald - how could I not go to this one?

OHarmony - Penny Cookson - great theme, great topic, great presenter, great boss. Learn more tuning gold from the master.

PL/SQL Masterclass - Tim Hall - I'm not sure I've heard Tim speak too much about PL/SQL, and I think everyone should learn at least one important lesson in these two hours.

Presentation Skills - Penny & myself - I'm excited about doing this one - it should be fun & engaging.

APEX 5x5 - a third session I quickly said OK to, but has come together rather nicely based on experiences from my first 6+ years of APEX development. I'll have to run fast from the previous room!

And while the sessions are great, conferences are a lot of fun and a great chance to catch up with past colleagues, and find out what other people are doing with contemporary technologies.

ODTUG Webinar: APEX 5 Page Designer

I'll be presenting an online webinar for ODTUG not long after the Perth conference.

It will be the same session I did for AUSOUG back in July, which is lucky since it's 6am for us Perth locals. It's a close look at the Oracle APEX 5 Page Designer, and what it means to you as an APEX developer.

Check the webinar registration page for details on the abstract and help with the time zone conversion for: Thursday, November 20, 2014 5:00 PM - 6:00 PM EST

It was originally written as a Prezi, but due to my ordinary ADSL connection I've had to convert it to PDF. It translates ok but some of the magic is lost. I'll provide a link to the Prezi on the day and you should be able to follow along with the original if you wish.

Even if you're like me and still love the component view, I reckon once you see all the benefits of the new IDE, you'll be a convert and will be itching for the new release to finally arrive.

PL/SQL Challenge Roundtable

Do you write PL/SQL? Me too!

Trouble is, sometimes it's hard to decide how to structure your packages - particularly in an APEX project. Over at the PL/SQL Challenge website run by Steven Feuerstein and friends there is a page dedicated to roundtable discussions.

I submitted my question not so long ago and hope to get some interesting responses. Why don't you give the site a visit and contribute? The discussions usually last for about a month.

Past discussions are available to view - last time Steven asked for use cases for DBMS_RANDOM. You could even test out your PL/SQL while you're there and take a few quizzes.

The moon is a sphere

I was fairly excited at the prospect of seeing tonight's lunar eclipse but I neglected to take into account not being able to see the eastern horizon from my house.

I missed most of the total but enjoyed the crescent of the partial eclipse re-emerging. It's amazing how distinct the shadow leading to the penumbra.

My friend said there will be some good shots in tomorrow's paper. I laughed and said here in the 21st century I could probably get a high res online right now that matched the view I had through my binoculars, with the moon sitting on a bed of a few dozen stars.

A Twitter search for #lunareclipse gave me this gem from Australian based astronomer Katie Mack.

Another #LunarEclipse moment, showing gorgeous blood-red Moon just before clouds swallowed it. #Melbourne pic.twitter.com/aHSFix60MQ
— Katie Mack (@AstroKatie) October 8, 2014

Not quite hi-res, but certainly true to the view I had. Bring on the next of the tetrad.

My friend also suggested I could listen to update on the wireless, then I wondered at how the utilisation of radio waves were so revolutionary - being able to transmit signals with no telegraph poles.

I wondered what bit of kit we have today that our grandchildren will snigger at - and I figure it's probably smartphones.

"What do you mean we can't send signals by thought?"

Katie posted a few other great snippets, just as Neil Tyson often does with physics. I'll leave you to ponder this gem

Won’t it be wonderful when we have people living on the Moon, watching eclipses in the other direction? #LunarEclipse
— Katie Mack (@AstroKatie) October 8, 2014

APEX 5 not so near

It seems I may have jumped the gun a little.

Idioms - the bane of ESL speakers?

Reading Anton's comment on a Friday afternoon got me a little too excited - upon reading it again Tuesday (we had a long weekend) I realised it was tongue in cheek.

So instead I went to Twitter to find what's being said at Oracle Open World about #orclapex. Turns out there will be a third early adopter - a feeling I got from something I read back in March.

Now the big news: #orclapex EA 3 in the works. Also a formal beta program for the first time! #oow14
— Scott Spendolini (@sspendol) September 29, 2014

There will also be a beta program for the first time - details to come since so far the only source of this information so far (for those not at OOW) is Twitter.

Who needs reporters anymore when you have real time feeds & photos from actual events? ;p

APEX 5 imminent?

It seems the release of APEX 5 is due within hours - just in time for OOW weekend.

To quote Anton here

Apex 5.0 is going to be released in a couple of hours

It's a long weekend here in Perth, and I have a footy grand final to watch - but no doubt I'll be installing it somewhere soon!

All that being said, apex.oracle.com is still on 4.2.5, so don't hold your breath for too long.

Stay tuned...

Leisure Suit Larry Ellison

Larry Ellison 1978

So at 70 years of age, the man who's been heading Oracle since before I was born is stepping down.

While I admit I'm far removed from the reality of the situation, I find it a little amusing how it's been deemed as a 'shake up' when a few paragraphs before he's quoted as saying this (referring to new co-CEOs)

“I am going to continue to do what I have been doing the past several years and they are going to continue doing what they have been doing the past several years,” 

So I bet he's probably going to be working in the same office, just the title under his name on the door will read "CTO". So for us at the coal face, surely this means he will spend more time directing the nature of the beast and leave operations to those who specialise. That sounds to me like a good thing.

I enjoyed this New York Times article, but writer Quentin Hardy made me read one of the last paragraphs a few times

Mr. Ellison does not leave his company entirely untroubled. Besides continuing challenges in cloud computing, including acquisitions and new competition, the company faces a raft of new types of databases, first developed inside Google and Yahoo, that also threaten the dominance of the relational database.

I've been thinking recently what the next generation of databases will be like. The relational model was revolutionary at the time, but bigger data and intelligent networking will surely one day reach another punctuated equilibrium. No doubt it will come from somewhere like contemporary giant Google.

If I was a passionate billionare I think I would have left the business behind long ago, along with the suit.

Circa 1993 - no eyebrows then either

Using Post-Authentication to run process after APEX login

A frequent requirement, and hence frequent question on the forums is

How do I run procedure x after the user has logged in?

This is often required for such tasks as determining user access, such as populating a restricted Application Item relating to a role like F_IS_ADMIN based on the username defined in substitition string APP_USER

Those new to APEX and unfamiliar with certain concepts may consider using an Application Computation, firing "On New Instance (new session)"

Application Computation Frequency

It sounds fair enough, and I remember doing the same thing when I was first learning APEX. The documentation on understanding page computations states

The computation point On New Instance executes the computation when a new session (or instance) is generated

This still isn't clear, but this actually fires when you first navigate to a page in your application - any page. This means when you first open a page like /apex/f?p=100:1, which may redirect you to the login page - the 'on new instance' event has already fired since APEX needed to provide you with a new session to render the home/login page.

In other words, these events can be generally described in the following order:

• Open home page
1. On New Instance (new session) - APP_USER is 'nobody'
2. Redirect to login page
• User enters username/password and submits
1. Pre-Authentication 
2. Validate credentials - APP_USER now set
3. Post-Authentication
4. Redirect to relevant page

I think I've only used 'On New Instance' once or twice, possibly to prime content of application items - but I use 'Post-Authentication' all the time to calculate values based on the user who just logged in.

Post-Authentication is defined in the Authentication Scheme, and expects the name of a stored procedure.

Shared Components -> Authentication Scheme

This stored procedure can be defined within the Authentication Scheme -> Source -> PL/SQL code as an anonymous block:
PROCEDURE post_authentication IS
BEGIN
  -- do stuff here
  null;
END post_authentication;

For better performance and code management you should place it within a PL/SQL package. That way it doesn't need to be interpreted dynamically every time a user logs in.

The Post-Authentication Procedure Name attribute just needs the name of the stored procedure, no semi-colon.
apx_auth_util.post_authentication

The PL/SQL may run something like:

IF v('APP_USER') = 'WESLEYS' THEN
  apex_util.set_session_state('F_IS_ADMIN','Y');
END IF;

I understand changes are coming in APEX 5 regarding when these events fire as you navigate between applications that share authentication.

This also reminds me of a little experiment I wrote ages ago to determine the order of page/application events.

Birds don't make the best ornithologists

This week I listened to episode 44 of one of my favourite podcasts - Inquiring Minds. It was an interview with David Epstein on the 'science of athletes'.

The podcast culminated with some really interesting points regarding the concept of 'talent' - what it really means, how it can be measured or influenced. One particularly interesting basis was the traditional idea that '10000 hours' training leads excellence in any particular skill, vs what's in the genes.
Exhibit A: Kalenjin (regional Kenya) long distance runners. Well worth the listen to get the full detail.

Another interesting example was a female softball player Jennie Finch that made the news in 2004 striking out professional male players. The men expected to have no problems hitting her out of the park, but players confused their perceived reliance on ability to track ball in flight and rely on 'talented' reaction time with what they actually do.

(Getty Images)

In fact, this article I found when searching for Jennie Finch goes into very similar detail (since it's written by the interviewee!) and suggests

Given the speed of the pitch and the limitations of our physiology, it seems to be a miracle that anybody hits the ball at all.

It's apparent that players interpret the pitcher's shoulder, body rotation, ball thread rotation in flight and other factors to calculate the region the hitter expects the ball to fly by. But since thrown underarm, all information players read to calculate where the ball will land - is lost.
They've even got a bigger, slower ball to hit.

They found likeness to chess, where a good player sees the entire board - a number of moves ahead.
I found likeness to pool/snooker. After the break I might see the next 4-5 balls I can pot in a row. Newer players are often overwhelmed just to work out which one to try.

I'm sure my game would slow right down if was faced with a different shaped table.

Pool tables I wouldn't enjoy

I don't do the topic justice, but I recommend both this particular episode and the podcast in general. I listen to podcast in my commute to work - I play to post my favourites one day, I'd be interested to hear any other recommendations.

The blog post title? A quote from David during the interview

OTech Magazine Issue 4 - APEX 5 and much more

Well it's the second half of August and this is my first real post for the month. There are two very good reasons for this:

1) We've just completed a major implementation at one of our clients - a tablet based application using APEX, which completely changes the face of a major arm of the business. It's a groundbreaking application with only one peer in the industry - which users are already claiming superiority to and it's had years to mature. It's been a very enjoyable project with a glowing success story - kudos to my Sage colleagues and the entire team.

2) Douwe Pieter van den Bos from OTech Magazine asked if I would like to contribute to their next quarterly issue, but with a catch - it had to be submitted pretty soon as they were already finalising the content. I hadn't actually heard of it until I received a LinkedIn request from Douwe. This is only the fourth issue so I don't feel too bad. I browsed through the previous issues and read what the magazine is all about and it was something to which I was more than happy to contribute.

I think Douwe had seen my series of blog posts on APEX 5 and asked if I would like to add something on that. I also recently presented on the topic in Perth, so I figured my Prezi was something I could quickly adopt into prose. It was a bit of a rush job and I feel it shows a little, but I plan to do the presentation again as an ODTUG webinar, so stay tuned if you want to hear the original format.

OTech Magazine #4

OTech Magazine is free, independently produced, well polished, digitally formatted for Oracle professionals. The editors carefully select authors & articles in efforts to obtain high quality content. My APEX 5 (EA2) article is in the just released "Summer 2014" issue. Yep, I quoted that because I'm not sure that global products should relate to a season - especially since I'm currently living in a "winter" climate. Yep, I quoted that too because we're enjoying mostly 22C sunny days - I blame climate change.

It confuses the heck out of me when I see a movie advertised for release in Winter 2015 - what range of months is that?!

Anyway, check out the OTech Magazine - you'll find yourself wanting to read the past issues.
http://www.otechmag.com/2014/otech-magazine-summer-2014/

Demystifying Oracle Unpivot

A couple of years ago I posted a simple example using PIVOT, converting rows to columns with the classic example of figures by months.

Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.

Problem

I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.

create table aus_attractions(id  number, city varchar2(50)
  , attraction1 varchar2(50)
  , attraction2 varchar2(50)
  , reason1 varchar2(50)
  , reason2 varchar2(50)
  );
insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand');
insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic');
insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds');

select id, city
      ,attraction1,attraction2
      ,reason1, reason2
from aus_attractions;

Not optimal relational data design

I'd like to see each attraction by row - 6 rows instead of 3.

You could solve this with a UNION ALL, and/or a WITH - but one day "they" will ask for 5 options, might as well unpivot.

Solution

Then we can turn our original statement into an inline view, serving the unpivot function.

select id, city, attraction, reason, rec_nbr
from ( -- original query:
     (select id, city
         ,attraction1,attraction2
         ,reason1, reason2
   from aus_attractions
   )
 unpivot               -- the magic operator
 ((attraction, reason) -- names of replacement columns
  for rec_nbr in (     -- new column defining data source in literal alias below
   -- split each group of fields in here
   (attraction1, reason1) as 'REC1' 
  ,(attraction2, reason2) as 'REC2'
  )
 )
);

ID      CITY       ATTRACTION        REASON       REC_NBR
------  ---------- ----------------- ------------ -------
1       Perth      weather           sunny        REC1
1       Perth      beaches           white sand   REC2
2       Sydney     bridge            climb        REC1
2       Sydney     blue mountains    scenic       REC2
3       Melbourne  culture           activities   REC1
3       Melbourne  aussie rules      crowds       REC2

6 rows selected

Awesome.

Unpivoted data

Simple, once you've done it the first time...

Documentation

Oracle SQL Language Reference
Oracle Data Warehousing Guide - SQL for Analysis and Reporting

Other great examples of varying depth

OTN - Arup Nanda
Oracle-Base - Tim Hall
Oracle FAQ - Unpivot
Oracle-developer.net - Adrian Billington
AMIS - Lucas Jellema
SQL Snippets: Columns to Rows - UNPIVOT (11g)

If you're not already using the above sites for good reference material, you're missing out.

Also check out this example demonstrated at live.oracle.com

Case sensitivities and making JavaScript more like PL/SQL

Yesterday the Anti-Kyte (a.k.a. Mike) published a great post on coding styles. I started to post a comment but ended up rambling to I thought I'd put my thoughts here.

I can't be one to comment on tangential intros (just look at any of my presentations), but he almost lost me when I thought the entire post was going to be soccer jokes I didn't get but switched in time to three controversial topics:

1. Uppercase keywords
2. Camel case
3. ANSI joins

To add my 5 cents - I still tend to uppercase keywords - partially out of habit and sometimes standards are a client site. I still like the concept of the brain finding lower case easier to interpret than upper case - now with extra help thanks to syntax highlighters. I don't mind all lower case, though I feel a little naughty when doing so. I know Connor does it all the time, and it looks raw & simple - which is not a bad thing.

He scared me a little when he started to provide PL/SQL examples using camel case - I can't stand camel case.
My identifier naming conventions in JavaScript tend to match up with my PL/SQL preferences, though sometimes I find it to be a hybrid in occasions where the name might harmonise with other jQuery functions.

function p42_show_info (t) {
  theRow = $(t).closest('tr').addClass('highlight');
  l_order = theRow.children('td[headers=ORDER_NO]').text();
...

I should probably stick with one way or the other since bugs thanks to tolerated case sensitivity are the worst kind of bugs - parasites, if you will.

I have no problems using ANSI syntax when used explicitly (no natural joins), but I tend to only use them for outer joins and when readability would benefit. There are scenarios where ANSI outer joins are the best solution to a query.

Scott

APEX Printer Friendliness using CSS Media Queries

In the projects I've been involved with I've rarely seen it used, but there is a ninth argument in the f?p= syntax that toggles Printer Friendly mode.

Side note - being the space geek I am this reminded me of the hubhub over Pluto being the ninth planet.

The APEX documentation on Printer Friendly mode states

When referenced, the Application Express engine does not display tabs or navigation bars, and all items are displayed as text and not as form elements.

You also need to take care with the construction of your theme's Printer Friendly page template to help make this happen. This template is included in the theme by default and should have the namely template class.

However, there is another method you may like to consider to prepare your pages for printing - without needing to re-open the page - CSS Media Queries.

Here I have a print button on a demo page which invokes the browser's printer dialog box using window.print();

Basic report with Print button

Trouble is all my menus and sidebars are included in the print preview.

Printer preview including expanded menu / sidebar

CSS Media Queries aren't just for making your website respond to the gamit of screen sizes that exist, but also allow you to interrogate the media type. We can use this to identify components on our page that should not be displayed when printed.

@media print {
  #navwrap, #blog_posts, #p31_detail, #p31_notes, .uButton
 ,.noPrint
    {display:none;}
}

You could either identify the page components in the CSS media query - here I've identified my menu bar, side bars, second region at the bottom of the page and any buttons.

Alternatively you could apply a class to all components you don't want printed - eg: .noPrint.

The HTML shown in the print preview is now much cleaner when I click the printer button.

This HTML document is ready to kill a tree

Of course if you have various form elements you may still want to consider a combination of media queries and the use of the Printer Friendly mode. While looking for documentation links I also stumbled across this example by Andrew Tulley.

Run the demo to see it in action. Try view the print dialog before pressing the 'Apply Media Query' button

There is a more comprehensive breakdown on how to action this for UT here by Sandro
https://www.sotful.com/sandroferreira-blog/quickest-print-friendly-in-oracle-apex

References

CSS Media Queries
W3Schools Media Type
APEX Documentation - Printer Friendly mode
Grassroots Oracle - f?p syntax mnemonic
Andy's Blog - hybrid example
Smashing Magazine - tips and tricks for print style sheets

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;

VC

--
1
11
2
a

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.
1KB
1MB
2KB

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.

'<!--'||LPAD(dbms_lob.getlength(blob_content),10,'0')||'-->' 

||TRIM(apex_util.filesize_mask(dbms_lob.getlength(blob_content)))

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.
DBMS_RANDOM.STRING('a',length(filename))
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.

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
  ,doc_size
  ,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 4.2.0.0

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:
1KB
1MB
2KB
***/

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.

Scott

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.

Scott.

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

Scott

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!

Oracle APEX 5 EA2 ready for consumption

After last night's teaser I thought I'd try apexea.oracle.com 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

Just in time for #KScope14 - announcing Oracle Application Express 5.0 Early Adopter 2! http://t.co/1FfvcsQHRo #orclapex
— Joel R. Kallman (@joelkallman) June 18, 2014

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!

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 apexea.oracle.com 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!

SQL Analytics - Ranking with ordinal suffix

SQL Analytics provides a fairly simple mechanism for determining positional rank within a set of results.

Before I demonstrate that query - which is already found in many good libraries - I thought I'd show how we could take it a step further and add the ordinal suffix (st, nd, rd, th) to a result.

We can do this using date format masks

with placing as (select rownum rn from dual connect by level < 5)
select to_char(to_date('2013-01-'||rn,'yyyy-mm-dd')
              ,'fmddth') ordinal_suffix
from placing
/

ORDINAL_SUFFIX
--------------
1st            
2nd            
3rd            
4th

After adding the year/month to our position, we convert the result to a date - then convert it back to our desired output using TO_CHAR. The "fm" removes the leading zero, and we can obviously ignore the year/month from the output. On a side note, something I discovered while writing this query is the inability to concatenate values in the ANSI date expression.

select to_char(date '2013-01-'||1,'fmddth') from dual;

ORA-01847: day of month must be between 1 and last day of month

If you know a way around this, I'd be happy to know.

Now we can combine this expression with the dense_rank() analytical function.

select ename, sal
  ,rank() over (order by sal desc) rank 
  ,dense_rank() over (order by sal desc) dense_rank 
  ,to_char(to_date('2013-01-'||dense_rank() over (order by sal desc),'yyyy-mm-dd'),'fmddth')  rankth
from emp

ENAME             SAL       RANK DENSE_RANK RANKTH
---------- ---------- ---------- ---------- ------
KING             5000          1          1 1st    
FORD             3000          2          2 2nd    
SCOTT            3000          2          2 2nd    
JONES            2975          4          3 3rd    
BLAKE            2850          5          4 4th    
CLARK            2450          6          5 5th    
ALLEN            1600          7          6 6th    
TURNER           1500          8          7 7th    
MILLER           1300          9          8 8th    
WARD             1250         10          9 9th    
MARTIN           1250         10          9 9th    
ADAMS            1100         12         10 10th   
JAMES             950         13         11 11th   
SMITH             800         14         12 12th   

 14 rows selected 

Cool, huh?

Analytical functions essentially calculate another column of values based on the data queried. I've included 3 examples

1. "RANK" - demonstrates most of it is semantics, in this case you only need to provide which column you would like the ranking to order with.
2. "DENSE_RANK" - shows slightly different rules in the numbers generated in the rank. ie - do we get a bronze?
3. "RANKTH" - combines the ranking with date formatting to make it look cool

Probably nifty for these soccer world cup APEX apps I hear people are creating... just don't try go above about 30 places ;-)

SQL Analytics 101 - Break columns

SQL analytics can be used to generate break columns in your queries, without the need for break formatting attributes in APEX or the old fashioned break on option in SQL*Plus.

I came across an example recently where I wanted to apply the break formatting in my query to avoid extra sub-totals from being displayed after each break.

No sub-totals please

I could use jQuery to hide the rows instead of modifying them, but as Tom Kyte says - if it can be done in SQL, why not? (or something like that...)

And it's less work for the database

select case when row_number() over (partition by d.dname order by d.dname, e.ename) = 1 then d.dname end dname
  ,e.ename, e.job, e.sal, e.comm
from dept d, emp e
where d.deptno = e.deptno
order by d.deptno, e.ename

The row_number() clause allocates a distinct row number for each set of departments (partition by clause).
The case statement only shows the department for the first row - and we need the order by clauses to match up to keep things neat.

A simple report demo is available here.

These are the results if the query was run in SQL Developer, with the row_number() clause also in it's own column.

RN         DNAME          ENAME      JOB              SAL       COMM
---------- -------------- ---------- --------- ---------- ----------
         1 ACCOUNTING     CLARK      MANAGER         2450            
         2                KING       PRESIDENT       5000            
         3                MILLER     CLERK           1300            
         1 RESEARCH       ADAMS      CLERK           1100            
         2                FORD       ANALYST         3000            
         3                JONES      MANAGER         2975            
         4                SCOTT      ANALYST         3000            
         5                SMITH      CLERK            800            
         1 SALES          ALLEN      SALESMAN        1600        300 
         2                BLAKE      MANAGER         2850            
         3                JAMES      CLERK            950            
         4                MARTIN     SALESMAN        1250       1400 
         5                TURNER     SALESMAN        1500          0 
         6                WARD       SALESMAN        1250        500 

 14 rows selected 

SQL analytics are worth wrapping your head around - they can offer simple solutions to common problems.

Enhancing APEX "no data found" message

Truth be told, the default "no data found" message for APEX reports is pretty boring.

Out of the box

Even the default wording irritates me just a little bit every time I see one amidst a page I thought was looking pretty snazzy.

There are two things I've done recently to make it look a little better.

1) Surround it with a nice soft coloured box.

Something with a little pop

This is pretty simple, just add the following CSS to your page (Edit page properties -> Inline CSS) or application's .css file. Tweak it to your heart's content - just no blinking text, please.

span.nodatafound {
  font-size:120% !important;
  border: 1px solid #FC0;
  background: #FFC;
  color: #384F34;
  display: block;
  font-weight: bold;
  margin: 2px auto 14px;
  padding: 15px !important;
  text-align: left;
}

2) Customise the output

Speaking of cats (if you clicked through the reference), there are probably a few ways to skin this one.

If you create a dynamic action the fires after refresh of your report region, you can execute this JavaScript

var ndf;
switch($v('P4_REPORT_SEARCH').length)
{
case 1:
  ndf="One character and you found no records?!";break;
case 2:
  ndf="Make sure there is no type";break;
default:
  ndf="No employees by this name, sorry.";
}

$('span.nodatafound').text(ndf);

And that's it!

See a demonstration on this sample search page. Note it refreshes the report on key release. Try type letters not found in the emp table.

Applying the APEX 4.2.3 patch

My experience patching Oracle APEX v4.2.1 to 4.2.3 on my Windows 7 laptop.

Background

Personally my main driver for this is to experience the updated themes and templates. I'd also like to explore the packaged applications and see if any of the dynamic PL/SQL regions have been converted to templates.

I already had 4.2.1 installed and before the patch was available to me I thought I'd try running scripts on the full download of 4.2.3

It turns out if you attempt the apxpatch.sql script you get
ORA-39702: database not open for UPGRADE or DOWNGRADE

If you run the apxins.sql
Error: This version of Application Express is already installed (APEX_040200).

So you kinda need to use the patch. If I had more time and a virtual machine ready I would see if it were possible to revert to 4.1, then upgrade.

Process

First I closed my APEX Listener stand-alone console, preventing myself from playing with APEX in the meantime.

I'm still running on Oracle 11g XE, so I run the non-CDB option (referring to 12c container databases)
This means I unzip the patch p17347169_423_Generic.zip to my c:\apex4.2.3 folder.

I then open a cmd window, change directory to that folder and run SQL*Plus as SYSDBA, then run the patch script.
cd \apex4.2.3
sqlplus sys as sysdba
@apxpatch.sql

While that was running I need to manage the images folder. For me I decided this meant copying custom content from c:\apex4.2\images to my new c:\apex4.2.3 folder.
This was a /sage folder that houses my training supplementary files
I also decided it was time to create a /scott folder to store my nick-nacks I play with, instead of dumping that in the root /images folder.
I didn't have anything under /themes I needed to transfer.

This means I modify my start_listener.bat file that launches APEX Listener stand-alone to
cd c:\apex_listener2.0.1\
java -jar apex.war standalone --apex-images \apex4.2.3\images

The next thing I did was open a browser tab to see what the latest APEX Listener version was - and if there's anything that would compel me to upgrade.

After all that, the patch script finished in 28 minutes (on my laptop), and I was ready to restart my listener and log in.

I initially had a blank page with a bunch of resource GET errors - but then I realised I had a typo in the path of my start listener batch file.

At runtime, there is a difference in the developer toolbar - can't we just leave these things alone? ;-)

Difference spotted between 4.2.1 and 4.2.3

Bring on APEX5.

Millions of big androids

This latest analysis on the population of mobile device technology got me thinking more about the typical sized screen you might encounter as a global web developer.

Canalys Q1 2014 Smartphones: 81% Android, 16% iOS, 3% Windows Phone http://t.co/ty3E1vSysn
— Kellyn Pot'Vin (@DBAKevlar) May 13, 2014

So it's looking at 2014 Quarter 1 shipment of nearly 280 million smartphones. 81% are now android, only 16% related to Apple - that next iPhone better be a seller!

The telling statement for me was regarding a third of those shipped:

Smartphones with 5″ and larger screens grew 369 percent, substantially faster than the overall market

I've been working on an APEX project for delivery on an Apple Air - building for a table sized interface has been fascinating. I can only imagine how different a functional APEX application for a 5" screen would need to be.

APEX 4.2 Application builder icon size

A common bug-bear for me is the large distance on web pages between the browser toolbar and the start of any real content on the page.

I remember providing feedback during a tryapexnow beta release on the issue

Gimme content without scrolling!

I can't expect too much - while news websites are frequent offenders for this, newpapers have also been doing it for years.

Pravda 1912

Today I (re?)discovered how to make these buttons smaller - by accident, while clicking between windows.
This was on 4.2.0.00.27.

It rings a vague bell...

Some developers may prefer it. Some developers just do this

Samsung physical monitor rotation

I've finally go the opportunity to try it - I've always preferred height to width.
It's great for APEX and my text editor, but there is still software that works better in landscape - Balsamiq for instance.