Monday, 8 September 2014

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.

Friday, 29 August 2014

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

Friday, 22 August 2014

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.

Thursday, 31 July 2014

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

Friday, 25 July 2014

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

References

CSS Media Queries
W3Schools Media Type
APEX Documentation - Printer Friendly mode
Grassroots Oracle - f?p syntax mnemonic
Andy's Blog - hybrid example

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;

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.