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.