A few notes about the Application Express 4.0 Early Adopter

1. The database character set is WE8MSWIN1252. When we create the Early Adopter 2 instance of APEX 4.0 (hopefully in January, 2010), the database character set will be AL32UTF8. Unfortunately, today you may experience some character conversion errors if you attempt to use characters outside of the character set (e.g., Chinese, Japanese, Hebrew, Arabic, etc.).
2. We will notify people in advance, at least a week, when we will install APEX 4.0 Early Adopter 2.
3. When APEX 4.0 Early Adopter 2 is installed, it is our intention to preserve the workspace definitions, your workspace credentials, and also your schema (objects and data). However, any APEX applications will be purged. You may be able to export the applications beforehand and import them into APEX 4.0 EA2, but that's not guaranteed.
4. APEX 4.0 Early Adopter 2 will add Websheets in addition to better support for Internet Explorer, among other things.
   

Moving table data from SQL Developer into Application Express using XML

Gary, an Application Express customer for many years, recently sent me a message with a problem he was having getting table data, exported from Oracle SQL Developer in XML format, into Oracle Application Express. He pointed me to his post on the Oracle Application Express discussion forum on OTN.

Using the EMP table as an example, he showed me that the data he exported from SQL Developer looked something like:

He originally thought that Application Express was choking on the CDATA values. It isn't that Application Express didn't like the CDATA elements, it's just that they are two completely different XML formats. Underneath the hood, Application Express uses DBMS_XMLSAVE.INSERTXML for the XML loading, and it uses the default ROWSET and ROW tags. SQL Developer, for whatever reason, uses a different format. Maybe there's a way to control this in SQL Developer, but I'm not aware of it.

So how do we transform the XML from SQL Developer into something compatible with the standard ROWSET format used by Application Express? Well...this is exactly what XSLT was designed for. I'm no XSLT expert, but I learned enough from Carl's examples to accomplish this type of simple transformation. You can use any XSLT engine to do this type of transformation, but I chose to use Microsoft's command-line utility, msxsl, to do this.

1) Before I did anything else, I had to re-generate the XML from SQL Developer so the DATA type data values would be in the canonical date format used by Application Express XML data loading. To do this, from the SQL Worksheet in SQL Developer, I issued the statement:

alter session set nls_date_format = 'rrrr-MM-dd"T"HH24:mi:ss."000"'

And then exported the data from SQL Developer to XML again, which now gave me:

2) mxsxl did not like the encoding the SQL Developer included in my XML file (it will give an error like "System does not support the specified encoding.") So I edited the XML file from SQL Developer and changed:

encoding='Cp1252'

to

encoding='utf-8'

In my case, this didn't present a problem, as I have all ASCII characters in my XML file.


3) Then, I created a text file named 'emp.xslt' with the following contents:

4) Finally, using the command-line in Windows, I simply issued:


msxsl emp.xml emp.xslt



And voilà! I was able to take:

And turn it into:

7839
KING
PRESIDENT

1981-11-17T00:00:00.000
5000

10


7934
MILLER
CLERK
7782
1982-01-23T00:00:00.000
1300

10

ready for loading into Application Express, and all with just a little bit of XSLT.

I haven't tested what is output from SQL Developer for CLOBs and BLOBs, so I can't speak for this solution for those types. But for the most common data types, this simple transformation can work quite easily.

(ā'pěks)

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.

----

Update on March 29, 2020:  In formal, written materials, you can also use Oracle APEX.

Who uses asktom.oracle.com?

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

Certification Exam for Oracle Application Express

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

Going to Oracle OpenWorld 2009?

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.

AnyChart AnyMap updated in Application Express 3.2.1

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.

Who uses apex.oracle.com (an update)

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.

Oracle Multimedia DICOM demonstration application available

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.

Ask Tom moved to 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.

Minimum database version for Application Express 4.0

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+.

Why I use the Resource Manager on apex.oracle.com

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.

Application Express 3.2.1 patch set applied to apex.oracle.com

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.

Neues Application Express Buch - auf Deutsch!

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!).

Forthcoming book about Application Express

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!

Application Express & YubiKey

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.

