31 March, 2010

Beginning Oracle Application Express 4

That’s the title of the book I’ve agreed to write for APRESS, or at least the working title.

The book will be aimed at new comers to Application Express, introducing them to the environment via the development of a full system and using as many of the new features as possible. However, I’m hoping that it will be useful for people who have dipped their toes in the waters of 3.2 and want to get up to speed on what 4.0 has to offer. The book is due to hit shelved early next year, so it’s a ways off. But as APEX 4 is still only in Early Adopter, that’s probably a good thing.

This is the first time I’ve attempted anything like this, and I’m taking it on with more than just a little trepidation. However my good friends, and business partners Scott Spendolini and John Scott (Co-Authors of the APRESS book Pro Oracle Application Express) assure me that it’s a wonderful experience that I will cherish for the rest of my days! (Actually John said that the two best days are the day you start it and the day you finish, and I believe that to be closer to the truth!)

I’m going to be blogging about my experience. Both about writing the book and about some of the things I learn along the way. So watch this space for more on the book and my travels along the APEX 4.0 path.

Wish me luck!

05 March, 2010

Sumneva Education Slots Still Available.

There are still slots available on many of the Sumneva Education Courses world wide. The training will be covering APEX 3.2.1, but there will definitely be an opportunity to ask questions about the differences in APEX 4.0 and we might even have time to show you some of the highlights.

Brussels, Belgium (Public Classroom)
Introduction to Oracle APEX I - 08-MAR-2010
Introduction to Oracle APEX II - 11-MAR-2010
Advanced Oracle APEX - 15-MAR-2010

Manchester, UK (Public Classroom)
Introduction to Oracle APEX I - 22-MAR-2010

Alexandria, VA USA
Introduction to Oracle APEX I - 12-APR-2010
Introduction to Oracle APEX II - 15-APR-2010

Americas (OnLine)
Introduction to Oracle APEX I - 22-MAR-2010
Introduction to Oracle APEX II - 31-MAR-2010
Intermediate Oracle APEX - 12-APR-2010
Troubleshooting and Debugging APEX - 21-APR-2010

Qualified companies can receive up to 10% off the cost of their booking. Contact us to find out if your company qualifies.

Questions or want private on-site training?
Should you have any questions about the courses, technical requirements or want to discuss private on-site training, please send us a note at info@sumnertech.com or call us at (703) 879-4615 Americas / +44 113 386 9296 Europe . We look forward to seeing you soon!

03 March, 2010

Refreshing Flash Charts with AJAX.

NOTE: This post makes use of internal APEX JavaScript that is not part of the standard public API’s. Therefore it is likely not supported, so use at your own risk!


Yesterday, I ran across this post on the OTN APEX Forums. The problem was basically this.

Say you have a chart which you want to refresh each time someone changes a select list value. Sure you could have a select list with submit, but that would cause a redraw of the entire page and, depending on what else might be on that screen, could potentially incur some wait time on behalf of the user.

Another way to do this would be to use the chart’s Asynchronous Refresh feature, but this would cause the chart to refresh on a set interval, not on an event. And to make this worth your while, the interval would have to be so small (seconds) that you would instigate an unnecessary amount of network traffic.

So how do you get a chart to refresh asynchronously? Lets have a look

First, what I’ve done is to create a simple chart that selects the employees and their salaries from the EMP table. Along with it I created the select list upon which it will depend.

Select List SQL

select null link, ENAME label, SAL value1
from "SUMNEVA"."EMP"
where deptno like decode(:P2_DEPT_NO,'%null%','%',:P2_DEPT_NO)

Select List SQL

select DNAME display_value, DEPTNO return_value
from DEPT
order by 1


If this were a select list with submit, then everything would work fine as the newly selected value for the department would get set in session state and the chart would redraw using that value when the page was refreshed. However i want to bypass the page submit and make the chart refresh without redrawing the entire screen. How can this be done?

We can find the initial clues by looking at what APEX does internally when you turn ASYNCHRONOUS REFRESH on for a chart.

Looking at the chart region’s SOURCE in the REGION DEFINITION tab, shows us that APEX uses a number of replacement variables, including #CHART_NAME# and #CHART_REFRESH#.


It’s the second of these that initially caught my attention. If you turn Asynchronous Refresh on for the chart and run the page, you can then view the source to see the javascript that is inserted in place of the #CHART_REFRESH# Variable.


I won’t go into the details of what this is doing, but the code that does the “heavy lifting” is

apex_RefreshChart (2, '10588887924364140363', 'en-us');

After tracking down the definition of this function, now now that the method has the following signature.

apex_RefreshChart (A, D, C);

A = Current Page Number
D = Name of the chart with the initial ‘c’ removed
C = Language

