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:

• Oracle APEX on Facebook
• Oracle SQL Developer on Facebook
• Oracle SQL Developer Twitter

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.

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.5	2004
HTML DB 1.6	2005
HTML DB 2.0	2005
Application Express 2.1	2006
Application Express 2.2	2006
Application Express 3.0	2007
Application Express 3.1	2008
Application Express 3.2	2009
Application Express 4.0	2010
Application Express 4.1	2011*

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.

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.

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 http://tryapexnow.com. 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 tryapexnow.com. They won't have any idea what you're talking about.

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

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.

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 http://tryapexnow.com 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 http://tryapexnow.com 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.

Application Express 4.1 Early Adopter 1 available

The Early Adopter 1 version of Oracle Application Express 4.1 is now available at tryapexnow.com. 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 tryapexnow.com instance.
  
  
• When we do refresh the tryapexnow.com 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 tryapexnow.com. They won't have any idea what you're talking about.

Thank you for all of your support.

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.

Indiana University Supports Researchers, Inspires Innovation and Secures More Research Grants with Web-Based Data 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.

Application Express 4.0 and Library Cache Latch Contention in Oracle DB 11.2.0.2

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 11.2.0.2, 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:

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

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. apex.oracle.com was on APEX 4.0.2 on 11.2.0.2 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 11.2.0.2, 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 4.0.2.00.07 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.

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 https://irecruitment.oracle.com
2. In the field labeled "Search Job Descriptions by Keywords ", enter IRC1453677
3. Click the "Search" button

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.

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 (
SELECT ID
FROM wwv_flow_page_plugs
WHERE flow_id = :b3
AND page_id = :b2
AND plug_source_type IN
('SIMPLE_CHART', 'UPDATABLE_SQL_QUERY',
'DBMSSQL_CURSOR',
'FUNCTION_RETURNING_DBMSSQL_CURSOR',
'FUNCTION_RETURNING_SQL_QUERY_CACHED',
'FUNCTION_RETURNING_SQL_QUERY', 'STRUCTURED_QUERY',
'SQL_QUERY', 'DYNAMIC_QUERY'))

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.

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	Bytes	Amount Used	Amount Free	Percentage Used
APEX_1193421002954380607	43,057,152	40,697,856	2,359,296	94.52

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';

     BYTES AUT   MAXBYTES
---------- --- ----------
  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.

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

MONTREAL POLYTECHNIQUE SCHOOL SELECTS INSUM SOLUTION TO DEVELOP THEIR INTERNSHIP AND JOB MANAGEMENT APPLICATION

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.

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 apex.oracle.com 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.

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.

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(
    user_name,
    click_ts,
    ip_address,
    user_agent,
    apex_session_id,
    rownum_clicked,
    info )
values
    ( v('APP_USER'),
      systimestamp,
      owa_util.get_cgi_env('REMOTE_ADDR'), 
      owa_util.get_cgi_env('HTTP_USER_AGENT'), 
      v('APP_SESSION'),
      v('P5_ID'),
      'Clicked: ' || v('P5_NAME') );
commit;

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="http://www.amazon.com/" target="_new">http://www.amazon.com</a>

to:

<a href="http://www.amazon.com/" target="_new" id="link">http://www.amazon.com</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.

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(
    user_name,
    click_ts,
    ip_address,
    user_agent,
    apex_session_id,
    rownum_clicked,
    info )
values
    ( v('APP_USER'),
      systimestamp,
      owa_util.get_cgi_env('REMOTE_ADDR'), 
      owa_util.get_cgi_env('HTTP_USER_AGENT'), 
      v('APP_SESSION'),
      v('P2_ID'),
      'Clicked: ' || v('P2_NAME') );
commit;

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="http://www.amazon.com/" target="_new">http://www.amazon.com</a>

to:

<a href="http://www.amazon.com/" target="_new" onclick="f_logClick();">http://www.amazon.com</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

APEX 4.0.2.00.07

Today, Niels, a customer, asked me:

"We have currently installed 4.0.2.00.06 and I couldn't find any information about the upgrade from this version to 4.0.2.00.07. 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 4.0.2.00.06 and 4.0.2.00.07, namely:

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

These issues were discovered after the release of APEX 4.0.2.00.06 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 4.0.0.00.46, 4.0.1.00.03 or 4.0.2.00.06 installed, you can download the patch set (p10173973_11202_GENERIC.zip) 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 4.0.2.00.07.

Oracle Application Express 4.0.2 released

Today, Oracle Application Express 4.0.2 was released. This corrects a number of issues discovered in the original Application Express 4.0 release as well as the Application Express 4.0.1 release. In the README of the Application Express 4.0.2 patch set, there is a listing of all of the bugs corrected in this patch set. You can view a copy of it here. The patch set is cumulative and includes all of the patches and bugs fixed in the Application Express 4.0.1 patch set.

We have also repackaged the Oracle Application Express full distribution. If you have never installed or upgraded to Oracle Application Express 4.0, you can download the latest full distribution and install this and you'll have Application Express 4.0.2 out-of-the-box. Since this always creates confusion for customers (do you need the patch set or the full distribution?), use this guide:

1. If you have Oracle Application Express release 4.0 or Oracle Application Express 4.0.1 installed, download the Oracle Application Express 4.0.2 patch set from My Oracle Support and apply it. Look for patch number 10173973. Even though the file is named p10173973_11202_GENERIC.zip, seemingly only for DB 11.2.0.2, it can actually be used against any supported database version running Application Express 4.0.
   
   
2. If you have Oracle Application Express release 3.2.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.0.2 release from the Oracle Technology Network (OTN).
   
   
3. If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.0.2 release from the Oracle Technology Network (OTN).

And how do you determine what version you're running? Well, if you're a DBA, you can connect to the database as SYS using SQL*Plus and issue:

select version from sys.dba_registry where comp_id = 'APEX';

If you can login to the Application Express development environment, you'll see the version number in the lower right of every page.

As mentioned in an earlier post, there are two new and very polished themes included in Application Express 4.0.2. A lot of work has also gone into correcting the display and usability issues of all of the modern themes.

Oracle Application Express at http://apex.oracle.com had the final 4.0.2.00.06 patch set applied today, November 19, 2010.