Hi Guys,

This is already posted on OTN forums, but thought I'd post here as
well, though am a bit dismayed by the amount of spam that this group
seems to be on the receiving end of, hopefully some old hands are
still around to lend a hand...

Ta

Ralph


Hi,

I am writing a web application. One of the features is that the
transactions of one user can cause an alert to be raised to another
user.
This other user's session is polling the database every n seconds to
see whether or not there is an alert for them.
The application makes use of custom application contexts to store
information relevent to a users session.
Included in this context is a flag which denotes whether or not there
is an alert for that user (which makes the polling calls extremely
light weight).
In order to set that flag the user causing the alert spoofs into the
other users context and sets the flag. This works a treat.
The problem I have comes with the polling routine clearing the alert
flag once it has been set and read as such.

The check alert function looks like this...

FUNCTION check_alert return varchar2
IS
i_am constant varchar2(128) := 'fe.check_alert';
ret_val varchar2(1);
BEGIN
ret_val := nvl(sysadmin_ctx.ctx_pkg.get_ctx(p_attr =>
'alert_flag'),'N');

if ret_val = 'Y' then
-- clear alert flag
sys_utl.log(p_level => 'DEBUG', p_module => i_am, p_message =>
'Alert flag was Y. Clearing flag.');
sysadmin_ctx.ctx_pkg.set_ctx(p_attr => 'alert_flag', p_value =>
'N');
sys_utl.log(p_level => 'DEBUG', p_module => i_am, p_message =>
'Cleared flag. value now :'||nvl(sysadmin_ctx.ctx_pkg.get_ctx(p_attr
=> 'alert_flag'),'N'));

end if;

return ret_val;
EXCEPTION
when OTHERS then
sys_utl.err_and_stop (p_module => i_am ,p_sqlcode => sqlcode,
p_errstack => dbms_utility.format_error_stack, p_backtrace =>
dbms_utility.format_error_backtrace);

END check_alert;

The relevent parts of the ctx_pkg are standard and look like this.

PROCEDURE set_ctx(p_attr IN VARCHAR2, p_value IN VARCHAR2)
AS
BEGIN
DBMS_SESSION.SET_CONTEXT(
namespace => 'custom_ctx',
attribute => p_attr,
value => p_value,
username => USER, -- Retrieves the session user, will always
be same for web users
client_id => session_id_global);
END set_ctx;

FUNCTION get_ctx (p_attr varchar2) return varchar2
is
begin
return(sys_context('custom_ctx',p_attr));
end get_ctx;

Now what is interesting is the resulting debug messages. They look
like this...most of the time...

15-SEP-2010 14:02:21 22 fe.check_alert DEBUG
Alert flag was Y. Clearing flag.
15-SEP-2010 14:02:21 22 fe.check_alert DEBUG
Cleared flag. value now :Y

Occasionally it works and they look like this...which is what i'd
expect.

15-SEP-2010 13:37:11 22 fe.check_alert DEBUG
Alert flag was Y. Clearing flag.
15-SEP-2010 13:37:11 22 fe.check_alert DEBUG
Cleared flag. value now :N

Now I am stumped as to what is going on. Any ideas welcome.

Cheers

Ralph

On 15 Sep, 15:59, "Ralph.in... (AT) googlemail (DOT) com"
<ralph.in... (AT) googlemail (DOT) com> wrote:
Sorry, 11.2 standard edition on Linux 64-Bit...