[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.

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

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

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

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

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