Happy Friday - something from Isaac Asimov

"which I repeat here for everyone that find's this page"

Well Morgan, I found the page.

The backstory - I've been tidying my bookmarks, and I read through ACE Director Daniel Morgan's thoughts for 2013.
(For those Oracle technologists who haven't seen his Library, bookmark it now.)

Anyway, he referenced a book dedication from Isaac Asimov which I thought was a little terrific.

To Mankind

And the hope that the war against folly may someday be won, after all

After reading about the book, it's now on my list.

Happy Friday, Isaac

APEX 101 - Set region title on page load

I saw a forum post recently asking how to dynamically change the region title.

This is a little ambiguous - is this dynamic to the value set in APEX meta-data, or dynamic as a result of user events on the page?

Here is the instructions for the former:

1) Create new hidden item called P6_REGION_TITLE. I placed it on the same region as my form items.

2) Create a computation for this item Before Header, with perhaps a SQL expression like

Region title computation

This extends the concept of Pn_MODE demonstrated in my sample application, discussed in this tutorial. You could also interrogate Pn_ID to determine if reading an existing record or creating a new one.

CASE :P6_MODE 
WHEN 'R' THEN 'View employee'
WHEN 'E' THEN 'Edit employee'
END

3) Modify the region title to
&P6_REGION_TITLE.

When the page renders, the region title will be set relevant to the edit mode.

Here is a post how to modify the title after the page has rendered - often useful for modal dialogs or master/detail pages.

Scott

Customise Totals row in APEX classic report

This post covers one way of customising the declarative sum totals on classic APEX reports.

Classic Report sum totals

The default output isn't that flash looking, and no doubt many would prefer at least a different label. Unfortunately I haven't found a method within APEX, so I applied some jQuery.

If you're not aware, these are turned on using the checkbox in the 'Sum' column on the relevant region's 'Report Attributes'.

Classic Report Attributes

So to customise the label, first we should add a Static ID on the region attributes. This will be used to help identify the report.

Static Region ID

Then we can use Chrome's 'Inspect Element' option upon right-clicking the label. Using this we can identify more of the jQuery selector we'll need to identify this cell.

Using the browser to identify DOM components

Now I'd still consider myself beginner/intermediate when it comes to jQuery. I know how to adopt code well, but I'm not yet familiar with the full suite. So if any of you have suggestions on how to improve this bit of code, especially from a performance perspective - I'd be happy to hear them. I've resigned myself to never being an elegant OO programmer, but fire away if I've also made this ugly.

jQuery code running as part of page render

$('#emp td.data').each(function(){
     if ($(this).text() == 'report total:') {
        $(this).text('Total Salary');
        $(this).css({'font-weight':'bold', 'color':'red'});
     }
});

This code identifies the cell using the selector specified within the $(''). This is the #emp ID we added to the region, and any cell within that report. For each cell it will check the contents to match the default label, then modify that cell as desired.

The final product

So add CSS to your heart's content. I'd be happy to hear people's ideas on how to add snaz.

Simple page showing it in action.

Extra challenge : find a way to extend the selection to locate the cells with numeric totals.

Suggested references

jQuery selectors
Related OTN forum post

APEX 101 - Server-side Dynamic Actions

Dynamic actions are awesome.

Let's so you have a scenario where after you change the value of one item, you would like to validate it or perhaps calculate the value of a separate item - or both!

Given an entry of Radius, I would like to calcular Sphere area.

Let's add an Oracle Forms style when-validate-item

This requires a relatively simple Dynamic Action that fires on 'Change' of the field P16_RADIUS.
In this case no condition is required - any change will fire the action. You could ensure the value is above zero, for example.

Dynamic Action properties

The action itself contains the PL/SQL formula.
:P16_SPHERE_AREA := POWER(:P16_RADIUS,2) * 4 * 3.141592;
This can be any anonymous block, or call to a PL/SQL package.

'True' action properties

The most important part of this action are the 'Page items to Submit' and 'Page items to return' fields.
This is what passes information from the browser to the database (session state) and back again.

In this case, the P16_RADIUS value is sent from the browser to the database so it can be used in the PL/SQL expression calculation, then the resulting P16_SURFACE_AREA is set in the browser.

Note some of the other options facilitated by the checkboxes.

The flexibility that dynamic actions provide give me just cause to vote them the best feature of APEX 4.x, just above plug-ins.

Other links

Inspired by this forum post, amongst many others like it.
For a more in depth tutorial, check out this Oracle-by-example.
This example is among others on session state in a page of my sample application.

Friday Fun : Tim Minchin

I like Tim Minchin.

One year I took my wife to see him with West Australian Symphony Orchestra (WASO) at Kings Park in Perth - now she likes Tim Minchin.
It's a shame he didn't perform his 10 minute beat poem, but that's best watched animated, thanks to a project led by Tracy King.

Just recently he received an Honorary Degree of Doctor of Letters for his contribution to the arts - a field in which he's a master at melding with science.
I think if you watch his acceptance speech, I think you'll also like Tim Minchin. 

Note - the recording automatically shows Tim's portion, keep watching to see his introduction.

Advert: SAGE APEX 4.2 course

SAGE Computing

Attention Perth residents - and perhaps those interstate...

SAGE Computing are holding a 4 day Oracle Application 4.2 course from the 28th October, 2013.

Please contact me or Penny if you're interested in attending.

Scott.

Formatting 101 - Nested single row functions

Sometimes to get the data displayed exactly how you want it, you need to employ some nested functions.

with t as (
  select null n from dual union all 
  select 0  from dual union all 
  select 0.5 from dual union all 
  select 4 from dual union all 
  select 4.5 from dual)
select to_number(n) verbatim
  ,TO_CHAR(n,'fm90.9') no_trail
  ,TO_CHAR(n,'fm90.0') trail_zero
  ,RTRIM(TO_CHAR(n,'fm90.9'),'.') trail_decimal
  ,RTRIM(TO_CHAR(NVL(n,0),'fm90.9'),'.') and_null
from t
/

  VERBATIM NO_TRAIL TRAIL_ZERO TRAIL_DECIMAL AND_NULL
---------- -------- ---------- ------------- --------
                                             0        
         0 0.       0.0        0             0        
       0.5 0.5      0.5        0.5           0.5      
         4 4.       4.0        4             4        
       4.5 4.5      4.5        4.5           4.5    

I'm curious - does anyone have formatting techniques the prefer/hate to help follow the brackets & commas?

For example, I might indent similar to JavaScript, and line up brackets/commas.

<< my_loop >>
FOR r_rec IN (
  SELECT 
    DECODE(this_value
          ,'Y', 'Yes'
          ,'N', 'No'
          ,'Maybe')
  FROM   that_table
) LOOP
  null;
END LOOP my_loop;

Scott