Application contexts in multi-user environments

Giganews Newsgroups
Subject: Application contexts in multi-user environments
Posted by:  Ed Holloman (inval…@invalid.com)
Date: Mon, 19 Apr 2004

[Oracle9i Enterprise Edition Release 9.2.0.3.0]

Hi,

I'm experimenting with application contexts as a means of utilizing
bind variables in variable WHERE clauses.

In a multi-user environment where the database is accessed from an
application using a single user id, are there any conflicts involved
when the same procedure is accessed multiple times and the same context
variable is set to different values by each call? In other words, can I
assume that between the time the user_id of my_context is set and the
query is executed in procedure call 1 that those values will be used
and not be affected by other procedure calls also setting the user_id
of my_context?

Thanks,

Ed Holloman

================================================
create or replace context my_context using my_proc;
================================================

In procedure call 1 to my_proc:
================================================
my_query := 'SELECT * FROM my_table ' ||
            'WHERE user_id = sys_context(''my_context'',''user_id'')';

dbms_session.set_context('my_context', 'user_id', '12345');

OPEN my_refcursor FOR
  my_query;

================================================

In procedure call 2 to my_proc:
================================================
my_query := 'SELECT * FROM my_table ' ||
            'WHERE buyer_id = sys_context(''my_context'',''user_id'')';

dbms_session.set_context('my_context', 'user_id', '45678');

OPEN my_refcursor FOR
  my_query;
================================================

etc.

Replies