Oracle Database Resource Manager and Oracle Application Express

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.

Who says Application Express can't scale?

Over a year ago, I wrote about who is using Oracle Application Express on http://apex.oracle.com. At the time, I included some weekly statistics.

During our team meetings every week, I report on the overall usage of http://apex.oracle.com. I just wanted to report on some astounding numbers:

Total Page Views Distinct Applications Distinct Users
---------------- --------------------- --------------
       6225566                  3268           2778


Total Workspaces
----------------
         11643


Total Applications
------------------
           39849


Workspaces Approved
-------------------
              316

That's 6,225,566 page views in the past week, and this is still running on the same Dell PowerEdge 1950.

Now granted - the ability to sustain this type of load throughout the week deserves some credit to Kris Rice, who spent a fair amount of time analyzing Application Express on apex.oracle.com and identifying some major problem areas in performance. As well, he brought in some experts to perform OS tuning. The recent purge on apex.oracle.com removed close to 10,000 workspaces, schemas, tablespaces and datafiles. Lastly, a couple months ago, I implemented a Resource Manager plan which prevents someone from monopolizing the entire server. I'll be presenting a detailed discussion of Resource Manager and apex.oracle.com at ODTUG Kaleidoscope 2009.

A large number of these page views are from the bots of Search Engines, especially since ProMED Mail is run on apex.oracle.com. But a page view and execution of the engine is a page view and execution of the engine. All in all, that's a large load on a relatively cheap piece of hardware.

OraTweet - Micro-blogging with Oracle Application Express

About a year ago, Carl Backstrom was telling me how he was helping another gentleman in Oracle, Noel Portugal from the CRM On Demand team, with a micro-blogging site developed with Oracle Application Express. Carl game me a demonstration of OraTweet, tried to explain Twitter, tweets, and micro-blogging. I just didn't get it, at the time.

Today, micro-blogging appears to be all the rage. Dwight Howard from the Orlando Magic basketball team is on Twitter. CNBC's Fast Money television show is on Twitter. I am even on Twitter, but I really don't use it, as you can tell.

Noel's application, OraTweet, has been in use within Oracle for the past year. As Noel states on the oratweet.com site: "Teams around Oracle are finding ways to communicate with their teams while reducing email overload. The results have been amazing. Global team members are connecting and sharing information. Users have started to develop stand alone clients to consume OraTweet’s timeline. We have also integrated the OraTweet timeline to Oracle Connect, our own internal social network. Overall information is flowing faster in a lightweight format."

Noel Portugal has developed an excellent Web site which explains OraTweet, lets you submit feedback, and provides a link to download the entire source code for OraTweet: http://oratweet.com/

See you in Monterey?

Time is running out for the Oracle Development Tools User Group Kaleidoscope Conference in Monterey, California on 21-June-2009 through 25-June-2009. The lower cost advanced registration ends next Tuesday, 02-June-2009. The folks from ODTUG were kind enough to provide a template to use for a justification letter for your employer.

It all begins with the Application Express Symposium on Sunday, of which over 160 people are registered. Other than the opening remarks by some joker from Oracle, all other presentations at the Symposium are from customers and the community. And then there is a full agenda for the remainder of the week if you're interested in Oracle Application Express.

This is an outstanding, well-organized and well-run conference, giving customers the opportunity to interact with some of the greatest Application Express experts from around the world. This is a conference where you can listen, learn and discuss the solutions created by successful Oracle customers. Oracle is invited to participate, but this conference is run by ODTUG.

Disclaimer: I am not associated with this user group nor is there any financial incentive to me for the number of people in attendance.

Oracle Single Sign-On and apex.oracle.com

Application Express on apex.oracle.com is now registered as a Partner Application for Oracle Single Sign-On. This means that you can now create demonstration and sample applications, hosted on apex.oracle.com, and have the users authenticate via the Oracle Single Sign-On Login Server.

Who is registered on Oracle.com? Well, if you have ever asked or answered a question on the wildly popular Oracle Application Express discussion forum on OTN, then you already have an account.

To enable SSO authentication to Oracle.com in your application on apex.oracle.com, simply follow these steps:

