Re: Application contexts in multi-user environments

Ed Holloman wrote:
> 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

Ok, this is slightly different from your original question. I'm not sure if context is the right approach for this - sounds like your best bet is to use execute immediate .... using <bind variables>.

HTH
Holger Received on Tue May 04 2004 - 06:43:28 CDT