owa_cookie and owa_custom interaction

From: Aya the Vampire Slayer <ryoko_at_gatech.rmv.this.part.edu>
Date: Thu, 6 Nov 2008 21:43:26 +0000 (UTC)
Message-ID: <gevodu$c0p$1@news-int.gatech.edu>


DB: 10.2.0.1.0
App Server: 10.1.3.1.0
OS (both): Windows 2003

Architecture:
The App Server publishes stored procedures in the DB as webpages to a user's web browser (via a mod_plsql/dads.conf configuration).

Problem:
owa_cookie is being used in the DB to create cookies on the client whenever a user logs into the website. It seems that the GMT offset of the passed-in expiry date/time to owa_cookie.send factors in to what it actually sets the cookie's expiration to be based on one of two constants (dbms_server_timezone or dbms_server_gmtdiff), which are defined in the package sys.owa_custom. These constants in this package and the owa_cookie code that use them seem to have no concept of the daylight savings time-related time changes. This of course causes problems when, say, dbms_server_timezone is set to EDT/GMT-4, then the late-year time change happens (EST/GMT-5), the expiry is passed in as 15min after the cookie's creation, and the cookies start being created with an expiry of 45min BEFORE the time when the cookie was created.

Note: Both servers (DB and App) and the clients we were testing with all had the time set properly when the time-change happened, and this is still happening. "select systimestamp from dual;" returns the expected and proper time based on the recent time change.

Documentation on this package seems very, very sparse online. Tahiti only has one page that mentions these two constants (first link), and even then, it doesn't say much about them or how they work. The second link (yes, non-oracle, but gives a little more info) seems to imply there's nothing I can do to get owa_cookie to do the expiry dates properly:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/w_custom.htm http://jstwebsrv.jstus.com/ows-adoc/ap2dvt16.htm

Here's the relevant cookie-creating code:

CREATE OR REPLACE PROCEDURE [...]
AS

  • the offset is 15min v_expiry TIMESTAMP := SYSTIMESTAMP + (PKG_Constants.Session_Timeout/1440); BEGIN owa_cookie.send('sessionid', RawToHex(session_id), v_expiry); [...]

I want a way to have owa_cookie automatically use the correct date/time despite the two time changes per year. Would prefer not to use a work-around, but may have to resort to that if there's simply no way to do it.

Am considering:

  1. v_expiry := SYSTIMESTAMP + (PKG_Constants.Session_Timeout/1440) + SOME_DAYLIGHT_SAVINGSTIME_OFFSET; (adds 1 hour if the DST puts us in EST, adds 0 hours if EDT)
  2. removing "constant" from "dbms_server_timezone"s delcaration in the owa_custom package and resetting it twice a year (via dbms_scheduler), if that will not harm anything and actually work at all. Don't know the impacts of this one, though.
  3. setting up a dbms_scheduler job to recreate the package header twice a year for the time change. (would prefer not to have to do this)
  4. any other suggestions? Would prefer a solution that does not involve dbms_scheduler.
-- 

"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
Received on Thu Nov 06 2008 - 15:43:26 CST

Original text of this message