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

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 - 15:44:37 CDT