Month: October 2008

Add colours to your Shuttle item

I wanted to allow users to select one or more colours from a list, and to control the order of the colours, so I’ve used a Shuttle item.

I wanted to have different background colours for each value in the list, so I started here for help. I’m not very strong with javascript (yet) but with a bit of looking around and playing I ended up with what I wanted.

I created an item on the page with the following attributes:

Display As: Shuttle

List of values definition: STATIC2:Yellow;#FFFF00, Green;#00FF00, Turquoise;#00FFFF, Pink;#FF90FF, Blue;#9090FF, Purple;#FF00FF, Red;#FF9090

Post Element Text:
<script type="text/javascript">
(function() {
for (i=0;i<$x("#CURRENT_ITEM_NAME#_2").length;i++) {
= $x("#CURRENT_ITEM_NAME#_2")[i].value;
for (i=0;i<$x("#CURRENT_ITEM_NAME#").length;i++) {
= $x("#CURRENT_ITEM_NAME#")[i].value;



  • the value of each item in the list is a HTML colour code. This colour code is used to set the background colour of the item in the list.
  • the shuttle item actually involves two select lists in the generated page. If the item name is P1_SHUTTLE, the generated items will be P1_SHUTTLE_2 (the left-hand list) and P1_SHUTTLE (the right-hand list). These are referenced in the javascript via #CURRENT_ITEM_NAME# and #CURRENT_ITEM_NAME#_2.
  • the $x returns the select list dom object, which supports the “length” attribute – this returns the count of items in the list
  • the select list index starts at 0 and goes up to length-1

A small problem is when the “reset” button is clicked the colours disappear. They reappear if the page is refreshed, however.

FRM-40654 “Record has been updated by another user”

There are several reasons you might get this error in Oracle Forms, e.g. another user has modified the record before you saved it (as the error message suggests), or a table trigger has modified the record and your form’s DML Returning Value is set to No.

Another cause to chalk up is what one of my colleagues got today. He made a form with a simple table-based block, he would insert a record and save successfully, then try to modify it and consistently got FRM-40654 when he tried to save. No other user was trying to update the row, and there were no triggers on the table.

The answer? The table is an index-organised table, but the block on the form had Key Mode set to Automatic. I suspect the form is comparing the ROWID of the before-and-after change, but because the table is index-organised the ROWID is not necessarily constant. Whatever the reason, changing the Key Mode to Updateable or Non-Updateable solves the problem.

Apex, More Apex, a bit of PL/SQL, ACEs and Babbage

One of the worst times to get an ear infection, I learnt, is the night before you present at a conference. I hardly got any sleep and I’m deaf in one ear, making my voice echo in my head.

I survived relatively unscathed and there seemed to be a good level of interest in the room so that was encouraging. I knew that I had a lot more material than I had time to present, so I went pretty quickly, and in the end managed to get through about 80% of the slides and the bulk of what I felt was important.

If you would like a copy of the presentation you can download it from here [JeffKemp_Apex_Social_Networking.pdf]. You can also download an export of the sample application “Zample” [] if you want to play with it yourself.

After that I stayed to hear Scott Wesley talk about some creative uses for conditional compilation, and heard David Peake talk about the imminent Forms Conversion process in Apex 3.2. Personally I don’t see much potential for actual forms conversion projects, but that’s because every Forms application I’ve worked on has a lot of business logic in the triggers, and “creative” uses for all the features of Forms which will never translate into Apex. I can, however, see a use for this tool to take all the forms from a legacy app and expose all that logic in Apex for analysis. It could be used to document an existing system which may save some time when designing a replacement system in Apex/JDev/whatever.

It wasn’t just David who spoke on Apex, I enjoyed Penny Cookson/Eddie Harris’s talk “How Ugly is that Apex Report?” in which she gave a few options for making reports out of Apex page regions, and more options for those who are lucky enough to have BI Publisher. They also demonstrated what you can do with Cocoon. I haven’t dabbled in Apex reports at all (most of the time I’ve made Apex applications to avoid the need for printed reports) and hadn’t heard of Cocoon.

Tim Hall spoke on PL/SQL best practices for performance, which was pitched more at a mid-level PL/SQL programmer but good none-the-less. After that, another David Peake presentation revealed the roadmap for the future of Apex. I like what I’m hearing – version 4 will enable a number of Ajax-powered features declaratively, something I’ve been looking forward to. The standout, of course, is the enhancements to the Interactive Reports which allow users to edit the data in-place. His final presentation on UI presentation techniques focused primarily on the new Interactive Reports feature that is available now in 3.1.

I decided to end the day at Scott Hollow’s talk “Babbage vs Oracle” in which he compared/contrasted the lives and times of Charles Babbage (arguably the father of computers) and Larry Ellison. Scott has a passion for computer history and it showed.

There was a session mysteriously entitled “ACEbook” which wasn’t well attended, probably because it wasn’t clear what it was about. Turns out they got together a panel of Oracle ACEs and ACE Directors and discussed being an ACE, what the difference was between ACEs and ACE Directors, and advice on contributing to the Oracle community. I had a few questions but they ran out of time so I’ll ask them here:

  1. What killer new feature would you like to see in Oracle 12g? and
  2. What super power did you gain when you became an ACE/ACE Director?

AUSOUG Perth Conference

I’ve been in bed the last few days with a virus, but I should be ok in time for Monday. Had a look through the programme, and again it’s chocablock with very interesting titles. Quite a bit of Development Blue, but not so much DBA Cyan this time (unfortunately, in my opinion). There’s a lot of Apex sessions which I’m guessing reflects the growth of interest in Apex development here in Perth.

2008_Program_perth.pdf via

Highlights for me include:

  • 11g New Features for PL/SQL developers – Dr Timothy S Hall
  • Creative Conditional Compilation – Scott Wesley
  • What is coming in Oracle Application Express and SQL Developer – David Peake
  • How Ugly is that Apex Report? – Penny Cookson
  • Back to basics: Simple database web services without the need for SOA – Chris Muir
  • 11g features for developers – Connor McDonald
  • Boost performance with PL/SQL programming best practices – Dr Timothy S Hall
  • The SQL and PL/SQL Results Cache – Penny Cookson
  • Converting from Oracle Forms to Oracle Application Express – David Peake
  • Advanced UI Presentation Techniques – David Peake
  • Babbage vs Oracle – Scott Hollows

Unfortunately, I won’t be able to attend all the above sessions so I’ll have to make some hard decisions :)