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.

Did You Get Your Free Server, Yet?

Did you know that Amazon Web Services is giving away free virtual servers? I got mine yesterday. As of November 1, 2010, Amazon Web Services has a free usage tier for a year if you sign up for a new Amazon Web Services account. You can sign up and read more here. According to Amazon:

"To help new AWS customers get started in the cloud, AWS is introducing a new free usage tier. Beginning November 1, new AWS customers will be able to run a free Amazon EC2 Micro Instance for a year, while also leveraging a new free usage tier for Amazon S3, Amazon Elastic Block Store, Amazon Elastic Load Balancing, and AWS data transfer. AWS’s free usage tier can be used for anything you want to run in the cloud: launch new applications, test existing applications in the cloud, or simply gain hands-on experience with AWS."

If you've ever wanted to experiment with a server in the cloud and experience the robust offering that Amazon Web Services has in this space, now's your chance. We have had tremendous success running the Early Adopter programs of Oracle Application Express on Amazon Web Services.

Application Express 4.0.2 applied to apex.oracle.com

On Friday evening, 12-NOV-2010, a pre-release version of the Application Express 4.0.2 patch set was applied to apex.oracle.com. A listing of all issues corrected in this patch set will be published next week when we release this patch set on My Oracle Support.

Normally, new features are not included in a patch set but there are two new themes included in APEX 4.0.2 (themes finely crafted by Shakeeb).

Theme 21

Theme 22

Application Express, Network ACLs and Oracle Database 11gR2

In Oracle Database 11gR1, a new feature was introduced called Fine-Grained Access to External Network Services. Succinctly defined, this feature gives an administrator control over which database users are permitted to access external network services, and on which ports. If an application relied upon the PL/SQL packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP or UTL_INADDR, they would now need to be given permission to access the external network service via a Network ACL. An excellent writeup of this feature is on ORACLE-BASE.

There have been two changes in this feature in Database 11gR2 which may impact Application Express users. These aren't necessarily documented so prominently in the release notes or README of Database 11gR2, so I felt it necessary to share them here.

1. In Database 11gR2 11.2.0.1, the precedence order in evaluation of the network ACL entries has been changed to most specific to least specific. More about this below.
   
   
2. In Database 11gR2 11.2.0.2, the network ACL now applies to any use of DBMS_LDAP.

In the installation guide for Oracle Application Express, we document this feature in Oracle Database 11gR1 and also how to create a network access control list which permits the APEX engine to access any network service on any network port. The name of the ACL in our example is power_users.xml. It just so happened that someone else on our instance of Application Express needed access to an outbound HTTP proxy, so I created a separate network ACL for this user.

The Network ACL privileges looked like:

SQL> column host format a30
SQL> column acl format a40
SQL> select host, lower_port, upper_port, acl from dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------------
proxyserver.domain.com          80         80        /sys/acls/proxy_rule.xml
*                                                    /sys/acls/power_users.xml


SQL> column acl format a40
SQL> column principal format a30
SQL> select acl, principal from dba_network_acl_privileges;

ACL                                      PRINCIPAL
---------------------------------------- ------------------------------
/sys/acls/proxy_rule.xml                 SOME_OTHER_USER
/sys/acls/power_users.xml                APEX_040000

Prior to 11.2.0.1, if APEX_040000 needed to access the outbound HTTP proxy, this would be evaluated in terms of the least specific rule to the most specific rule. The ACL power_users.xml permitted access to all hosts on all ports, and thus, there were no issues. But in 11.2.0.1 and higher, this is now evaluated from most specific to least specific. If APEX_040000 now needs to access the outbound HTTP proxy, it is blocked and we'll encounter the dreaded error message "ORA-24247: network access denied by access control list (ACL)". A match for the host in the outbound HTTP proxy is found in /sys/acls/proxy_rule.xml, APEX_040000 does not have privileges on that ACL, and now the fine-grained access control blocks the request.

To correct this, I had to grant privilege on the proxy ACL to APEX_040000:

    dbms_network_acl_admin.add_privilege(
        acl => 'proxy_rule.xml',
        principal => 'APEX_040000',
        is_grant => TRUE,
        privilege => 'connect' );

To address the second point, in 11.2.0.2, access to the host and port specified by methods in the DBMS_LDAP PL/SQL package are now controlled via this same fine-grained access control to external network services.

