Re: clearing session state?

roger <xrsr_at_rogerware.com> wrote in message news:<Xns942587075BD41rsrrogerwarecom_at_204.127.199.17>...
> Not sure if I'm off the deep end here or not...
>
> I'm writing a .NET (1.1) based application server that
> connects to Oracle (9.2) using connection pooling.
>
> The nature of the .NET connection pooling seems to be that
> when a connection is "closed" and then reopened (returned
> from the pool), the session state remains intact for the
> new use of the connection.
>
> Specifically, things like sys_context values and rows in
> global temporary tables (with on commit preserve rows)
> remain in tact and visible to session that is associated
> with the "new" connection returned from the pool.
>
>
> This leaves me with just a few options, as far as I
> can tell:
>
> 1) Do not use any session state dependant values for anything.
> 2) Explicitly initialize all session values each time a
> connection is obtained from the pool.
> Also, clear/truncate any temporary tables that are used.
> 3) Do not use connection pooling.
>
>
> I'm tempted to opt for #1, just out of frustration.
> However, I'm pretty sure that I'm going to need to take advantage
> of some of these mechanisms as I try to implement the more
> elaborate functionality my application needs to provide.
>
> #3 is bad news. My app manages hundreds of users, all of which
> need database connectivity occasionally and briefly. Connection
> pooling is a very good thing for me.
>
> This leaves #2 which, though it ain't brilliant, seems plausible.
>
> The question then is whether there is some nice oracle mechanism
> (sql, plsql, dbms_* call, ?) to clear all such session state, or
> do I have to clear all values, and temporary tables explicitly?
>
>
> Hope this made sense.
>
>
> Please feel free to offer any alternatives that I haven't
> described. I can't be the first one over this ground.
>
> Thanks.

As long as the Oracle session is held open the Oracle objects, contexts, and pl/sql package states will remain available to the session with their last value.

HTH -- Mark D Powell -- Received on Fri Oct 31 2003 - 21:11:42 CST