Joel Kallman

At the ODTUG APEXposed conference in Atlanta this week, a customer asked me to blog about how to abbreviate and pronounce the abbreviation for Oracle Application Express. I've seen numerous abbreviations (Apex, ApEx, APPEx, AppEx, APEX), and I think the lack of a formal abbreviation lends to the confusion in pronunciation. So here goes:

1. Whenever an abbreviation is used in informal writing, it's abbreviated in all uppercase: APEX
   
   
2. It's pronounced (ā'pěks). It's a long 'A' as in 'acorn'.
   
   
3. In formal written materials from Oracle, it is always written as 'Oracle Application Express' and never abbreviated.

In conversation and in some presentation materials, you will see the Oracle Application Express team use the above spelling and pronunciation.

I have dedicated a couple posts to describe who is using apex.oracle.com. I have focused on the scalability of the Application Express environment on relatively cheap hardware. And towards the end of September, we moved asktom.oracle.com to apex.oracle.com. So why not combine the best of these topics and post about who is using asktom.oracle.com?

AskTom officially moved to apex.oracle.com on 18-SEP-2009. I turned on Google Analytics a day or two later. So my snapshot of asktom.oracle.com covers the period from September 20, 2009 through November 2, 2009 - not quite a month and a half.

Some interesting statistics:

1. There were 517,599 "visits" and 1,005,189 page views.
2. 78% of the page views were from hits from search engine results pages (thanks for the clarification, John Scott).
3. 53% of the browsers are Internet Explorer - a number that I suspect gradually declines over time
4. People found the site searching for 'ORA-00604' more than they found it by searching for 'tom kyte'. As a shareholder of Oracle, this statistic concerns me.
5. The lion's share of visits comes from the United States, followed by India, United Kingdom, Germany and Canada.

All in all, though, I'm quite happy with these numbers, and how it shows that the existing hardware on apex.oracle.com and this large database (with over 10,000 workspaces/users/tablespaces) was able to absorb the additional load with no problem.


asktom.oracle.com Google Analytics - 20091102

Oracle is in the process of preparing a certification exam for Oracle Application Express. Passing this exam will qualify someone as an "Oracle Application Express Developer Certified Expert." This certification is intended for intermediate to advanced APEX Developers who have at least 6 months experience using Oracle Application Express.

Participants for the very first pilot exam are being recruited now. Keep in mind - the feedback and recommendations from the participants of this exam will help shape this course, so some feedback will be expected. More information can be found at:

http://www.oracle.com/goto/apexpilot

Are you going to Oracle OpenWorld 2009? If so, I encourage you to look us up. The primary Application Express location will be in the DemoGrounds in Moscone West, stations W1, W2 and W3. As you walk into the DemoGrounds, this will be in the front on the left side. David has a map here.

A large majority of the Application Express product development team will be there, including Mike, Christina, David, Marc, Scott, Jason, Patrick, Hilary and Anthony. This is always a very rewarding event for us, as we get to learn directly from our users how they're using Application Express and what they want in the future. We'll also give you a demonstration of what is coming in the future.

Please stop by - we would love to meet you.

As John Allen pointed out in the APEX discussion forum on OTN, when using the recently bundled and licensed AnyMap files with Application Express 3.2.1, a license message of "Your license does not include the AnyMap extension" was displayed on the rendered map.

This problem is now corrected in Application Express 3.2.1. The full download from OTN has been updated with this new file as well as the Application Express 3.2.1 patch set on Oracle MetaLink (Patch Number 8548651). The file that has been updated is /images/flashchart/swf/AnyChart.swf, and this would need to be replaced in your current images directory for Application Express 3.2.1. This has also been corrected on http://apex.oracle.com.

If you have already installed/upgraded to Application Express 3.2.1 and you want to get this new file:

1. Download the Application Express 3.2.1 patch set from MetaLink and apply it to your Application Express 3.2.1 instance. If you're running the XDB HTTP Protocol Server/embedded PL/SQL Gateway, all you'll really need to do is run the SQL script apxldimg.sql to restage all of the files in the /images/ directory in XDB. If you're using Oracle HTTP Server, then you can just simply copy the updated /images/flaschart/swf/AnyChart.swf file to your existing /images/ directory.
   
   
2. If you don't have access to MetaLink, then you'll need to download the full Application Express 3.2.1 distribution and put this new AnyChart.swf file in place - either in XDB or in your existing /images/ directory.

Unfortunately, Oracle Database 11gR2 11.2.0.1 will continue to ship with this previous AnyChart.swf file, and if you wish to use the maps or Gantt charts, you'll need to download the Application Express 3.2.1 patch set from Oracle MetaLink and apply it.

