Application contexts in multi-user environments

From: Ed Holloman <invalid_at_invalid.com>
Date: Mon, 19 Apr 2004 03:46:34 GMT
Message-ID: <180420042246345062%invalid@invalid.com>

[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. Received on Sun Apr 18 2004 - 22:46:34 CDT