1. Shared Components -> Authentication Schemes
2. Click Create button
3. Choose “Based on a pre-configured scheme from the gallery” and click Next
4. Choose “Oracle Application Server Single Sign-On (Application Express Engine as Partner App)” and click Next
5. Give it a name like SSO and click Create Scheme
6. In the subsequent report of authentication schemes, click the “make current” link for your newly created SSO one
7. Click the Make Current button on the confirmation page
8. Go get a beer (or coffee or tea) to celebrate

It's that simple.

To see this in action, here's a very brief sample application with a public and non-public page, using SSO authentication.

Important note:  As of August 2012, SSO is no longer available on apex.oracle.com.

Cleaning Up

The architecture of Application Express is such that the database objects associated with a specific release all exist in a single schema. For the original Oracle HTML DB 1.5, all of the database objects existed in schema FLOWS_010500. As of the most recent version, Oracle Application Express 3.2, all of the database objects reside in schema APEX_030200.

When a new version of Application Express is installed, the following three actions take place:

1. The new version is installed into a new schema (this happens the same whether it's a new installation or an upgrade installation).
2. The meta data from the previous version is copied and sometimes transformed into the new schema.
3. All of the public synonyms which were pointing at the "old" version are now redirected to the new version of Application Express.

The beauty of this architecture is if an error occurs during installation or during upgrade, it's easy to revert back to the previous installation, as documented here. Both an APEX 3.1 installation (in FLOWS_030100) and an APEX 3.2 installation (in APEX_030200) can exist in the Oracle database at the same time, and with only one version active. Understand, though, if you choose to rollback and remove the newer version of Application Express, all of the changes made in the newer version (instance settings, application changes, new workspaces, etc.) will not be propagated back to the old version of Application Express if you switch schemas.

Once you have successfully upgraded your instance of Application Express, tested all of the upgraded applications, and deem the upgrade successful, there is no reason to maintain the old version of Application Express. Not only does it take up space in your database, it leaves a fairly privileged database account in the database, and this could pose a security risk if compromised.

For apex.oracle.com, which currently has over 20,000 workspaces, I always install the new version of Application Express into it's own tablespace. I do not install Application Express into the default SYSAUX tablespace. Application Express 3.1 was installed into tablespace APEX_REL31. When I recently upgraded to Application Express 3.2, and after we ran successfully for a couple days, it was easy to clean up the old Application Express 3.1 by issuing:

DROP USER FLOWS_030100 CASCADE;
DROP TABLESPACE APEX_REL31 INCLUDING CONTENTS AND DATAFILES;

That's it! The privileged database user from the previous version is gone. All of the space on disk is reclaimed. And the newer version of Application Express 3.2 in APEX_030200 hums along just fine.


Note: There is one schema installed with Application Express, FLOWS_FILES, which persists across version upgrades. The sole purpose of this schema is to maintain uploaded files. You should never manually remove this schema unless you want to remove every facet of Oracle Application Express from your database. And if you really wanted to remove every trace of Application Express, I'd recommend using the apxremov.sql script anyway.



Considerations when dropping on Database 11g

If you're using Application Express with Oracle Database 11g (11.1.0.6 or greater), undoubtedly you had to enable Network Services so Application Express could send e-mail, use Web Services, etc. If you didn't do this, then you'll encounter errors like "ORA-24247: network access denied by access control list (ACL)" when APEX attempts to send e-mail or access any other network resource.

One important caveat to the instructions above - if you created an ACL for FLOWS_030100 (Application Express 3.1) and granted connect privileges to FLOWS_030100, after you upgrade to Application Express 3.2 , grant these same connect privileges on the existing ACL to APEX_030200, and you drop database user FLOWS_030100, the Network ACL will contain a dangling reference to FLOWS_030100 and will be invalid. What this means is that if you're running along just fine with an upgraded Application Express 3.2 and you drop FLOWS_030100, your delivery of e-mail may no longer work. Users will see error messages like "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

This situation happened to me over the weekend. After Application Express 3.2 was running for a month on our internal instance, the old FLOWS_030100 database user was finally dropped. Numerous issues were raised on Monday morning by workspace owners who said they could no longer send e-mail and they were seeing the error message "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

The only way I've discovered to rectify this situation is to manually drop the ACL reference to FLOWS_030100. I connected as SYS in SQL*Plus and ran:

exec dbms_network_acl_admin.delete_privilege('power_users.xml','FLOWS_030100',TRUE,'connect');

This underlying database issue, with the ACL privileges not being dropped when a database user is dropped, was filed as Bug 7493477 and is targeted to be fixed in Database 11gR2.

Oracle Application Express in the cloud

You may have heard a thing or two about cloud computing. Jason Straub, from the Oracle Application Express development team, recently blogged about using Oracle Application Express in the cloud - and how you can run APEX in the cloud for as cheap as 20 cents USD / hour.

http://jastraub.blogspot.com/2009/03/test-drive-oracle-application-express.html

There's a little bit of setup to use the Amazon.com Elastic Compute Cloud, but once that's behind you, you can easily stand up your own Oracle database in the cloud for a tiny amount of money. And, in my opinion, this just further demonstrates how the browser-based Oracle Application Express hosted development and deployment environment is so well suited to cloud computing.

Oracle Application Express 3.2 released

Today, Oracle Application Express 3.2 was made available for download off of the Oracle Technology Network.

If you go to http://apex.oracle.com, you'll find links to:

1. The link to download Oracle Application Express 3.2
2. The list of new features in Oracle Application Express 3.2
3. New Oracle By Examples, including a an Oracle Forms to Oracle APEX OBE and a soon-to-be-released security OBE.

Thanks to the Oracle Application Express community for your continued support, ideas and enthusiasm.

Make all of your APEX applications run a bit faster

See the important update below

Interested in making your APEX applications run faster? I know this seems like an impossible and astonishing feat, and you'll soon be approaching page view execution times of zero, but you can squeeze even a little more throughput and scalability with this one small exercise. And this shouldn't cost you an extra cent.

As a lot of people know already, Oracle Application Express is essentially one big SQL and PL/SQL program. "Porting" of Oracle Application Express to other platforms is not necessary. It installs via SQL*Plus. It runs where PL/SQL does. And PL/SQL, truly, is a write-once-run-everywhere platform.

So how do you make a PL/SQL program run faster? Through native compilation of PL/SQL, of course. When you compile a module in PL/SQL, you are converting it to an intermediate form named system code (or bytecode). At runtime, this system code is interpreted. Execution of this program would be much faster if it were compiled natively and the interpretation step was bypassed altogether. This is analogous to the old days of taking an interpreted BASIC program and compiling it to a native program.

An excellent description of PL/SQL native compilation can be found in Oracle Database PL/SQL Language Reference. When PL/SQL native compilation was introduced in Oracle Database 9iR1 and 9iR2, I found it to be complicated and involved, and I think I was successful getting a small program to ncomp once (and only once). Here is the explanation from some poor guy who figured out all the steps to do this in 9iR2 on Windows. But in Oracle Database 11gR1, this is downright trivial.

My test below was done in an Oracle Database 11gR1 11.1.0.6 on Oracle Enterprise Linux on VMWare Server on a Windows Vista x-64 host. With all those layers of software, the performance difference at runtime could still be easily observed. Also, I did this with the soon-to-be-released Application Express 3.2. Wherever you see APEX_030200, replace it with the database user of your specific APEX release (e.g., APEX 3.1 = FLOWS_030100).

The database view DBA_PLSQL_OBJECT_SETTINGS provides information about the compiler settings for all stored objects in the database. Connect as SYS via SQL*Plus or SQL Developer and run the following query (remembering again to replace 'APEX_030200' if you're not running Application Express 3.2):

column plsql_optimize_level format 999
column plsql_code_type format a20
select count(*), o.object_type, s.plsql_optimize_level, s.plsql_code_type
  from dba_objects o, dba_plsql_object_settings s
 where o.object_name = s.name
   and o.owner = 'APEX_030200'
   and s.owner = o.owner
 group by o.object_type, s.plsql_optimize_level, s.plsql_code_type
 order by 2 asc

On my instance this returned:

COUNT(*) OBJECT_TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
---------- ------------------- -------------------- --------------------
      12 FUNCTION                               2 INTERPRETED
     370 PACKAGE                                2 INTERPRETED
     362 PACKAGE BODY                           2 INTERPRETED
      19 PROCEDURE                              2 INTERPRETED
       1 TABLE                                  2 INTERPRETED
     366 TRIGGER                                2 INTERPRETED
       4 TYPE                                   2 INTERPRETED

7 rows selected.

All PL/SQL objects are interpreted and have a PL/SQL optimization level of 2. You can alter the PL/SQL compiler optimization level via PLSQL_OPTIMIZER_LEVEL, but I encountered runtime errors in Application Express when I natively compiled with an optimizer level of 3. I don't know why, but I'm saving that for another day.

Recompiling all of these objects via native compilation can be done with three easy statements. Note: You should not do this while the APEX applications are actively being used, as these steps will recompile all of the objects in the schema and you could encounter object contention issues. Connect as SYS in SQL*Plus and run:

alter session set plsql_optimize_level = 2;
alter session set plsql_code_type = native;
exec dbms_utility.compile_schema('APEX_030200');

That's it! If you execute the query above, you should now see something like:

COUNT(*) OBJECT_TYPE         PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE
---------- ------------------- -------------------- --------------------
      12 FUNCTION                               2 NATIVE
     370 PACKAGE                                2 NATIVE
     362 PACKAGE BODY                           2 NATIVE
      19 PROCEDURE                              2 NATIVE
       1 TABLE                                  2 NATIVE
     366 TRIGGER                                2 NATIVE
       4 TYPE                                   2 NATIVE

7 rows selected.

If you encounter errors and you want to revert back to what you had, run:

alter session set plsql_optimize_level = 2;
alter session set plsql_code_type = interpreted;
exec dbms_utility.compile_schema('APEX_030200');

But I think you'll be pleasantly surprised with the results and have no desire to revert back. In Database 11gR1, this has become a downright trivial exercise. And faster page views means greater throughput which means greater scalability on equivalent hardware. That's both green and economical.

Lastly, you might wonder if the hosted instance of Application Express at http://apex.oracle.com is running with natively compiled PL/SQL. It's not, but after we formally release Application Express 3.2, it will be. There's no reason not to.



Important Update

08-APR-2009: There's nothing like actually using and testing these features on a large-scale system. A few weeks ago, I had natively compiled the APEX engine on apex.oracle.com. But just this past week, we had to switch this back to interpreted. Some unexplained ORA-600 errors were being encountered which is being actively researched by the database development team.

Carl Backstrom - Oracle ACE

Thanks to the work of Sharon on our team, she was able fulfill one of the desires Carl Backstrom had expressed a couple times.

The Oracle ACE program was "designed to recognize and reward members of the Oracle Technology and Applications communities for their contributions to those communities. These individuals are technically proficient (when applicable) and willingly share their knowledge and experiences." This really epitomized Carl - he was always ready and willing to share his vast knowledge with any one.

Well, as luck would have it, the folks at the Oracle Technology Network decided that Oracle employees could no longer be awarded the ACE designation. And that bothered Carl a little bit, as he was a very active contributor to the community every day. It just would have been nice to receive that designation.

Thanks to Sharon's suggestion, this has now been achieved: http://forums.oracle.com/forums/profile.jspa?userID=354238

apex.oracle.com upgraded to Application Express 3.2

Today (Wednesday, 11-FEB-2009) I upgraded apex.oracle.com to Application Express 3.2.0.00.21. As our long time customers know, this is one of the last milestones in our cycle prior to production release.

You can go here for a brief introduction to the new features in Application Express 3.2. As you'll see, there are two primary themes - Forms Conversion and security. The Application Express 3.2 documentation is not staged yet, but the online help/documentation is current for APEX 3.2.

If you encounter odd behavior or you feel that something was not properly upgraded in your application, please feel free to report it on the APEX OTN discussion forum. We will watch this closely.

Thank you for all of your support.