Posted by : Akshay Patil
Monday, 9 March 2015
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:
- How do we elegantly derive this
for each end user of our application?
- 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:
1
|
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:
- 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.
- This time zone offset
information will be sent to Application Express and recorded in the APEX
session information for that user.
- 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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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:
1
2
3
|
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.