Thursday, December 15, 2011

Oracle Learning Library Advertisement Video

The Oracle Learning Library team just published a new advertisement video, promoting the Oracle Learning Library.  OLL is a great, free source for a lot of technical content on virtually every Oracle product.  Just be sure you turn your volume down before playing this video - the music is quite loud.

You may ask why I'm promoting this on an APEX blog.  Well, it's because:
  1. The Oracle Learning Library application itself is written in Oracle Application Express.
  2. There is a wealth of Oracle Application Express content available in the OLL.
  3. It's yet one more application that runs successfully on  This application alone accounts for over 2M page views/events per week on

Wednesday, December 14, 2011

High CPU Usage and APEX (again)

A couple months ago, I wrote about a customer who was experiencing High CPU Waits presumably from Oracle Application Express.  Well, earlier this week I was at another customer site with exactly the same problem.

This was a case of "the business users" using Oracle Application Express to build an application.  The application was built in short order and well received by the end users.  The application became popular, they expanded the application and grew the user base, and after moving it to a different and faster environment, they hit a wall.  The DBA's performed some analysis on the application, and based upon the Active Session History reports, they concluded that the statements using the most CPU were all from Application Express and of the form:

   rc__ number; 
   simple_list__ owa_util.vc_arr; 
    complex_list__ owa_util.vc_arr; 
