owa_cookie and owa_custom interaction
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:
- 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)
- 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.
- 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)
- any other suggestions? Would prefer a solution that does not involve dbms_scheduler.
--Received on Thu Nov 06 2008 - 15:43:26 CST
"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