So we could potentially use this function to our own ends, but to do that we need to know the value of #CHART_NAME#, which is only in context as the chart source is being rendered to the page. What this means to us is that what ever JavaScript function we create needs to be created as part of the rendering of the chart itself.

Editing chart SOURCE in the REGION DEFINITION tab, I added the following JavaScript function below the #CHART_REFRESH# replacement variable.

<script type="text/javascript">
function myRefreshChart(){
var chartName = '#CHART_NAME#';
chartName = chartName.substring(1);
apex_RefreshChart(&APP_PAGE_ID., chartName, 'en-us');

The function basically creates a variable for the chart name and strips off the first character. (This is required for the call to apex_RefreshChart as that function actually prepends the ‘c’ back on to the name parameter). It then calls the apex_RefreshChart function.

Now it would be tempting to think that we could just hook this script to the on_change event for the select list, but there is a problem with that. The chart series query depends on the value of P2_DEPT_NO that is in session state, and if we’re not submitting the page, then the session state value for the item won’t change.

To force the change, we can use APEX’s standard AJAX toolkit; htmldb_get().

As with any AJAX function you need the following things.

  1. Application Item(s) to pass values to the process
  2. An Application Process
  3. A JavaScript function to execute the AJAX call
  4. An event that initiates the function
First we create an Application Item called AJAX_DEPT_NO that will be used to pass the value the user chose in the select list.

Then we create an Application Process called SET_DEPT_NO that does just that.


Now we need to create the Javascript that will do the AJAX call and then call the myRefreshChart() function. You could easily do this in the HTML HEADER, but I like to put these things in their own HTML REGION with a render sequence of AFTER HEADER.

<script type="text/javascript">
function selectChanged(filter)
var get = new htmldb_Get(null,$v('pFlowId'), 'APPLICATION_PROCESS=SET_DEPT_NO',0);
get.add('AJAX_DEPT_NO', filter.value);
var ret = get.get();


If you’re used to using AJAX in APEX, then none of this should be of any surprise to you. We’re using htmldb_Get to call the application process and to send the current value of the select list (represented in the function by the filter variable).

Once the application process is complete we then run the myRefreshChart() function to tell the chart to refresh it’s data.

The last thing we need to do is to put an on_change event on the select list and have it call the selectChange function indicated above. Edit the select list and in the ELEMENT region, enter the following in the HTML Form Element Attributes.


This calls the selectChanged JavaScript function each time the select list changes and passes it a reference to the select list itself.

Put it all together ...

Once everything is in place, we can now run the page and see that every time the select list changes, the graph updated without refreshing the page.

You can see an example of this in the Sumneva workspace on APEX.ORACLE.COM

15 February, 2010

Headed to RMOUG

All my bags are packed, and ready to go....

IN about 2 hours I’ll be stepping out the door to head to the Rocky Mountain Oracle user Group (RMOUG). Both Scott Spendolini and I will be presenting there on behalf of Sumneva.

Scott will be presenting Troubleshooting Oracle Apex on Tuesday, and I’ll be presenting Performance Tuning APEX Applications and Replicating NetFlix Drag & Drop With APEX on Wednesday and Thursday respectively.

If you’re going to be there, please swing by and say ‘Hi’.

09 February, 2010

Warning a user that the form has changed he navigates away without saving.

For those of you who haven't visited the new Sumneva.com site, here's an excuse.

We've added a Tips & Tricks section on the Support tab where we will be sharing little tid-bits of information that we learn as we work with APEX. Our goal is to try to continually add new entries and make this a repository of useful information.

I just posted my first tip - "Warning a user that the form has changed."

Watch for more of these as time goes on, or you can follow @sumneva on Twitter to be notified of new Tips & Tricks.


APEX @ Your Oracle User Group - Mach II

In this post, my new business partner (more on that in another post) John Scott mentioned that if you wanted him to speak at your Oracle User Group meeting about APEX, he'd be very happy too.

Not to miss out on any of the fun I think that I can speak for all four of us at Sumneva and say that if you need someone to speak about APEX at a User Group, or other technical related gathering, then do not hesitate to ask.

The four of us are dedicated to furthering the APEX cause and would be more than happy to help spread the message.

14 January, 2010

New Public APEX classes in Dallas Texas

We've just added several Dallas, Texas based training dates to our public course schedule.

Classroom Based Public Trainingå


Qualified companies can receive up to 10% off the cost of their booking. Contact us to find out if your company qualifies.

Questions, or want private on-site training?

Should you have any questions about the courses, technical requirements or want to discuss private on-site training, please send us a note at info@sumnertech.com or call us at (703) 879-4615. We look forward to seeing you soon!