Friday, January 21, 2011

Is anyone using Oracle Application Express?

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

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

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


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


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

Activity on 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.

Thursday, January 20, 2011

APEX 4.0 Cookbook

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





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

Friday, January 14, 2011

Counting Clicks via Dynamic Actions

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

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

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

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

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


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


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

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

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


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




insert into click_log(
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.


Wednesday, January 12, 2011

Counting clicks

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

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

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

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

The solution I devised was quite straightforward. It involved:

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

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

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

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



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


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

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


insert into click_log(
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.