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

Just want to say thank you for the feedback. We heard back from one of our feelers and it looks like a parameter setting on the ODBC connection string appears to resolve the problem. We have some more testing though.

OLE DB Services=-4;

yes thats a negative 4 on there. It disables the connection pool programmatically for the given execution. This essentially appears to enforce a separate session for each transaction requested. To provide some addtional clarity. We do not have a web page but a secure posting url that a vendor posts there XML transaction to. This in turn parses and then executes a singular Oracle stored procedure. So it is the same path way for all transations. The parms values are the only thing that effectively changes from one transaction to another.

Additional note on this is the following :

The OLE DB .NET Data Provider automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling. For example, the following connection string will disable OLE DB session pooling and automatic transaction enlistment.

Provider=SQLOLEDB; OLE DB Services=-4;

   Data Source=localhost; Integrated Security=SSPI;

For more information about OLE DB session pooling or resource pooling, as well as disabling pooling by overriding OLE DB provider service defaults, see the OLE DB Programmer's Reference in the MSDN library.

CAUTION: You must always close the Connection when you are finished using it. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed are not added or returned to the pool.

On 31 May 2005 13:44:37 -0700, "Chet Justice" <chet.justice_at_pfsf.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
>
>A possible workaround would be to use the dbms_session.set_identifier
>procedure for each page to maintain state.
>
>http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_sessio.htm#996936
>
>On login generate a unique session id and return that to the
>application. Pass that session id to each page and let each page
>(re)set the client identifier in the database.
>
>CREATE OR REPLACE
>PACKAGE p_my_context
>AS
> PROCEDURE set_sessionid(p_sessionid IN VARCHAR2);
>END p_my_context;
>/
>show errors
>
>CREATE OR REPLACE
>PACKAGE BODY p_my_context
>AS
> PROCEDURE set_sessionid(p_sessionid IN VARCHAR2)
> IS
> BEGIN
> dbms_session.set_identifier(p_sessionid);
> END set_sessionid;
>END p_my_context;
>/
>show errors
>
>
>Your call would be something like this (in java, don't know asp)
>
>String mysessionid = (String) session.getAttribute("sessionid");
>String SQL = "BEGIN " +
> " p_my_context.set_sessionid(?); " +
> " p_my_package.do_something(?, ?, ?); " +
> "END;";
>
>CallableStatement stmt = null;
>Connection conn = null;
>DataSource ds = (DataSource) ctx.lookup("jdbc/PFSFuserDS");
>
>try
>{
> conn = ds.getConnection();
> stmt = conn.prepareCall(SQL);
> stmt.setString(1, mysessionid);
> stmt.setString(2, firstParameter);
> ...
> ...
> stmt.execute();
>}
>catch (SQLException ex)
>{
> out.println(ex);
>}
>finally
>{
> if (stmt != null) { stmt.close(); stmt = null; }
> if (conn != null) { conn.close(); conn = null; }
>}
>
>Every page would make the " p_my_context.set_sessionid(?); " call to
>reset the identifier.
>
>There are probably better ways.
>
>Hope this helps.
>
>Chet
Received on Tue May 31 2005 - 18:19:55 CDT