Friday, 3 September 2010

Apex Post Calculation Computations

A warning - perhaps my musings are different as I wrote this sitting in Melbourne airport, my mind exhausted after a few days of conducting training with not much sleep in the hotel bed.

Once upon a time I was a regular Oracle Forms programmer (and sometimes still current). These days I spend most of my time with Application Express. This makes me happy as I did enjoy mod_plsql - an ancestor (of sorts) of Apex.

Occasionally I notice some parallels between the two, even more occasionally I get around to writing an entry for the world to see - a strange urge for some but it seems that people read even more mundane topics.

There are many attributes available within the Apex environment. By attributes I mean little boxes in the various wizards ready for my to type something in. Sometimes it seems overwhelming. Then I remind myself how flooded with settings the Forms environment must seem. Of course I snap myself back to normal when I think about what I've seen of JDeveloper.

Have you ever wondered what some of these settings do?

Recently I was creating a copy of a data entry form within Apex so I could present a cut-down / read-only version of the page. There were some fields that instead of being Select Lists, I needed to display their descriptive value - not the return value that is stored in the column.

There are a number of solutions to this problem, as with most problems. One solution I came to involved utilising the "Post Calculation Computation" attribute of the item. This means that after I source the item from the database column, I can transform it's value into something else. The obvious solution here would be to pass the value to a function that determines the descriptive form of the value - from some sort of reference code table.

I mentioned forms programming before, right? Immediately I thought of post-query triggers and the pros and cons behind various coding techniques in these triggers. First and foremost was the very same practice of taking a value and converting it to a description. This was an expensive task as not only did it require an extra hit on the database, you needed another round trip from Forms runtime to the application server. The better solution was to incorporate the request within the query - perhaps via a key-preserved view.

The same rings true within Application express. Sure, we don't have another round trip between servers since all the action is happening on the database; however it still requires another select statement to be executed. For a dinky (a Aussie/British colloquialism meaning small and insignificant) little Apex page, what's an extra hit on the ever powerful Oracle database? Perhaps try see what happens when scaling your request to thousands of users.

So perhaps some of our old habits can carry on to this modern day programming tool? I'm certainly not saying this post calculation attribute is not useful. I have another field populated via a list manager with a popup lov. This means the values are separated by a colon. In my application, this field holds a list of e-mail addresses. When I want to present this list to the user in a pretty format, I can use this attribute to convert it to something suitable for a HTML page:
REPLACE(:P1_EMAIL_LIST, ':', '<br>')

Of course if you wish to do this, you may need to ensure your item type does not convert special characters.

It seems my plane is about to call for boarding, so I'll save you all from further ramblings... for now. Enjoy your weekend.


mnolan said...

Hi Scott

The beauty of APEX, there's always a tonne of ways to do the same thing :)

Another option could be keeping the item as a select list and making the item "Read Only -> Always". You will simply get the display value on the page and not the return value. It might be the least expensive operation.

By the way I'm a keen follower of your blog, keep up the good work!


Scott Wesley said...

Cheers Matt,

That was my first consideration, using Read Only = Always, however considering the complexity of this particular page, I made the decision to make a copy of it and strip it right down to basics. That got me thinking about some of these considerations.

Brighton, eh? I didn't quite make it there when I was over a few years ago, despite the best efforts of our group.

mnolan said...

Brighton is a bit like the Gold Coast of England. Replace the sun with rain, the sand with rocks, and the seagulls with ones on steroids and it's a match. But it does have a great pub/music scene, probably more pubs per square mile than anywhere in England. So you can get pretty drunk on a pub crawl and not even make it to the end of one street.

Formerly Brighton though, I must update my profile, just moved to Somero in Finland a couple of weeks back. I'm never going to see the sun again ;) well not for another 10 months...


Scott Wesley said...

That certainly reminds me of England... especially those massive gulls! I spent a bit of time in Burnham-on-sea, and I thought that had plenty of pubs.
I guess in Finland you have a good chance of seeing Aurora, especially with the sun coming back to life after some time of dormancy.