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.