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?
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon May 30 2005 - 20:08:22 CDT
