Re: Application contexts in multi-user environments

In article <c60d7e$sim$1_at_news.BelWue.DE>, Holger Baer <holger.baer_at_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 Received on Mon May 03 2004 - 16:45:44 CDT