I can understand, from a security perspective, why this behavior was changed. However, if you've come to rely upon this behavior, upgrading to Oracle Database 11gR2 may introduce some changed behavior when accessing external network resources.

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager - Addendum

As mentioned in my earlier post about Oracle Application Express integration with Oracle Access Manager, Dilip Gowda, an Oracle consultant, very kindly shared a Word document detailing all of the steps he performed to get Oracle Access Manager 10.1 working with Oracle Application Express 3.2. It can be downloaded from here. The custom authentication scheme for APEX should work with any APEX version - it's a generic header variable authentication scheme.

Once I get Oracle Access Manager 11gR1 installed and configured, I hope to prepare and share a similar document. The eventual goal is to turn this into an official whitepaper and then ultimately provide an out-of-the-box header variable authentication scheme in a future version of Oracle Application Express.

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager

Our customers frequently ask about how to integrate Oracle Access Manager authentication with Oracle Application Express. There is currently a thread on the Oracle Technology Network discussion forum, asking for this type of solution. It has always been my intention to present this as an official whitepaper and recommended solution from Oracle. However, I have been struggling with some Oracle Access Manager configuration issues and I simply did not want to delay any further. The "official" whitepaper and detailed instructions will have to come later.

Back in March, 2010, I took careful note of a message that Scott Spadafore on our team had sent to someone in Oracle Support. It was a generic solution for authentication via an HTTP header variable. A couple months ago, this question came up again and Tyler Muth provided me a slightly modified version of what Scott had originally authored. With some more minor modifications on my part, I can share this custom authentication scheme, which can be used with Oracle Access Manager and really any environment which will securely set a header variable to an authenticated username.

The page sentry function is:

create or replace function header_variable_page_sentry ( p_apex_user in varchar2 default 'APEX_PUBLIC_USER' ) 
    return boolean 
as
    l_cgi_var_name              varchar2(100) := 'REMOTE_USER';
    l_authenticated_username    varchar2(256) := upper(owa_util.get_cgi_env(l_cgi_var_name));
    --
    l_current_sid               number;    
begin
    -- check to ensure that we are running as the correct database user
    if user != upper(p_apex_user) then 
        return false; 
    end if;

    if l_authenticated_username is null then
        return false; 
    end if;     
  
  
    l_current_sid := apex_custom_auth.get_session_id_from_cookie;
    if apex_custom_auth.is_session_valid then
        apex_application.g_instance := l_current_sid;
        if l_authenticated_username = apex_custom_auth.get_username then
            apex_custom_auth.define_user_session(
                p_user=>l_authenticated_username,
                p_session_id=>l_current_sid);      
            return true;
        else -- username mismatch. unset the session cookie and redirect back here to take other branch
            apex_custom_auth.logout(
                p_this_app=>v('APP_ID'),
                p_next_app_page_sess=>v('APP_ID')||':'||nvl(v('APP_PAGE_ID'),0)||':'||l_current_sid);
            apex_application.g_unrecoverable_error := true; -- tell apex engine to quit            
            return false;
        end if;
        
    else -- application session cookie not valid; we need a new apex session
        apex_custom_auth.define_user_session(
            p_user=>l_authenticated_username, 
            p_session_id=>apex_custom_auth.get_next_session_id);
        apex_application.g_unrecoverable_error := true; -- tell apex engine to quit
        --
        if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  then
            wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'|| wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
        else
            wwv_flow_custom_auth.remember_deep_link(p_url=>'f?p='||
                to_char(apex_application.g_flow_id)||':'||
                to_char(nvl(apex_application.g_flow_step_id,0))||':'||
                to_char(apex_application.g_instance));
        end if;
        --        -- register session in APEX sessions table,set cookie,redirect back
        apex_custom_auth.post_login( 
            p_uname      => l_authenticated_username,
            p_session_id => nv('APP_SESSION'),
            p_app_page   => apex_application.g_flow_id||':'||nvl(apex_application.g_flow_step_id,0)); 
        return false;        
    end if;    
end header_variable_page_sentry;

The high-level steps to be performed are:

1. Compile this function header_variable_page_sentry in the parsing schema of your application
2. Create a new custom authentication scheme. In the Page Sentry Function attribute of the custom authentication scheme, enter: return header_variable_page_sentry;
3. Add directive PlsqlCGIEnvironmentList inside the corresponding APEX Database Access Descriptor. By default, OAM Webgate uses the header variable REMOTE_USER.
4. Secure the APEX application in Oracle Access Policy Manager by defining its corresponding Policy Domain.
5. Back in your APEX application, make this new authentication scheme "current" for your application.