owa.init_cgi_env(:n__, :nm__, :v__); 
htp.HTBUF_LEN := 63; 
null;  null; 
simple_list__(1) := 'sys.%'; 
simple_list__(2) := 'dbms\_%'; 
simple_list__(3) := 'utl\_%'; 
simple_list__(4) := 'owa\_%'; 
simple_list__(5) := 'owa.%'; 
simple_list__(6) := 'htp.%'; 
simple_list__(7) := 'htf.%'; 
simple_list__(8) := 'wpg_docload.%'; 
simple_list__(9) := 'ctxsys.%'; 
simple_list__(10) := 'mdsys.%'; 
if ((owa_match.match_pattern(p_string => 'f' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then
    rc__ := 2; 
    null; null; f(p=>:p); 
if (wpg_docload.is_file_download) then
   rc__ := 1; 
   null;  null;   null; 
  rc__ := 0;  null;      null;   null;  commit; 
   owa.get_page(:data__, :ndata__); 
end if; 
end if; 
:rc__ := rc__; 

The problem was, as I tried to explain to the DBA's, this PL/SQL block merely reflected the execution of a page view in Application Express. The ASH report will aggregate the "top" sections by top-level call, but the problem really lies within these calls, with what the application developers coded in their application.  For all practical purposes, the overhead of the APEX engine is fixed, and anything else beyond that is what is coded inside of the application by the application developer.  The DBA's asked a very reasonable question, namely "what do we do next?"

We started the process by analyzing the activity log of Oracle Application Express for the particular time window, to identify what was consuming the vast majority of time.  And we were able to produce a nice listing of applications and pages, sorted by average execution time (really no differently than what I posted earlier).  But once we did this, the DBA's wanted to be able to drill down into what was executing on those pages so they could analyze it and tune it.  Given that the vast majority of this customer's primary application used SQL Reports and Interactive Reports, we joined the activity log with the APEX view which contained the SQL statements for the reporting regions on those pages (APEX_APPLICATION_PAGE_REGIONS).  We ended up with the following query:

select sum(elapsed_time), count(*), 
       to_char(avg(elapsed_time),'9999.99') avg_elap, 
       a.application_id||':'||a.page_id app_and_page,
  from apex_workspace_activity_log a
  join apex_application_page_regions b 
    on a.application_id = b.application_id and a.page_id = b.page_id
 where a.view_date between to_date('201112142100','RRRRMMDDHH24MI') 
                       and to_date('201112142105','RRRRMMDDHH24MI')
   and a.page_id is not null
   and b.source_type like '%Report%'
 group by a.application_id||':'||a.page_id, dbms_lob.substr(b.region_source,2000,1)
having count(*) > 10
 order by 3 desc

This query can also be issued directly from SQL*Plus by the DBA's.  If they connect as SYS or as a user who has been granted the database role APEX_ADMINISTRATOR_ROLE, in Application Express 4.1, they will be able to query across all workspaces and APEX applications on the instance.

Understand that there are many places in an application which could be causing performance issues.  There could be slow queries in an authorization scheme that is used in multiple places on a page.  There could be processes or validations or elements on page 0 or a slow page sentry or a host of other things that could be causing degraded performance and which would become apparent when debugging a page.  But to identify the report queries on pages and associate those with the activity log and slowest performing pages, the above query is a great place to start.

Monday, November 21, 2011

Oracle Application Express / Oracle Access Manager Integration White Paper now available

David Peake, the product manager of Oracle Application Express, recently published on the APEX OTN site a white paper describing how to integrate Oracle Application Express 4.1 with Oracle Access Manager 11g. If you go to the Learn More section on the Application Express site on OTN, look for it in the bottom section entitled "Technical Information and White Papers". It's the one with the (obvious) title of Integrating Oracle Application Express with Oracle Access Manager.

Many thanks to Christian Neumueller from the Application Express team and Ramana Turlapati from the Access Manager team for all their efforts in this integration and white paper.

Tuesday, November 08, 2011

Adjusting the Width of an Interactive Report Filter Textbox

I learn something new every day.

Today, Oracle Support sent the following customer question my way:

"We have columns with long data value but the filter (while running a Interactive report) in action menu is of fixed width and hence difficult for us to view the complete data. Is there a possibility for increasing the size of the filter dialog ? (which contains Columns, Operator and Expressions)."

When I asked for clarification, I received the following picture:

I'll never claim to know it all. But I will claim to know the people who collectively do know it all. Shakeeb Rahman from the Application Express development team replied with a simple solution. As Shakeeb stated:

"We can target the expression field and its drop down menu using the IDs that are assigned to these items. Here is the style that you would put into your page header to give these two items a fixed width:"

#apexir_EXPR, #apexir_col_values_drop { width: 300px; }

And that's all there is to it. If you want to adjust the width of the expression field and the associated drop down menu, simply add this inline style to the page header attribute on the page where your Interactive Report is defined.

Wednesday, October 26, 2011

High CPU Waits and SQL from Oracle APEX

I was recently at a customer site helping one of the DBAs diagnose a performance issue on their rather large Oracle Application Express instance (over 800 production applications on the one instance). This gentleman self-admittedly didn't know all that much about APEX but he was adept at producing and analyzing ASH (Active Session History) and AWR (Active Workload Repository) reports.

He showed me the extraordinarily high CPU times between 0900 and 1100 his time, and he traced it to the following PL/SQL block that "comes from APEX":

rc__ number;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
owa.init_cgi_env(:n__, :nm__, :v__);
htp.HTBUF_LEN := 63;
null; null;
simple_list__(1) := 'sys.%';
simple_list__(2) := 'dbms\_%';
simple_list__(3) := 'utl\_%';
simple_list__(4) := 'owa\_%';
simple_list__(5) := 'owa.%';
simple_list__(6) := 'htp.%';
simple_list__(7) := 'htf.%';
simple_list__(8) := 'wpg_docload.%';
simple_list__(9) := 'ctxsys.%';
simple_list__(10) := 'mdsys.%';
if ((owa_match.match_pattern(p_string => 'f' /* */, p_simple_pattern => simple_list__ , p_complex_pattern => complex_list__ , p_use_special_chars => false))) then
rc__ := 2;
null; null; f(p=>:p);
if (wpg_docload.is_file_download) then
rc__ := 1;
null; null; null;
rc__ := 0; null; null; null; commit;
owa.get_page(:data__, :ndata__);
end if;
end if;
:rc__ := rc__;

I've worked with Oracle Support on issues exactly like this from other customers too, so it seems to be a common problem "with APEX".

As I explained to this customer:

  1. The engine of Oracle Application Express is written in SQL and PL/SQL.
  2. When someone performs a page request from their browser to the Application Express engine, they are ultimately calling a PL/SQL procedure which is the entry point into APEX.
  3. Oracle Application Express is front-ended by a Web server which has some facility to map requests in the URL to execution of the APEX engine - this is usually either Apache & mod_plsql or the APEX Listener or the XDB HTTP Protocol Server & embedded PL/SQL Gateway.
  4. This "agent" on the Web server prepares an anonymous PL/SQL block for each and every request into the APEX engine. The PL/SQL he was showing me was exactly this anonymous PL/SQL block.

To state this succinctly and in more lucid terms, the PL/SQL block he was showing me wasn't anything out of the ordinary. I saw an anonymous PL/SQL block which was ultimately calling the APEX engine (via the code in the block: f(p=>:p); ). And since he showed me that this specific PL/SQL block executed about 800K times in an hour, I said that this directly correlated to the number of page events on his system, e.g., the number of times a page was rendered or posted or report paginated. He needed to drill down into the APEX instance and see what was consuming the vasty majority of execution time within APEX.

While there are nice instance administration reports in APEX to convey this type of information, this DBA didn't have instance administration access - poor guy. So we did some very basic queries against the APEX views to return the necessary information.

One of the most relevant views for him was APEX_WORKSPACE_ACTIVITY_LOG. Connecting as SYS (or in APEX 4.1 or later, connecting as SYS or a database user granted the APEX_ADMINISTRATOR_ROLE database role), we are able to see all information across all applications and workspaces. So we came up with a very easy query to pinpoint the slowest pages on the system during his time window:

select workspace, application_name, application_id, page_id, count(*) total_page_events, avg(elapsed_time) avg_elapsed_time, sum(elapsed_time) elapsed_time
from apex_workspace_activity_log
where view_date between to_date('201110260900','RRRRMMDDHH24MISS') and to_date('201110261100','RRRRMMDDHH24MISS')
group by workspace, application_name, application_id, page_id
order by 6, 7 asc

Thus, using this query, I was then able to tell him which application and page was consuming, on average, the most time on his system. Then, using this information along with other APEX views, we were able to identify the workspace names, applications, pages, report queries and eventually the administrators of these workspaces and applications so they could begin their own analysis and tuning exercise.

Sunday, August 28, 2011

Who uses (as of August 28, 2011)?

I was recently browsing a blog post over at, where they showed the breakdown of visitors by country to their site, and found that most developers visiting their site came from the U.S. and Germany and asked the "guys who manage" if they could confirm this. From time to time, I like to post the Google Analytics report for, so people can see where users are coming from.

Note that I only capture the Google Analytics information from the login page and nowhere else.

Here is the map overlay and table by country of visitors to the login page. - Google Analytics - 20110828

My interpretation of this report:

  • This report should not be used to determine the primary geographies of Application Express developers. There are countless developers who happily use APEX yet never visit the login page of
  • The U.S. consistently has more visits than any other country.
  • Since I did this same exercise in 2008, there has been a significant increase from India.
  • Canada has also climbed into the top 5.
  • Poland and Brazil have vaulted into the top 10. Three years ago, Brazil was 23rd, now they are 7th. Impressive.

Friday, August 26, 2011

Where did the online help go in APEX 4.1?

This morning, I responded to an inquiry from Oracle Support (on behalf of a customer) about the lack of "online help" in Application Express 4.1. There is also a current thread on the Oracle Technology Network discussion forum that is asking the question "where did the help go?"

This is not an oversight. This was intentional by design and for the following reasons:

  • This gives our documentation team greater flexibility in correcting documentation and online help issues. Here is a great example of why this is beneficial to us and our customers.
  • Many customers would never use the online help and use Google Search instead.
  • The online documentation is searchable.
  • This dramatically reduces the overall distribution size of APEX 4.1 as well as the size of the content loaded into the XDB repository (if you're using the embedded PL/SQL gateway).
  • This removes the complexity associated with indexing the online help files, especially if your instance is accessed over SSL and/or the database is behind a firewall that cannot get to the Web server.
And what about a customer who cannot get to the Internet, if they are disconnected or at a facility (like a military customer or government intelligence) where access to the Internet is blocked? Well, this customer can download the entire documentation, stage it somewhere, and then adjust the URL that is used when clicking the 'Help' link from within Oracle Application Express. this "improvement on the wrong side" as one customer suggests? I personally don't think so.

Thursday, August 25, 2011

Error in Installation Guide for Oracle Application Express 4.1

The new release of Oracle Application Express 4.1 will be installed into a new schema in your database. The name of this schema is 'APEX_040100'. Unfortunately, the Installation Guide for Oracle Application Express 4.1 references a schema name of 'APEX_041000'. This is incorrect. For any example code (e.g., creation of Network ACL) which references APEX_041000, simply replace it first with APEX_040100.

Hopefully this error is short-lived, as I have reported this error to our documentation team and requested that they correct this in the hosted documentation. If you're reading this blog post a month from now and you don't see any reference to the incorrect APEX_041000, then the problem has been resolved.

Thanks to Dimitri Gielis of APEX Evangelists for finding and reporting this error.

Important Update: As of August 26, 2011, this problem is now resolved. This is the beauty and flexibility of online help and documentation.

Wednesday, August 24, 2011

Oracle Application Express 4.1 released

Oracle Application Express 4.1 was officially released today and is available for download from the Oracle Technology Network.

  • Data Upload - add functionality to your application so your end users can upload CSV data (similar to what you can use in Application Express SQL Workshop)

  • Error Handling - Dramatically improved error handling, where you can define how exceptions are handled in your application instead of being constrained by the APEX engine itself. Patrick discussed this in detail here and here.

  • ROWID - Instead of being constrained by one or two element primary keys, you can simply use ROWID as the unique identifier for Automatic DML forms and tabular forms.

  • Websheets - Websheets have undergone a complete overhaul of the user interface, as well as some interesting new features, like the ability to create reports on SQL queries against DataGrid data.

  • Tabular Forms - Many new features with tabular forms, including tabular forms processes and tabular form validations.

  • Plug-Ins - Support for Authentication and Authorization plug-ins

  • Calendars - Enable editing of data directly from links in a calendar, and also support drag-and-drop to move rows from one day/time to another.

  • Dynamic Actions - Support added for dynamic actions against buttons, and other enhancements.

  • Accessibility - Numerous accessibility improvements have been made in the templates and HTML produced by Application Express.

  • Translations - Complete overhaul of the user interface, previously discussed here.

One obvious question is going to be "where is mobile support?" When jQuery Mobile becomes production software, we will include it in the very next patch set of Oracle Application Express. The infrastructure is there in APEX 4.1 to support it. And Marc Sewtz has promised to blog about how you can drop in the jQuery Mobile Beta into an APEX 4.1 installation. The jQuery Mobile Beta portion can't be supported by Oracle Support, obviously.

Our many thanks to the countless people who participated in the Application Express 4.1 Early Adopter program and provided feedback, suggestions and bug reports. We also owe a special thanks to the many Oracle employees who run their mission critical applications on (an internal instance of Application Express, but for production applications, used by virtually every business unit in the company). They reported many issues and endured some outages while we rectified flaws in the upgrade process and Application Express engine itself. It is thanks to their patience and problem reporting that we were able to avoid embarrassing and reputation-breaking occasions for our customers.

Thursday, August 11, 2011

UK Ministry of Justice, Zippy Zebra and Oracle Application Express

An Oracle partner, Zippy Zebra, has recently published a case study of one of their most recent engagements. The system is for the Ministry of Justice in the United Kingdom. Zippy Zebra was commissioned by Capita and HP on this program, and their efforts were part of a modernization program for a system called OASys, which is the abbreviated term for the Offender Assessment System.

I received a personal demonstration of the Web interface of OASsys (written in Oracle Application Express) from Jonathan Rhind of Zippy Zebra. I was impressed not only with their expert knowledge of Oracle Application Express, but also their competence in Web technologies and user interface design. Most Web applications are "okay"; this system was beautiful.

You can read the full details in the case study on the Zippy Zebra site. Please note that the system is targeted to support a user population of 30,000, all running on Oracle Real Application Clusters (Oracle RAC), with Oracle Database 11g Enterprise Edition.

And who said APEX is only good for Access or Excel replacement? for APEX 4.1 Early Adopter going away

This may be a late blog posting, but for those who haven't noticed, on the login page of there is the following message:

"This instance will be turned off on 12-AUG-2011."

After this Friday, August 12, 2011, we will be extinguishing the database instance and server. It has served its purpose. If you want anything from there, please go grab it now.

Thanks to the 1,411 customers who signed up to kick the tires of Application Express 4.1 Early Adopter. Your time spent is appreciated and your feedback has been invaluable.

Wednesday, August 03, 2011

Need Your Support

Firstly, this post has nothing to do with Oracle nor Oracle Application Express. So you can stop reading now, if you wish. Also, this is a shameless request for financial donations. So, again, you can stop reading now. As our friend Carl Backstrom said so many years ago, "Hey it's my blog I can post what I want ! :)"

In a few weeks, I will be participating in a bike tour with the sole purpose of raising money for cancer research. Last year, I rode with a group of guys for 43 miles. This year, I am doing the full 180 mile (289.6 km) tour over two days. I get nothing out of this endeavor other than tired legs and sore arms. My goals are to to raise awareness of this dreadful disease and help solicit donations which will help fund cancer research.

The tour is called Pelotonia. 100% of every donation will fund essential research at The Ohio State University Comprehensive Cancer Center - James Cancer Hospital and Solove Research Institute. I am simply asking you to help me reach my fundraising goal. Large or small, every donation makes a difference. Even a $5 donation is relevant - every contribution is important. In 2010, over 4,000 riders and volunteers participated to raise $7,846,705 for cancer research.

My ride this year has extra meaning for me. I am dedicating my 180 mile ride in honor of my father, Robert R. Kallman, a prostate cancer survivor, who passed away on July 22, 2011. He handled his disease and old age with both grace and zeal.

If you're interested, my Pelotonia profile is at: Look for the "Donate To My Ride" link in red. And I know that you can be on the opposite side of the planet and still make a donation, as my good friend Arie Geller from Israel has already proven.

I am grateful for your support in this cause, no matter how large or small. Thank you.

Wednesday, July 06, 2011

Oracle APEX on Facebook

Today, an email was sent out to everyone who subscribes to the Oracle Database Insider newsletter. In this email were 3 links:

This announcement isn't going to move mountains, but the important thing to note is that Oracle APEX on Facebook is created and run by Oracle Product Marketing. For all those people who ask "where's the marketing for APEX from Oracle?", here's just the start of it. And with more to come.

Monday, July 04, 2011

You shouldn't use Oracle Application Express because...

I always revel at the myriad of untruths and misinformation given about Oracle Application Express and the reasons why you shouldn't use it. Some of the most common and my favorites are:

  • It can't scale. Most people know this is my favorite to refute.

  • It's only suitable for Access and Excel replacement.

  • With the Sun acquisition, it's going to be all Java all the time. And APEX is not Java. (An Oracle partner told me this, not aware that I work for Oracle).

  • It's written in SQL and PL/SQL. It can't really be all that extensible. It's in SQL, after all. (This gem came to me from Oracle's own Siebel CRM team).

Well, I have a new one to add to the list. At the recent ODTUG Kscope conference in Long Beach, CA, I met with two partners who have been building an elegant application for use by the United Nations (yes, those United Nations). In their presentation, one of the attendees raised the issue of Oracle's commitment to APEX. He raised the point of how most Oracle products are labeled as 11 release whereas APEX has its own release numbers. For him this must be a sure sign of long term non-commitment and preference to ADF.

Let me state a few things:

  1. The difference in APEX version number has zero (as in nil) bearing on any direct or indirect commitment by Oracle for APEX

  2. We have a major revision of Oracle Application Express at least once a year. The database does not release that frequently. It's beneficial to not have the same version number, otherwise, we'd have to sit and cool our heels between 11gR1 and 11gR2, or 11gR2 and Database 12.

  3. The Oracle Database ships with Oracle Application Express, and has done so since Database 10gR1 - all the way through 10gR2, 11gR1, 11gR2 and Database 12.

  4. With respect to the forthcoming Database Express Edition (XE), to reduce the size of the download and size of on-disk distribution, the early decision was to not include Oracle Application Express. But the Senior VP of Server Technologies responsible for all of Oracle Database asked for APEX to be put back in and included with XE.

Oracle Application Express was first started in 1999. Oracle HTML DB and Oracle Application Express have been in constant development since the first supported production release in 2004. The versions and year of release are:

HTML DB 1.52004
HTML DB 1.62005
HTML DB 2.02005
Application Express 2.12006
Application Express 2.22006
Application Express 3.02007
Application Express 3.12008
Application Express 3.22009
Application Express 4.02010
Application Express 4.12011*

I think there's only one thing to safely conclude about this person who doubted Oracle's commitment to APEX solely because of the difference in version numbers....he has no idea what he's talking about.

Saturday, June 11, 2011

Translations in Application Express 4.1 Early Adopter 2

With Application Express 4.1 Early Adopter 2 recently announced, one of the enhancements that needs clarification are those affecting translations. The purpose of this post isn't necessarily to explain how the translation process works in Application Express but, instead, to explain the improvements. These aren't earth-shattering enhancements, but they're intended to improve the process and information for the developer.

It does begin with a revision to the translation home. Here, a developer should be able to quickly see what translations are defined for the current application and if any of them require synchronization. A translated version requires synchronization when the primary application has been changed, but the translated version hasn't been published, to pick up this change. Anyone who has worked with translations in the past has gone through the pain of debugging a translated version of their application, only later to realize that it's out of sync with the primary application.

When it comes to seeding and publishing, these operations can now be performed on the same page and in bulk. If you have multiple languages mapped for your application, you can quickly seed and publish them all from the same page.

And the same type of improvement has been made to the XLIFF file upload and apply translation process. You can now upload up to 10 XLIFF files at a time and also apply these files to different translations in one operation.

Lastly, and most importantly, is the ability to include the application translations in an application export. Prior to Application Express 4.1, if you ever needed to move your application to a different workspace or different application ID, there was no practical way to bring along your translations. You basically hit the end of the road. Now, as an option to application export, you have the choice to include all of your translations in the application export file.

While these enhancements aren't perfect, they are at least an improvement over the previous interface and process. I must give credit to a few people for pushing for these changes.

  • David Bliss from the Oracle Store team has cringed every time he has to perform the translations in Application Express 4.0 for the Oracle Store. The Oracle Store is translated into a multitude of languages and the process he must follow is very cumbersome. He offered to even write the necessary changes in Application Express Application Builder for us if we didn't have time. David provided a very detailed recommendation and extensive feedback on how this should be improved. There is nothing like getting feedback from an end-customer who frequently lives and breathes through this process.

  • Francis Mignault from Insum has been asking for years for a way to move translations from one instance to another, and to different applications. I praise his persistence. He also helped to rationalize the numerous ways to accomplish the same tasks.

  • And lastly, credit must be given to Roel Hartman and Peter Raganitsch - for Peter's presentation at Oracle Gebruikersclub Holland and Roel's blog post about it. When someone says about translations in APEX "In short: it works, but takes a lot of work and maintenance is not so simple. So if you can stay away from multi-lingual applications, you should.", that is not to be perceived as a compliment. And justifiably so.

Friday, June 10, 2011

Application Express 4.1 Early Adopter 2 is now available

As promised earlier this week, Application Express 4.1 Early Adopter 2 is now available at Thanks to the many participants (1,136 total workspaces) who participated in the Early Adopter 1 of Application Express 4.1. The feedback has been extraordinarily valuable.

A few notes about Early Adopter 2:
  • Nothing was migrated or updated from Early Adopter 1. You'll need to sign up for a new workspace again in Early Adopter 2.

  • Some of the issues of using Internet Explorer 7 with Application Express 4.1 have been corrected, but not all of them. You will be best-served to use IE8 or higher (if you're stuck on using Internet Explorer).

  • Click the "What's New" link to see what's new in APEX 4.1, as well as what's new in Early Adopter 2. Some notable additions include Data Loading wizard, Drag & Drop Calendar, and numerous translation improvements (including the ability, finally, to include translations in your application export).

  • And just to state the obvious, this is evaluation software. You can't contact Oracle Support about any issues on They won't have any idea what you're talking about.

Thank you once again for all of your support and many contributions.

Wednesday, June 08, 2011

Advanced Registration for ODTUG KScope11 ends tomorrow

The Oracle Development Tools User Group is holding their annual technical conference (named KScope11) in Long Beach, California in a few weeks. If you're into Oracle Application Express, this is the conference for you. There is a separate track dedicated to Oracle Application Express.

Advanced registration, with the opportunity to save $250 off of the normal conference fee, ends tomorrow, June 9, 2011.

Hope to see you there.

Monday, June 06, 2011

Application Express 4.1 Early Adopter 2 is coming

The Early Adopter of Application Express 4.1 is progressing nicely. There are 998 workspaces on and the feedback has been invaluable.

Early Adopter 2 of Application Express 4.1 is coming. It will introduce a number of features which didn't make the cut for EA1, including Data Loading, Drag & Drop Calendars, and numerous translation improvements.

On or after this Friday, 10-JUN-2011, the current Application Express instance on will be terminated and a new one will be created. Nothing will be migrated. If there is any information you would like to save from your workspace, I encourage you to export it before Friday, 10-JUN-2011.

Thanks once again for the tremendous response and extremely valuable feedback and guidance. It is our customers who ultimately help make Application Express what it is.

Friday, May 06, 2011

Application Express 4.1 Early Adopter 1 available

The Early Adopter 1 version of Oracle Application Express 4.1 is now available at Feel free to sign up for a workspace and give it a whirl. We welcome your feedback (via the Feedback link) at any time.

A few important points about Early Adopter 1 of Oracle Application Express 4.1:

  • If you're using Internet Explorer, you will be best-served to use IE8 or higher. You may run into difficulties with IE7, which we hope to correct in the future.

  • Click the "What's New" link to get an overview of the new features in Application Express 4.1. You'll quickly get the sense that this is not the same "big bang" that you saw between APEX 3.2 and APEX 4.0 - but that's by design.

  • You will see some items listed on the APEX Statement of Direction which are not in Early Adopter 1, most notably Mobile Support and Data Upload. Those didn't make the cut for Early Adopter 1 but we hope to be able to provide these when we refresh the instance.

  • When we do refresh the instance, we will not be migrating any information from the workspaces there. There's also no guarantee that what you export from APEX 4.1 Early Adopter 1 can even be imported into a later Early Adopter instance or APEX 4.1 production.

  • Our friend Patrick Wolf should be blogging soon about the extensive changes made in support of error handling. More explicit control over the error handling in APEX has been a long sought feature. And hopefully this will satisfy those of you who have said the "error handling in APEX sucks" (yes, Bart, that's you).

  • And just to state the obvious, this is evaluation software. You can't contact Oracle Support about any issues on They won't have any idea what you're talking about.

Thank you for all of your support.

Thursday, April 28, 2011

Oracle Application Express in Research Environments

Today, Oracle issued a press release in conjunction with Indiana University, highlighting their use of the Oracle Database and Oracle Application Express in their research environment.

I think it's a perfect example of:
  1. An organization providing access to the rich functionality of an Oracle Database as a service, on their "private cloud".
  2. How Oracle Application Express can be used to provide easy access to this rich database functionality.
  3. How Web and database application development via APEX is approachable by those who don't have "Web Master" on their business card.

It's a natural extension to see how Oracle Application Express could be used in research environments, but on Oracle Exadata. Stay tuned.

Wednesday, April 27, 2011

Application Express 4.0 and Library Cache Latch Contention in Oracle DB

In January of 2011, Oracle Support approached me about a Service Request they received from a customer. This customer had an APEX 4.0.2 application in production with more than 1,000 users. After they upgraded their Oracle database to version, the system ran fine for 2 days and then eventually "locked up", as they put it. They identified the problem as being related to library cache locks, and the offending statement they reported was:

begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;

I reviewed the problem myself, reviewed it with a couple of other folks on the APEX development team, and we were mystified how this could possibly be related to library cache locks.

Fast forward to today, and I was contacted by a completely different customer who was running a benchmark with 100 simulated concurrent users (concurrent as in truly concurrent with no think time). Their conclusion after the benchmark was that there was library cache latch contention due to Application Express, and lo and behold, the statement they identified as being the source of library cache latch contention was:

begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;

This was beyond coincidence that two different customers, in highly concurrent use of an APEX application, identify latch locks and/or latch contention issues with the very same line of the APEX source code.

The PL/SQL block in question was in the activity logging PL/SQL package of APEX. This is the package that writes to the activity log of Application Express, which tracks information like the application, page, user, elapsed time, etc. One of the changes in Application Express 4.0 was a new attribute in the activity log called content length. It was populated via a block like:

execute immediate ('begin wwv_flow_log.g_content_length := sys.htp.getcontentlength; end;');
exception when others then
g_content_length := 0;

I'm sure some will ask why this was done dynamically. Simply stated, because it could not be statically compiled. Most versions of the PL/SQL Web Toolkit will not contain the function getcontentlength() in the package specification. So for those installations that do contain this function, the dynamic execution will succeed. For those that don't (the vast majority), the parse of the PL/SQL block will fail, an exception will be raised, and the exception block will catch it and set a variable to 0. But why would something like this cause library cache latch contention?

I really had no idea. I found no bugs on this issue. was on APEX 4.0.2 on and this was never an issue. So I diagnosed this problem the only remaining way I knew how...I asked Tom.

I knew in advance that Tom was going to rail about the "when others", but I told him I wasn't the author of this code. He also pointed out that doing this dynamically would not result in optimal performance. Sounds good...but that's not the source of the latch contention. In a nutshell, Tom suspected that there is a change in the way the parse happens in 11.2, and a bad parse (i.e., failed parse) is more expensive in 11.2 than earlier database versions. Is it a database bug? Maybe - but it also could simply be changed behavior.

Rather than wait for this problem to be diagnosed as potentially a database bug and maybe some database patch set may contain a fix, I decided to work around this. This particular piece of code, for all practical purposes, has been commented out - no dynamic execution, no failed parse and exception processing, for each and every invocation of the activity logging procedures.

I believe this issue is quite important. For our customers on large, highly concurrent production sites on APEX 4.0, when they decide to upgrade to Database version, they may experience degradation in performance for the same application with the identical load. There is now a patch set exception (a "one-off patch") for this issue for APEX on My Oracle Support. Look for patch 12404581. The actual patch itself is not database version specific. Hopefully this blog post (and patch) will save some of our customers from future headaches.

Thursday, April 14, 2011

Oracle Finance is looking for an experienced Application Express developer in Reston, Virginia, USA

I'm posting this to help my buddy Arie in Oracle Finance locate the right candidate.

Oracle America, Inc. is looking for a competent Oracle Application Express developer, to work out of the Reston, Virginia, USA office. OBIEE experience is also desirable. This person would work directly for the Oracle Finance division.

If you're interested in more details about this position:

  1. Go to
  2. In the field labeled "Search Job Descriptions by Keywords ", enter IRC1453677
  3. Click the "Search" button

Tuesday, March 29, 2011

Oracle Application Express 4.0 with Ext JS

Packt Publishing is certainly getting in the groove when it comes to books on Oracle Application Express. They just released Oracle Application Express 4.0 with Ext JS, authored by Mark Lancaster.

I won't pretend to be an expert on ExtJS - I'm not. But I do know a number of customers who use Oracle Application Express combined with ExtJS and they love it. Additionally, I do know some of them have informally consulted with Mark Lancaster, as he is one of the recognized experts on the planet for Oracle Application Express and ExtJS. Mark's Playpen is his hosted demonstration site of the types of things you can accomplish with APEX and ExtJS.

I've browsed through this book and it seems to be another home run from Packt - very clearly presented and very polished.

Monday, March 14, 2011

Rewrite it in .NET

A few weeks ago I received a call from a customer who had an urgent issue. While this customer had gone the proper route and was working with Oracle Support, they really needed an immediate resolution. I've also worked directly with this customer since 1996 and have respect for them. They're judicious with my time - they usually only call when the situation is dire.

This customer had an Internet facing application that was previously written using an older version of Oracle Portal. To get a "quick win", they rewrote the application in Oracle Application Express - it's a simple lookup application that also uses Oracle Text. Once they released this application, during the peak periods of the day they would witness hundreds of database sessions blocking each other. The cause of the problem? Why - Oracle Application Express, of course. The director had called me to "give one last try using Oracle Application Express" before they rewrote it in .NET. He knew how to push my buttons! I dropped what I was doing, reviewed their Service Request with Oracle Support, and immediately arranged to visit them onsite that afternoon.

Once I was onsite, the DBA showed me numerous sessions on their production system, all executing the SQL statement:

DELETE FROM wwv_flow_data
WHERE flow_instance = :b1
AND item_id IN (
FROM wwv_flow_page_plugs
WHERE flow_id = :b3
AND page_id = :b2
AND plug_source_type IN

I looked at this statement and I immediately knew that this was a Reset Pagination process. The scope of this statement is for a single logical APEX session - meaning that there are Internet users growing impatient with the performance of the application and clicking "Refresh" in their browsers. [When a page request is executing in the APEX engine, if the end-user clicks Reload in their browser, it will not preempt the database session currently processing their request - it will initiate a new request to a new database session, concurrently competing for the same finite resources as the first.] And why would an end-user click "Refresh" in their browser? Most likely, it's a poor performing application.

Without getting into too many details of Oracle Text, this customer had implemented their search interface such that it could easily result in a broad wildcard expansion search, i.e., the query would be expanded to essentially include all indexed search terms in their Oracle Text CONTAINS clause. To prove my point, I asked them to isolate the query that one of these long-running sessions was performing and issue the same query from their command-line client. The same query took between 30 and 60 seconds - from the command line! Now imagine if you're some Internet user trying to get some work done. You enter your search terms and click Go - the natural tendency after a few seconds, let alone 60 seconds, is to try the search again. And that's what was happening - end-users were growing impatient and repeatedly clicking Go.

The SQL statement from above (DELETE FROM wwv_flow_data...) was due to a Reset Pagination process they were performing after clicking the Go button. The pagination information for the user in the session table of APEX would be deleted, the APEX engine intentionally does not COMMIT, and then their slow-running query would run - all the while maintaining the row-lock until the query completes. The second session comes along (from the impatient user), the DELETE for the pagination information is issued, but now it's blocked from the first session.

My simple advice to them was to improve the performance of the application, as it was essentially unusable. My recommendations included:

  • Inspect the search terms being provided, and if all of the search terms were to be eliminated because of stop words in their Oracle Text stopword list or the search terms didn't meet a minimum length, raise an error and don't issue the query.

  • Add a PREFIX_INDEX and SUBSTRING_INDEX to their Oracle Text index, since they were performing numerous wildcard queries

  • Disable the Go button after it was clicked. This doesn't make the query run faster, but it can help to reduce the number of concurrently running sessions, emanating from an impatient user.

  • Change the pagination of their Classic SQL Report to "Row Ranges X to Y (with next and previous links)." Again, this won't make the query run any faster, but this is one of the fastest pagination styles of Classic Reports - simply because it reduces the number of row fetches performed by the APEX Reporting Engine.

This customer implemented these changes over the next couple of days and they haven't had any issues since (and this was over 2 months ago).

The customer presented this as "an APEX issue." And somehow rewriting it in .NET was going to correct all these problems. But guess what? If they rewrote it in .NET or PHP or Ruby or Java or Formspider or whatever, the original query that took 30 - 60 seconds to execute is going to continue to take 30 - 60 seconds to execute in all of these environments, and their end-users were going to continue to be impatient. In my recommendations above, only the last one is specific to Application Express, and that one is simply a nice-to have. It was the first and second recommendations which fixed their application. Was APEX the cause of their performance problem? I say "no" - and in fact, it did a good job of exposing their application problem.

Thursday, February 17, 2011

APEX and Tablespaces

Adrian, an internal employee, recently sent me the following issue/question:

I am the administrator and owner of an internal APEX workspace. Through time I made multiple requests to increase the storage size of this workspace each time by 10 MB. All these requests got approved and as a result the total table space should have been of at least 80-100 MB. However, it is of only 41 MB.

To see the current table space I went to Administration > Monitor Activity > Report Tablespace Utilization (popup) and I got the following report:

Tablespace Name


Amount Used

Amount Free

Percentage Used






I then requested 10 additional MB to be added and the request was immediately approved. Then, I extracted again the above report, but the exact same values appear (total tablespace – 41MB) so the approved increase of 10 MB is not visible.

Could you please indicate if there is a limit of table space that I can request, if this is a bug or if I am no (sic) looking in the right place?

Good question. Adrian stumbled upon a bug in APEX, but this topic deserves some further explanation of how tablespaces are created in Oracle Application Express.

When requesting or creating a new workspace in Oracle Application Express, you can choose to associate the workspace with an existing schema (one that you or a DBA may have created beforehand), or you can ask for Application Express to provision a new database user and associated tablespace. This post is centered on the latter scenario - where APEX provisions the tablespace and user.

The actual location of the tablespace's associated data file is determined by:
  1. If Oracle Managed Files are in use, then the name of the datafile and location are determined by the DBA via the OMF setting.

  2. If Oracle Managed Files are not in use, then the datafile is created in the same physical database file system directory where Application Express is installed. For example, if Application Express is installed in the SYSAUX tablespace, and the location of a datafile for the SYSAUX tablespace is /d1/oracle/oradata/mydb/sysaux01.dbf, then the files for the newly provisioned tablespaces will also be created in /d1/oracle/oradata/mydb.
In Application Express 3.2.1 and earlier, the name of the tablespace is FLOW_x and the datafile is named FLOW_y.dbf, where x and y are an integer. In Application Express 4.0 and later, the name of the tablespace follows the pattern of APEX_x and data file name of APEX_y.dbf. In both APEX 3.2.1 and earlier and APEX 4.0, obviously, the data file name is generated by OMF when Oracle Managed Files is in use.

So how big will these files and tablespaces be when initially created? Good question. In APEX 3.2.1 and earlier, the data file for the provisioned tablespace is as big as the "storage request" for the workspace. If someone signed up for a workspace and requested a 100 MB workspace, APEX would create a tablespace and associated datafile of exactly 100 MB. The datafile of this tablespace would not autoextend. The actual size of the data file on the file system would be 100 MB. In our hosting of Application Express over the years, we learned two important facts:

  1. People will always request the maximum permitted. If you let them choose 1 GB, they'll choose 1 GB even if they're only going to store 100 rows in a single table.

  2. Most of the tablespaces on our hosted instances were full of "air" and occupied a lot of disk space unnecessarily.

In Application Express 4.0, we got a bit smarter. There is now an internal instance administration setting named Auto Extend Tablespaces, and in all APEX 4.0 and later instances, this is enabled by default. Now when a tablespace is created, the associated data file is set to autoextend up to a maximum of the requested size. The initial size of the data file will be 10% of the requested size, and autoextend up 10% at a time. So if you request a 100 MB workspace, the initial data file will be roughly 10 MB and autoextend 10 MB at a time up to 100 MB.

Workspace administrators can request additional storage, and in APEX 3.2.1 and earlier, when this type of request is processed, the tablespace data file is simply resized by the requested amount. But in APEX 4.0 and later, if the data file is set to autoextend, then when processing this type of request, the maximum size of the data file is adjusted but the data file itself is not increased in size until it needs to be (via the normal auto extend action of a tablespace).

So now getting back to Adrian's initial question, he stumbled upon a bug in Application Express 4.0. The report he was executing in Application Express showed the physical allocated size of the tablespace data file and did not take into account that the data file itself could grow. To prove this, I ran the following query:

SQL> select bytes, autoextensible, maxbytes 
from dba_data_files
where tablespace_name = 'APEX_1193421002954380607';

---------- --- ----------
45154304 YES 320339968

Sure enough - Adrian's data file is only approximately 45 MB and can grow up to roughly 320 MB. Thus, I can conclude with the following:

  1. This is a simple bug which will be fixed in Oracle Application Express 4.1.
  2. Adrian - you can take a break from making so many workspace storage requests, please.

Tuesday, February 08, 2011

Is Application Express only good for Excel or Access replacement?

Is Oracle Application Express only good for Excel or Access replacement? This is what those with no experience with Oracle Application Express like to assert. And although I repeatedly give hard evidence on my blog about the scalability and breadth of Oracle Application Express, some (rightly so) view my writings as biased. Your Honor, I present to you Exhibit A.

Here is a recent press release from our partner Insum Solutions inc.:

A couple things to observe:
  1. The customer, Ecole Polytechnique de Montreal, decided upon an Oracle Application Express solution before putting it out for bid.
  2. The application (over 100 pages, 1,500 companies, 3,000 contacts, 5,000 students) is certainly more than an Excel or Access replacement application.

Friday, January 21, 2011

Is anyone using Oracle Application Express?

Is anyone really using Oracle Application Express? I couldn't tell you the number of times I've been asked that question by customers. There's been little marketing of APEX (in part because the marketing dollars flow to for-cost products & features), yet I believe it's grown organically at a high rate over the last few years.

Iloon Ellen-Wolf from Oracle Support provided a list of all of the customers who have filed a Service Request against Application Express through Oracle Support over the last two calendar years - all 1,723 of them. And as I often say, it's a virtual who's who of the Fortune 1000.

Recently, Kris Rice was kind enough to aggregate the Oracle Technology Network download numbers of Application Express over the last few years, and it shows a nice trend:

2006 83,482
2007 107,021
2008 119,092
2009 148,304
2010 209,555

In 3 years, we have doubled the number of downloads of Oracle Application Express. It's difficult to extrapolate this to "number of users" or "number of developers", because a single instance of Oracle Application Express can suffice for hundreds of workspaces and thousands of developers and even more end-users (for example, within Oracle we have one APEX application used by 40,000 distinct users on a daily basis). Other customers download Application Express for their personal use on XE. So the number of developers and users per download is somewhere between 1 and "a plethora."

Activity on continues to be very brisk. We typically average well over 4 million page views per week and over 500 new workspaces every week. For the past 7 days:

Total Page Views: 4,882,817
Distinct Applications: 3,644
Distinct Users: 4,141
Number of Workspaces: 10,777
Number of Applications: 37,883
Workspaces Approved: 535
Workspaces Purged: 578
To be purged in a week: 594

535 new users in the past week alone kicking the tires of Oracle Application Express can't be all that bad.

So back to my original question - is anyone using Oracle Application Express? Yes, without a doubt - and it continues to grow.

Thursday, January 20, 2011

APEX 4.0 Cookbook

At the risk of alienating other authors (which is not my intent at all), I did want to raise awareness of a recently published book Oracle APEX 4.0 Cookbook written by Marcel van der Plas and Michel van Zoest. Very often, new developers to Oracle Application Express get up to speed quickly with the basics of APEX, but sometimes are left floundering when trying to do a specific task. The Oracle documentation on APEX tends to explain "what something is", and the Oracle By Examples are excellent soup-to-nuts tutorials, but often times, someone just wants to know "how do I do X?". That's where I think this book does an excellent job at filling this void, with very lucid and finite topics (e.g., Sending mail via APEX, Uploading and downloading files, Protecting a page using an authorization scheme).

P.S. For the record, I am deeply appreciative of all authors of APEX Books who have contributed so much of their time to help grow the APEX community.

Friday, January 14, 2011

Counting Clicks via Dynamic Actions

The ink was barely dry on my recent post Counting Clicks when Dimitri Gielis suggested a much simpler way to achieve this in Oracle Application Express 4.0. And even though the suggested implementation is buried in the comments of that post, I wanted to explicitly offer it here.

This second solution is even more straightforward. It involves:
  1. A dynamic action
  2. An anchor with a specific class or ID

That's it! A demonstration of this logic is on pages 4 and 5 of this application here.

I created a simple table to store some of my most commonly used URLs. I built an application containing a report and form on top of this table, and then removed a lot of the functionality of the form to make it read-only.

Then, I created a simple log table with columns for some of the information I'd like to capture:

create table click_log(
user_name varchar2(1000),
click_ts timestamp,
ip_address varchar2(100),
user_agent varchar2(1000),
apex_session_id number,
rownum_clicked number,
info varchar2(4000) )

With this basic infrastructure in place, I now want to implement the two constructs to easily enable click logging of these URLs.

On page 5 of my application (the "form" page), I created a dynamic action named "Log Click" and with the following attributes:

  • Event: Click
  • Selection Type: jQuery Selector
  • jQuery Selector: #link
  • Condition: No Condition

The True Action was of Action Type "Execute PL/SQL Code" and the actual code itself was:

insert into click_log(
info )
( v('APP_USER'),
'Clicked: ' || v('P5_NAME') );

So with my Dynamic Action, all I needed to do is modify the anchor on my page to include the id "link". I did this by augmenting the URLs on my form page, changing them from:

<a href="" target="_new"></a>


<a href="" target="_new" id="link"></a>

Because my jQuery Selector was for any element with an ID of "link", and I wanted the event to operate on my link, I needed to add this "id" attribute to my anchor.

You can see a demonstration of this logic in pages 4 and 5 here in My Favorite URLs application.

Wednesday, January 12, 2011

Counting clicks

In the past week, I've been asked to help implement the "counting of clicks" in two different applications, the forthcoming Oracle Learning Library being one of these applications. All that's really desired is to track when someone clicks on a link. This is usually accomplished by responding to the click, inserting a row into a log table and then redirecting to the desired target URL.

If you're familiar with APEX_UTIL.COUNT_CLICK, this is implemented in a similar fashion. There is an underlying log table in Application Express which has a row inserted into it before redirecting to the desired URL. But the requirements of these two applications were a little bit different and required something more than APEX_UTIL.COUNT_CLICK, namely:

  1. They wanted to preserve the logged clicks for all time and they wanted to insert into their own log table. In the case of APEX_UTIL.COUNT_CLICK, it's actually stored in a set of two rotating tables which switch every 2 weeks.

  2. In the case of the Oracle Learning Library, we found that when you're on an SSL-rendered page, if you redirect to another URL, even a fully-qualified one, it will always redirect to an SSL (https) URL. Thus, we needed a solution which would not do a browser redirect.

The solution I devised was quite straightforward. It involved:

  1. A page-level on-demand process
  2. A local JavaScript function
  3. and an anchor with an Onclick event

That's it! A demonstration of this logic is in an application here.

To demonstrate, I created a simple table to store some of my most commonly used URLs. I built an application containing a report and form on top of this table, and then removed a lot of the functionality of the form to make it read-only.

Then, I created a simple log table with columns for some of the information I'd like to capture:

create table click_log(
user_name varchar2(1000),
click_ts timestamp,
ip_address varchar2(100),
user_agent varchar2(1000),
apex_session_id number,
rownum_clicked number,
info varchar2(4000) )

With this basic infrastructure in place, I now want to implement the three constructs to easily enable click logging of these URLs.

On page 2 of my application (the "form" page), I created a PL/SQL process named LOG_CLICK with execution point "On Demand - Run this process when requested by AJAX". It consisted of the code:

insert into click_log(
info )
( v('APP_USER'),
'Clicked: ' || v('P2_NAME') );

Note how I'm able to use both session state information via the APEX v() function and the PL/SQL Web Toolkit functions owa_util to get other information about the user.

The second part of this solution is a JavaScript function which I used to invoke my on-demand function. I defined this in the page-level attributes of page 2 in the JavaScript section for Function and Global Variable Declaration:

function f_logClick(){
var req = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=LOG_CLICK',&APP_PAGE_ID.);
var gReturn = req.get();
req = null;
return gReturn;

I'm no AJAX wizard and I won't pretend to be, but this is the syntax (even in Application Express 4.0) to invoke an On-Demand process from JavaScript. For those familiar with On-Demand process invocation, note how I had to specify &APP_PAGE_ID. because I defined this process at the page-level and not the application-level.

So with my On-Demand process in place and my JavaScript function defined, all I needed to do is simply employ this function. And I did this by augmenting the URLs on my form page, changing them from:

<a href="" target="_new"></a>


<a href="" target="_new" onclick="f_logClick();"></a>

You can see a demonstration of this logic here in My Favorite URLs application.

Note: View the comments below for an even simpler implementation using dynamic actions, new in Application Express 4.0


Today, Niels, a customer, asked me:

"We have currently installed and I couldn't find any information about the upgrade from this version to What are the changes? What should be installed (metalink patch or full install)?"

A customer had earlier pointed out how this new .07 version had silently been rolled out. I addressed it on the OTN forum, but to repeat it here:

There are two differences (bug fixes) between and, namely:

  • 10347091 - Page sentry function of custom auth scheme does not work in
  • 10406645 - Expired Application Express password cannot be changed

These issues were discovered after the release of APEX and I didn't think that the fixes for these warranted a "4.0.3". I knew I risked confusion for our customers, which appeared to have happened, but that may only be a result of poor communication on our part.

If you have APEX, or installed, you can download the patch set ( from My Oracle Support and apply it. If you don't have Application Express installed at all, then when you download Application Express 4.0, you'll automatically get