There is no declarative interface in Application Express 3.2.1 to create maps or Gantt charts. You'll need to understand their respective APIs to be able to use these components in your Application Express application. But the maps and Gantt charts are included in the license for Application Express 3.2.1. The declarative interface to maps and Gantt charts is coming in Application Express 4.0, and you'll be able to see this in action at Oracle Open World 2009.

In February 2008, I posted "Who uses apex.oracle.com?". I thought it was time for a quick update.

I have left the same Google Analytics code in place. And keep in mind - this has only been tracking users who hit the login page on apex.oracle.com and nowhere else - not within Application Express after logging in, not those users who go directly to Carl Backstrom's or Denes Kubicek's or Mark Lancaster's sample applications, nor those users who go directly to ProMED Mail. It only tracks those who go to the login page.


apex.oracle.com Google Analytics - 20090918


So what can we conclude?

1. United States still holds the top spot - not surprisingly since we tend to talk about APEX a little more (as a lot of us live and work in the U.S.)
2. India has surpassed the United Kingdom
3. Colombia shot up from 22 to 9.
4. Romania moved from 8th to 4th - a strong showing.
   
5. There were previously 122 distinct countries that touched apex.oracle.com. Now it's up to 152 distinct countries.
6. Some dude in New Caledonia checked out Application Express and never came back.
   

In hindsight, I should have left this Analytics JavaScript code in place in the System Message itself, so I could track who actually logs in and uses Application Express - I'll add it back now. But this explains why you see a difference with statistics from February 2008 with the Pages/Visit and Average Time on Site - I've only been tracking the login page.

I won't pretend to be an expert in DICOM - Digital Imaging and Communications in Medicine. But I do know that the Oracle Multimedia team are experts in this field, and they recently released a new packaged application which demonstrates the use of DICOM Medical Image Support in the Oracle Database 11g, all from within an Application Express application. This just further illustrates my point that Oracle Application Express, instead of insulating you from the database via multiple layers and stacks and tiers, enables you to readily exploit the native functionality of the Oracle Database, like Oracle Multimedia and the DICOM features.

From Melli, the Oracle Multimedia Product Manager:

The DICOM sample application shows how the database can be used to store and manage DICOM medical images and illustrates how quickly Oracle technology (DICOM functionality and Application Express) can be used to build an archive to manage medical images. With this archive application, clinicians can easily browse the images, share images with other clinicians, and search images.

The DICOM sample application is demonstrated in a browser. Because a DICOM viewer is not available, full-size JPEG images and JPEG thumbnail images are created using Oracle Multimedia DICOM features. These images are stored in a main archive table, along with the original DICOM images. Additionally, all the meta data from the DICOM images is extracted from the DICOM images to enable searching based on DICOM meta data.


The DICOM Image Archive Demonstration application can be downloaded from the Oracle Application Express Packaged Applications. You can also preview the DICOM Image Archive Demonstration as well as the DICOM Image Archive Administration applications, all running on (you guessed it) apex.oracle.com.

For those who are familiar with Tom Kyte and his site Ask Tom, this is now running on apex.oracle.com. Don't believe me? Then compare http://asktom.oracle.com and http://apex.oracle.com/pls/otn/f?p=100:1.

This turned out to be a fairly painless exercise. I actually moved the application about 4 months ago, created the workspace, created the ASKTOM database user, did an initial database import of his database objects and data, imported his application, changed all of the references to images and CSS's in a custom /i/asktom_i directory to be Shared Components of the application, and then worked with Global IT to setup the proper definition of this new Virtual Host and other Web Server configuration. Then it was just a simple matter of timing, to coordinate the DNS switch (which Global IT handled) and the Data Pump export and import (which Tom handled - I tried to think of someone more qualified and could not).

The switch from the old server to the new application on apex.oracle.com happened yesterday at 16:48P EDT (GMT - 04:00). Since then, roughly 16 hours ago, we've had 24,701 page views on the AskTom application. And Tom went so far as to describe the performance as "snappy".

I warned Tom that there is an ongoing issue with some users and the network performance to apex.oracle.com, and that the new AskTom would be inheriting this issue, but so far, no issues have been reported. And for the person who will undoubtedly call me out and say "apex.oracle.com is not intended for production applications", that is a correct statement - if you're not ProMED or AskTom and you don't work in my group, consider it only your playground and demonstration instance.

Today, Anthony Rayner sent me a message, describing the issue he had installing a recent build of Application Express 4.0 on his database. He ran into all sorts of compilation errors. As it turns out, Anthony was using Oracle Database version 10.2.0.1. And that won't work with Application Express 4.0.