The custom authentication scheme should work in any version of Application Express. A gentleman from Oracle Consulting got this to work at a customer site using APEX 3.2 and OAM 10g. He very graciously put together a document detailing all of the steps he performed in Application Express and Oracle Access Manager to get this to work, which is invaluable to someone like me who is essentially OAM-ignorant. I've asked for his permission to share this document, and when/if I get his okay, I'll make it available from this blog.

Brief Discussion of Oracle Application Express

Last week at Oracle OpenWorld 2010 in San Francisco, Justin Kestelyn of the Oracle Technology Network interviewed Oracle Application Express Product Manager David Peake. If you're looking for a fairly short discussion about Oracle Application Express, this is a good place to start as I thought David's message was clear.

Customizing the look of Interactive Reports

If you've ever wanted to have precise control over the look and feel of the Interactive Reports in your Oracle Application Express application, take a look at Shakeeb Rahman's recent blog post on his new blog http://apex.shak.us. Shakeeb Rahman is on the Application Express product development team and is the master of all things HTML and CSS.

APEX events at Oracle OpenWorld 2010 - for your iPhone

Marc Sewtz from the Oracle Application Express development team has been busy crafting a set of HTML templates and styleswhich are suitable for an iPhone or other small device. And to demonstrate this, Marc has crafted an APEX application running on apex.oracle.com (and Application Express 4.0) listing all of the Application Express-related sessions at Oracle OpenWorld 2010.

Bookmark this: http://apex.oracle.com/pls/apex/f?p=oow2010

And you can read more about it on Marc's blog.


P.S. Don't bother using this application with Firefox nor Internet Explorer. It will work with any Webkit-based browser (Safari, Chrome). But it will look even better on your iPhone or Droid.

SINCE when?

This will not be new information for long-time Oracle Application Express users, but it's worth mentioning for those new to Oracle Application Express.

In APEX page items (those using Automatic DML) and columns in SQL Reports & Interactive Reports, you have the ability to format date and timestamp values using a variety of format masks. Every report column and form page item has a format attribute. For date and timestamp columns, you can specify any valid Oracle date or timestamp format mask, with a full listing provided here. Some examples include:

MM/DD/YYYY

DD.MM.RRRR

DS FMHH24:MI:SS

DL

RRRR-MM-DD"T"hh24:mi:ss.xff

In APEX applications, you can also employ the SINCE format mask on date and timestamp columns. This is specific to APEX applications in the context of the format mask for report columns and Automatic DML page items. Instead of showing your end-users values like:

09/11/2010

you can present a value of:

3 days ago

The time period is relative to the difference between the time it is rendered and the value itself. Values can include 'seconds ago', 'minutes ago', 'hours ago', 'days ago', 'weeks ago', 'months ago', and 'years ago'. The benefit of using SINCE is:

• It's time zone agnostic. Regardless of the time zone of your end user, 3 minutes ago is always 3 minutes ago.
• It's locale agnostic. Some locales interpret 09/11 as September 11, other locales interpret 09/11 as November 9. Everyone interprets '3 days ago' as 3 days ago.
• It's translated into the 10 languages provided for Application Express 4.0. The output will be correctly translated based upon your user's language preference.

New in Application Express 4.0:

