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.