Starting with Oracle Application Express 4.0 (most likely due out some time in 2010), the minimum database version will be 10.2.0.3. Database version 10.2.0.2 or less will not work. Application Express 4.0 cannot be installed and function and be supported in the earlier database versions - hacking the installation scripts won't work this time.

I say this with one caveat, though. Application Express 4.0 will still work with XE. Even though the database version of XE is 10.2.0.1, it's not really the same software as 10.2.0.1 of the released database software (i.e., Standard Edition). XE is really 10.2.0.1+.

A few weeks ago, I blogged about the use of the Oracle Database Resource Manager and apex.oracle.com, and how it is essential to keep this service up and running.

Well, today, I stumbled across 10 active database sessions, all from the same user, all running an anonymous PL/SQL block issued within SQL Commands on apex.oracle.com. All 10 sessions had been running for hundreds of seconds and all 10 were in the APEX_LOW resource consumer group. By using the built-in-to-APEX Utilities -> Database Monitor -> Sessions reports, I was able to determine exactly what was executing in these sessions.

What's wrong with this user's code?


DECLARE
i NUMBER(3):=1;
BEGIN
WHILE (i<5) LOOP
IF( mod(i,2)=0) THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;


I am NOT suggesting you run this on apex.oracle.com nor your own instance of Application Express.

