Re: Mutliple calls to an ASP page which executes an Oracle Stored Procedure. Sessions blending causing errors.

9.2.0.5 Database... not sure about the Microsoft side of things. We have tried both the Oracle ODBC driver and Microsoft ODBC driver, both with the same disheartening result. Oracle support has replicated the problem and what appears to be happening is the default functionality of Microsoft ... Microsoft has an MTS server that maintains a connection for 2minutes after a request has been made. If another request occurs within that time, it reuses that connection. Problem appears that it reuses it as long as transactions continue to occur until 2min lapses with no activity at which time it releases the session. We would like a way to turn this so called connection pooling effect off. we disabled the connection pool feature on the ODBC setup for the Microsoft server, but the problem persisted. It is why we suspect that if we can go to true multi-threaded environment, the problem will go away (hopefully). I am only frustrated that I can't find out how others got around the problem in the past without use of C, Java, or .NET. Just plain old ASP.

Barry

On Mon, 30 May 2005 18:08:22 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>bsc7080mqc_at_mylxhq.com wrote:
>> Okay here is an odd one that neither us or Oracle can currently explain. Need some help from the experts. I have an Oracle Stored
>> package for which I provide a common stored procedure for an external ASP web page to call upon and pass the appropriate parameters
>> to. This procedure in turn evals the parms and then executes additional procedures and Oracle provide APIs. An external XML
>> processing engine parses an XML transaction and identifies the appropriate parameters to pass to the stored procedure. This process
>> works quite well. Problem we are running in to is that when I have multiple XML transactions occuring at the same time, the local
>> variables begin to bleed over from one transaction to another. My assumption is that each transaction would be unto itself. This is
>> not holding true. When I execute via SQL*Plus I get a separate Oracle SESSION_ID assigned to each executing SQL*Plus call, even if
>> performed in separate copies of SQL*Plus. Each call the local variables start out empty, and I would never expect them to bleed over
>> to another parallel session. BY using a select from dual of the SYS_CONTEXT call to retrieve the SESSION_ID, I can verify and
>> repeatedly demonstrate that the SESSION_ID is being reused when called from the ASP process vs. the SQL*Plus sessions give me
>> separate ids.
>>
>> Oracle concurs that this does not appear to be normal. We have disagreements internally that SESSION_ID should be unique and
>> separate. One individual says no that it can be the same, however when it is the same we see blending of data. WHen it is not the
>> same it works fine. So evidence lends me to say no its not supposed to be the same.
>>
>> One expert on our side indicated that ASP is not a true multi-threaded environment but a Free Threaded environment and is intended
>> to share sessions to reduce resource issues and boost performance, which may be where our problem is. We are working on a proof of
>> concept using .NET, but surely this problem has been encountered by others as well as overcome.
>>
>> In summary, we need a means to call an Oracle Stored Procedure multiple times including sequential and parallel executions, but
>> maintain separate transactions. There should never be an occassion where the local varibles of one transaction blend over to the
>> other.
>>
>> Any one ???
>>
>>
>> Barry Chase
>> Mylxhq - My Oracle Portal
>> http://www.mylxhq.com
>
>And your version number is?
Received on Mon May 30 2005 - 23:12:45 CDT