• Support for future dates and timestamps. You can now have values like '3 weeks from now', '2 days from now'.
• Support for the SINCE format mask against columns of type TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.
• And an API (should be documented, but I find that it's not right now) to compute this yourself:

APEX_UTIL.GET_SINCE( p_date IN DATE ) RETURN VARCHAR2

APEX_UTIL.GET_SINCE_TSWTZ( p_timestamp IN TIMESTAMP WITH TIME ZONE ) RETURN VARCHAR2

APEX_UTIL.GET_SINCE_TSWLTZ( p_ltimestamp IN TIMESTAMP WITH LOCAL TIME ZONE ) RETURN VARCHAR2

Impress your boss and your end-users with the SINCE format mask!

Going to Oracle OpenWorld 2010?

Are you going to Oracle OpenWorld 2010, which starts on September 19, 2010? If so, be sure to attend the "APEX Meetup" on Tuesday night. Dimitri has more information on his blog.

Automatic Time Zone support in Application Express 4.0

A feature of Application Express 4.0 which hasn't received a lot of press but is useful for those building applications that span time zones is the Automatic Time Zone application attribute.

The Oracle database has this wonderfully rich data type called TIMESTAMP WITH LOCAL TIME ZONE. The elegance of this data type is that the value stored in this column will be displayed in the user's current database session time zone. Having written a PL/SQL package to do time zone conversion, it is a non-trival exercise to develop this type of functionality let alone maintain it. Wouldn't it be great if we could put this burden of maintaining constantly evolving time zone rules and daylight saving time dates on the database? Well, you get this for free with TIMESTAMP WITH LOCAL TIME ZONE.

So if all we need to do is set the database session time zone, then:

1. How do we elegantly derive this for each end user of our application?
2. How do we ensure that every page view and page submission in Application Express has its database session time zone set correctly for a particular user?

There were numerous suggestions in the past, of storing a user's preferred time zone as a preference and then authoring a PL/SQL block in the VPD attribute of an application like:

execute immediate 'alter session set time_zone =''' || :MY_USER_TIMEZONE || '''';

Not exactly obvious. And this still doesn't answer question #1 of how do we elegantly derive this. This is where the new Automatic Time Zone attribute is useful.

In the Application Builder, if you edit the Application Properties and navigate to the Globalization subtab, you should see something like:

By default, Automatic Time Zone will be set to 'No'. When set to 'Yes', this will now change the behavior of your application:

1. At the beginning of an Application Express session (which happens at the beginning each time a user runs your application ), the time zone offset will be calculated from their Web browser client.
2. This time zone offset information will be sent to Application Express and recorded in the APEX session information for that user.
3. Then, each and every page view for the duration of their APEX session, the Application Express engine will read this value and set the database session time zone to this value.

All you have to do is employ data types which are time zone aware (like TIMESTAMP WITH LOCAL TIME ZONE; DATE is not time zone aware) and check a box in your application definition. It couldn't be simpler!

To demonstrate this, I created a simple application using the following DDL:

create table tz_log( 
    id number primary key, 
    username varchar2(255) not null, 
    tz varchar2(512) not null, 
    created_ts timestamp not null );

create or replace trigger tz_log_trg1
    before insert on tz_log
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    --
    :new.created_ts := localtimestamp;
end;

Then, I just built an application with a SQL report on this table and added an on New Instance PL/SQL Process of:

insert into tz_log (username, tz) values(:APP_USER, apex_util.get_session_time_zone );
commit;

You can run this sample application here. Just keep in mind - it will require you to authenticate with your oracle.com credentials (the same credentials you use if you login to the OTN discussion forum) and it will record your visit in a log table, which others can view. Here's what it looks like - nothing fancy:

If you pay close attention, immediately after authentication, you'll see a URL like:

https://apex.oracle.com/pls/otn/f?p=27207:1:127976719236631&tz=-4:00

Obviously, your APEX session identifier and time zone value will be different than what I show above. But you'll see that there is a new parameter 'tz' to the 'f' procedure. And it is through this interface that you can create a URL to an APEX application and explicitly set the APEX session time zone to a different value. After you login, change the time zone value in the URL to something else (e.g., tz=0:00) and watch the values in the "Inserted into the Log Table (in your local time zone)" report column automatically adjust to that time zone. The underlying report definition didn't change - we're still simply selecting the TIMESTAMP WITH LOCAL TIME ZONE column out of the database, just now the database is automatically converting that value to display in the current session time zone.

You can also programmatically set and get the APEX session time zone setting using two new APIs in Application Express 4.0, namely APEX_UTIL.SET_SESSION_TIME_ZONE and APEX_UTIL.GET_SESSION_TIME_ZONE.

Pelotonia10

Sorry, this is not a post about anything Oracle-related. But as our dear, departed friend Carl used to say - it's my blog.

I had the privilege of participating in this year's Pelotonia bike tour. What is Pelotonia, you may ask? "Pelotonia is a grassroots bike tour with one goal: to end cancer. Pelotonia raises money for innovative and life saving cancer research at The Ohio State University Comprehensive Cancer Center - James Cancer Hospital and Solove Research Institute."

This event had special significance for me, as our next-door neighbor, Jacob Carlino, was diagnosed with a rare form of cancer earlier this year and is undergoing treatment now. Jacob is 12 years old. A child like Jacob or any person, for that matter, shouldn't have to suffer through this horrible disease. Jacob's father Dave organized a team in support of his son Jacob, and I had the privilege of being on this team. Above is the team - Dan, Matt, Scott, me and Dave. On the front of our shirts is a picture of Jacob who we were honoring with our team's tour.

Pelotonia was a most-impressive and well-organized event. There were over 4,000 riders and thousands of volunteers in addition to a great deal of corporate sponsorship. The estimate is that this one event will raise over $8 million, with 100% of this money going directly to fund cancer research at the Ohio State University Cancer Care Center and Solove Research Institute. The honorary chair of this year's Pelotonia tour was Ohio State football legend Chris Spielman, who he himself lost his wife to cancer just 9 short months ago.

My goal for this blog post? To simply raise awareness of this disease, and to also let the world know that there is an ever-growing army of people right here in Ohio who is contributing in ways small and large to the eradication of this disease. Lastly, I would like to recognize and thank the sponsors of me for this ride - I am grateful for your generous support: Sue, Sergio & Priscila, Jason & Shelley, Eric, Bob & Marge, Matt & Gretchen, Tim & Susie, Anton & Chris, Kathy & Terry, Neil & Margaret, Tom, Nada & Matt, Frank, Harry, and Mike & Anita.

Application Express 4.0.1 released

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

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.1 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 installed, download the Oracle Application Express 4.0.1 patch set from My Oracle Support and apply it. Look for patch number 9976149. Even though the file is named p9976149_11201_GENERIC.zip, seemingly only for DB 11.2.0.1, 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.1 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.1 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.

Oracle Application Express at http://apex.oracle.com had the 4.0.1.00.03 patch set applied on Saturday, August 14, 2010.

Application Express and parsing of SQL

Recently, I worked with Oracle Support who was helping a customer who had a variety of questions about Oracle Application Express. One of the questions was:

"Please provide a detailed explanation of how apex interacts with database security. It appears that apex is manipulating queries prior to presenting them tot he database to be run either by wrapping them in a pl/sql block that prevents role based security from working or doing a pre-security check that doesn't check role level security prior to issuing a query, or some other even that bypasses role based security. "


Since there really isn't any great explanation of this anywhere, I'd like to provide a brief but lucid explanation about how parsing of SQL works in Oracle Application Express.

• The Oracle database ships with a supplied PL/SQL package named DBMS_SQL, to perform the execution of dynamic SQL.
  
  
• The Oracle database also ships with a supplied but undocumented PL/SQL package named SYS.DBMS_SYS_SQL. This package enables the execution of dynamic SQL but it also enables the parsing of a statement as a specific database user and with the privileges of this specific database user. This PL/SQL package is highly privileged, obviously. The EXECUTE privilege on this package is not granted to database user, by default. It should almost never be granted to any database user, ever. When asked to grant execution on this package to someone, a smart DBA will always say "no."
  
  
• Until Database version 10.2.0.3, both DBMS_SQL and DBMS_SYS_SQL would not observe roles when parsing SQL. This was consistent with database object access in PL/SQL itself (database roles are not observed in PL/SQL, in case you didn't know).
  
  
• In Database 10.2.0.3 and later (and XE), SYS.DBMS_SYS_SQL was changed to support a flag which enabled the observation of database roles when parsing SQL.
  
  
• In Application Express 2.1 and later, support was added to Oracle Application Express SQL Workshop on DB versions 10.2.0.3 and later to observe database roles, to be consistent with SQL*Plus. However, the execution of SQL in an Application Express application still does not observe roles when parsing user SQL. This remains true for the recently released Application Express 4.0.

So this explains a few things:

1. If you've ever wondered how Application Express can parse SQL as a specific database user and with the privileges of this database user but without ever connecting as that database user, this is the answer - DBMS_SYS_SQL.
   
   
2. When a new database user/schema is provisioned through Application Express, the discrete system privileges are granted to this new database user and not through any database role.
   
   
3. When you look the underlying database view V$SESSION, it will show that the database sessions associated with Application Express applications are connected as the minimally privileged database user APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL gateway). But within that session, the underlying Application Express engine is being invoked and, after determining who the SQL can be parsed as for that specific page view, the SQL is being parsed as a different database user.
   
   
4. This also explains why, in some database versions, it will appear that roles are enabled when you issue DML statements from SQL Commands in SQL Workshop, but in the development of your application and in the execution of your application, it will appear that database roles are not enabled and that direct object privileges are required.