This past weekend, the forthcoming Application Express 3.2.1 patch set was applied to apex.oracle.com (and apex.oraclecorp.com, if you're an Oracle employee and user of the internal instance). The APEX 3.2.1 Patch Set Note lists the bugs fixed in this patch set.

In addition to the bugs fixed in this patch set, a few interesting points:

1. The online help (the popup window of the brows-able documentation) is available in Japanese.
2. AnyChart AnyGantt Flash Gantt and AnyChart AnyMap files are included in this patch set. Even though these aren't directly integrated into the Application Express declarative environment, they are included in the license of Application Express and available for your use within an Application Express application.
3. An updated version of FCKEditor is included, fixing a number of bugs. However, one user has already reported a change in behavior, with the default behavior of the Enter key resulting in a '<p>' versus the previous '<br />'.
4. This version of Application Express, 3.2.1.00.10, is the identical version that will be bundled with Oracle Database 11gR2.

For those customers running Oracle Database 11gR1 11.1.0.7, you'll need to pay special attention to the note about applying the APEX 3.2.1 patch set.

As with all past Application Express patch sets, the Application Express 3.2.1 patch set will be available for download on Oracle MetaLink. The full distribution of APEX 3.2.1 will be available for download on OTN. These should be available within one day.

Im November 2009 soll das neue Buch "Oracle APEX und XE in der Praxis" von Dietmar Aust, Denes Kubicek und Jens-Christian Pokolm herauskommen. Ich kenne Dietmar schon seit den Anfangstagen der Oracle XE Datenbank, wo er innerhalb kurzer Zeit ein Experte für diese wurde. Und Denes Kubicek ist ein bekannter Experte für Oracle Application Express, welcher letztes Jahr mit dem "Oracle APEX Developer of the Year" ausgezeichnet wurde.
Ich freue mich schon auf das Buch (und natürlich auch auf eine signierte Ausgabe des Buches!).

--------

Coming in November 2009 is a new book from Dietmar Aust, Denes Kubicek and Jens-Christian Pokolm, entitled "Oracle APEX und XE in der Praxis". I have known Dietmar since the early days of Oracle Database XE, where he quickly became a subject matter expert. And Denes Kubicek is a recognized expert of Oracle Application Express and was last year's Oracle APEX Developer of the Year.

I look forward to this book (and getting a signed copy of this book!).

Today, Packt Publishing announced their forthcoming book, "Oracle Application Express 3.2 – The Essentials and More", authored by my good friend Arie Geller and Matthew Lyon. The expected availability is January 2010.

Congratulations, Arie. The end is in sight!

Roger Cohen from APEXtras was kind enough to introduce me to two-factor authentication using YubiKey and Application Express.

Now - I'll be honest, I had never heard of YubiKey before, but I am familiar with two-factor authentication. I am a satisfied user of KeePass, and I am able to maintain both a password and a separate key file (in my case, on a Flash Drive) for access to my encrypted KeePass passwords database. I need both my password and the keyfile to access the passwords database. I lose one or both and I can't get in.

Roger gives a very good description of YubiKey here, what it's good for and why you would want to consider it. But more importantly, Roger has a working demonstration of APEX and YubiKey authentication live on apex.oracle.com. Granted, you'll need a YubiKey for this demonstration to work. Lastly, the folks from APEXtras were kind enough to post an explanation of the logic and all of the source code for the custom authentication for this solution.

At ODTUG Kaleidoscope this year, I gave a presentation on using the Oracle Database Resource Manager with Oracle Application Express. The Oracle Database Resource Manager enables an administrator to control the allocation of hardware resources within an Oracle database - something that an OS-based process scheduler cannot accomplish. The actual PowerPoint presentation is here.

I often talk about the scalability of Oracle Application Express on apex.oracle.com, which runs on fairly modest hardware. However, I fully believe that this database and server would eventually crumble if it were not for our use of the Oracle Database Resource Manager. Unlike a database instance which is running a handful of tuned applications, apex.oracle.com is a free-for-all. As long as you have a workspace, you can run whatever SQL you like from SQL Commands (I'm not recommending this), or, you could inadvertently write some pretty inefficient SQL or PL/SQL in your application. Without any controls in place, a user could easily and quickly monopolize the CPU resources on apex.oracle.com. And for a site that gets between 3.5 million and 6 million page views per week, page view requests could quickly back up and overwhelm the database server.

So how do we manage this chaos? Via the Oracle Database Resource Manager. Rather than explain the syntax of the Resource Manager, I think it's much simpler to convey the plan that is in place right now on apex.oracle.com and then comment on it.


-- apex.oracle.com resource plan

-- Section 1
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();

dbms_resource_manager.create_consumer_group(
consumer_group => 'APEX_HIGH',
comment => 'All APEX sessions start in this group.');
dbms_resource_manager.create_consumer_group(
consumer_group => 'APEX_MEDIUM',
comment => 'APEX sessions are switched to this group after 10 seconds.');
dbms_resource_manager.create_consumer_group(
consumer_group => 'APEX_LOW',
comment => 'Any sessions in this group have been executing for more than 120 seconds');

dbms_resource_manager.submit_pending_area();

end;
/


-- Section 2
begin
dbms_resource_manager_privs.grant_switch_consumer_group (
grantee_name => 'APEX_PUBLIC_USER',
consumer_group => 'APEX_HIGH',
grant_option => FALSE );
dbms_resource_manager_privs.grant_switch_consumer_group (
grantee_name => 'APEX_PUBLIC_USER',
consumer_group => 'APEX_MEDIUM',
grant_option => FALSE );
dbms_resource_manager_privs.grant_switch_consumer_group (
grantee_name => 'APEX_PUBLIC_USER',
consumer_group => 'APEX_LOW',
grant_option => FALSE );
end;
/

begin
-- Section 3
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'APEX_ORACLE_COM_PLAN', comment => 'APEX Plan');



-- Plan Directives Section
dbms_resource_manager.create_plan_directive(
plan => 'APEX_ORACLE_COM_PLAN',
group_or_subplan => 'APEX_HIGH',
comment => 'All APEX sessions start in this group.',
mgmt_p1 => 70,
switch_group => 'APEX_MEDIUM',
switch_time => 10,
switch_for_call => TRUE,
switch_estimate => FALSE );

dbms_resource_manager.create_plan_directive(
plan => 'APEX_ORACLE_COM_PLAN',
group_or_subplan => 'APEX_MEDIUM',
comment => 'APEX sessions are switched to this group after 10 seconds.',
mgmt_p1 => 8,
switch_group => 'APEX_LOW',
switch_time => 120,
switch_for_call => TRUE,
switch_estimate => FALSE );



dbms_resource_manager.create_plan_directive(
plan=> 'APEX_ORACLE_COM_PLAN',
group_or_subplan => 'APEX_LOW',
comment => 'Any sessions in this group have been executing for more than 120 seconds',
mgmt_p1 => 2,
switch_group => 'CANCEL_SQL',
switch_time => 1800,
switch_for_call => TRUE,
switch_estimate => FALSE );


-- Section 4
dbms_resource_manager.create_plan_directive(
plan=> 'APEX_ORACLE_COM_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => 'The mandatory group',
mgmt_p1 =>10);

dbms_resource_manager.create_plan_directive(
plan=> 'APEX_ORACLE_COM_PLAN',
group_or_subplan => 'ORA$AUTOTASK_SUB_PLAN',
comment => 'Sub plan for maintenance activity',
mgmt_p1 => 10 );

-- Section 5
dbms_resource_manager.set_initial_consumer_group(
user => 'APEX_PUBLIC_USER',
consumer_group => 'APEX_HIGH');

dbms_resource_manager.submit_pending_area();

end;
/



-- Section 6
begin
dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
dbms_scheduler.set_attribute( name => 'TUESDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
dbms_scheduler.set_attribute( name => 'WEDNESDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
dbms_scheduler.set_attribute( name => 'THURSDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
dbms_scheduler.set_attribute( name => 'FRIDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
dbms_scheduler.set_attribute( name => 'SATURDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
dbms_scheduler.set_attribute( name => 'SUNDAY_WINDOW',
attribute => 'RESOURCE_PLAN', value => 'APEX_ORACLE_COM_PLAN');
end;
/


-- Section 7
alter system set resource_manager_plan = 'APEX_ORACLE_COM_PLAN' scope=both;

• Section 1 - Create three consumer groups with the names APEX_HIGH, APEX_MEDIUM and APEX_LOW. These group names are arbitrary. A consumer group is simply a collection of users.


• Section 2 - Remember that the Oracle HTTP Server and mod_plsql connect to the database as user APEX_PUBLIC_USER (if you're using the embedded PL/SQL Gateway, this is user ANONYMOUS). From a database perspective, all it sees are a bunch of database sessions, connecting as database user APEX_PUBLIC_USER. So in Section 2, we are granting the ability to switch among these consumer groups to database user APEX_PUBLIC_USER.


• Section 3 - Create the plan directives which controls how much of the machine resources is dedicated to a particular consumer group, and how long they are allowed to remain in this group before switching to another group. So if a session is in the APEX_HIGH group, the APEX_HIGH group is entitled to 70% of the CPU resources. If the top-level SQL call is executing for more than 10 "on-CPU" seconds, then they get switched to the APEX_MEDIUM group. The APEX_MEDIUM group gets only 8% of the available CPU resources, for up to 120 seconds. After 120 seconds, the session gets switched to the APEX_LOW group. The APEX_LOW group has only 2% of the CPU resources available. After 1800 seconds (which is a *long* time), if the top-level SQL call is still executing, then it will be canceled. The session will not be killed, but the top-level SQL call will be canceled.


• Section 4 - Directives are created for two other pre-defined and built-in groups, namely, OTHER_GROUPS and ORA$AUTOTASK_SUB_PLAN. The ORA$AUTOTASK_SUB_PLAN is used for all of the automated maintenance tasks of the database (e.g., statistics collection). OTHER_GROUPS is used for any other session not already a part of a group in the active Resource Manager plan. This would be the group for anyone connecting directly to the database, background database jobs not running as APEX_PUBLIC_USER, etc.


• Section 5 - Make APEX_HIGH, our newly created consumer group, the default consumer group for user APEX_PUBLIC_USER. Then, submit the pending area for validation.


• Section 6 - The Oracle database (at least for 11gR1 and 11gR2) ships with a predefined scheduler plan for maintenance tasks. Modify the resource plan in effect during these maintenance windows. If we don't, then the DEFAULT_MAINTENANCE_PLAN would be in effect and not our newly created Resource Manager Plan APEX_ORACLE_COM_PLAN. And what that means is we'd be back to a free-for-all during this maintenance window, with anyone being able to monopolize the server.


• Section 7 - Change the settings of the database to use our newly created plan.

There are tools available in Enterprise Manager to create and monitor resource plans, so you don't have to commit the syntax of all of these PL/SQL packages to memory. Also, there are a large number of database views that can be used to monitor the Resource Manager, including:

• V$RSRC_PLAN – Currently active resource plan


• V$RSRC_CONSUMER_GROUP – Cumulative amount of CPU stats


• V$RSRC_PLAN_HISTORY – History of resource plan, when enabled, disabled or modified


• V$RSRC_CONS_GROUP_HISTORY – History of consumer group statistics


• V$RSRCMGRMETRIC – Information about resources consumed and wait times per consumer group


• V$RSRCMGRMETRIC_HISTORY – History of Resource Manager metrics

I granted privileges on these SYS-owned views to another database user that was mapped to an APEX workspace (actually, my workspace on apex.oracle.com). Then, I was able to easily build an APEX application using Interactive Reports on top of these views and monitor how well we were doing.

The Oracle Database Resource Manager is only available as a part of the Oracle Database Enterprise Edition. It is not available with XE, Standard Edition One, or Standard Edition of the Oracle Database. But for those customers that are already using Oracle Application Express on the Oracle Database Enterprise Edition, and they're attempting to consolidate a number of groups and applications onto a single instance, the Oracle Database Resource Manager can be easily used to prevent any one user or application from monopolizing the server.