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/B...8/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

In article <gevodu$c0p$1 (AT) news-int (DOT) gatech.edu>,
ryoko (AT) gatech (DOT) rmv.this.part.edu says...>
Hi

On reading this I recalled some problems we had where IIRC the
expiration date we told owa_cookie to use was simply ignored.

So we ended up setting the cookies in a much simpler way:

owa_util.mime_header('text/html', FALSE);
htp.p('Set-Cookie: '||'sessionid='||l_id||'; expires=Mon, 01-Jan-1990
08:00:00 GMT; path=/ ');
owa_util.http_header_close;

i.e. you don't have to use owa_cookie - it does no magic

I hope this is helpful to you.

--
jeremy

In article <gevodu$c0p$1 (AT) news-int (DOT) gatech.edu>,
ryoko (AT) gatech (DOT) rmv.this.part.edu says...>
Hi

On reading this I recalled some problems we had where IIRC the
expiration date we told owa_cookie to use was simply ignored.

So we ended up setting the cookies in a much simpler way:

owa_util.mime_header('text/html', FALSE);
htp.p('Set-Cookie: '||'sessionid='||l_id||'; expires=Mon, 01-Jan-1990
08:00:00 GMT; path=/ ');
owa_util.http_header_close;

i.e. you don't have to use owa_cookie - it does no magic

I hope this is helpful to you.

--
jeremy

In article <gevodu$c0p$1 (AT) news-int (DOT) gatech.edu>,
ryoko (AT) gatech (DOT) rmv.this.part.edu says...>
Hi

On reading this I recalled some problems we had where IIRC the
expiration date we told owa_cookie to use was simply ignored.

So we ended up setting the cookies in a much simpler way:

owa_util.mime_header('text/html', FALSE);
htp.p('Set-Cookie: '||'sessionid='||l_id||'; expires=Mon, 01-Jan-1990
08:00:00 GMT; path=/ ');
owa_util.http_header_close;

i.e. you don't have to use owa_cookie - it does no magic

I hope this is helpful to you.

--
jeremy

In article <gevodu$c0p$1 (AT) news-int (DOT) gatech.edu>,
ryoko (AT) gatech (DOT) rmv.this.part.edu says...>
Hi

On reading this I recalled some problems we had where IIRC the
expiration date we told owa_cookie to use was simply ignored.

So we ended up setting the cookies in a much simpler way:

owa_util.mime_header('text/html', FALSE);
htp.p('Set-Cookie: '||'sessionid='||l_id||'; expires=Mon, 01-Jan-1990
08:00:00 GMT; path=/ ');
owa_util.http_header_close;

i.e. you don't have to use owa_cookie - it does no magic

I hope this is helpful to you.

--
jeremy

Jeremy <jeremy0505 (AT) gmail (DOT) com> wa:

Thanks, I'll take a look at this and see if we can't get it to work, as
something like this solution is definitely what I was looking for.
Thanks again!

--
"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

Jeremy <jeremy0505 (AT) gmail (DOT) com> wa:

Thanks, I'll take a look at this and see if we can't get it to work, as
something like this solution is definitely what I was looking for.
Thanks again!

--
"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

Jeremy <jeremy0505 (AT) gmail (DOT) com> wa:

Thanks, I'll take a look at this and see if we can't get it to work, as
something like this solution is definitely what I was looking for.
Thanks again!

--
"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

Jeremy <jeremy0505 (AT) gmail (DOT) com> wa:

Thanks, I'll take a look at this and see if we can't get it to work, as
something like this solution is definitely what I was looking for.
Thanks again!

--
"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