In article <c60d7e$sim$1@news.BelWue.DE>, Holger Baer
<holger.baer@science-computing.de> wrote:

> Ed Holloman wrote:
> > [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?
> >
>
> I'd suggest a good reading and understanding about what sessions in
> Oracle do; http://asktom.oracle.com and http://tahiti.oracle.com will
> provide you with enough information.
>
> To answer your question in short: What you do is a valid approach, and
> quite scalable too. The fact that you have to use dbms_session to set the
> value of a context variable is some kind of a hint, ain't it?
>
> HTH
> Holger

Thanks for the reply.

But what if the procedure is called multiple times within the SAME
session?

Situation:

Web gadget is populated by result set from procedure call applying a
WHERE clause based on userid. Gadget is associated with multiple middle
tier collaborations/threads, each one connected to Oracle in a
different session. Sessions remain active until system goes down,
error, etc.

So, let's say there are three collaborations (3 sessions) associated
with a particular procedure. The procedure would be called with
whatever thread is not busy, but if there were multiple procedure
calls, the procedure could be called more than once in a session, one
right after the other. So, if the same procedure is called more than
once in the same session, is the application context a viable method to
set bind varibles?

steps within procedure:
1. select WHERE clause based on userid
2. concatenate WHERE clause with SQL string
3. use dbms_session.set_context to set bind variables
4. execute dynamic sql string


Question, then is, given two calls to the same procedure in same
session, is it possible for step 3 in procedure call 2 to set the
context variables before step 4 can happen in procedure call 1 (step 4
in procedure call 1 incorrectly uses context values set in step 3 of
procedure call 2)?

Thanks,

Ed Holloman