2 Replies Latest reply: Dec 2, 2012 10:07 PM by rp0428 RSS

    maintain the same session identifier for multiple sessions

    scottjhn
      This issue started as a font end (java side) issue, but I need to properly understand the Oracle side. So I raise it here.

      Let's say a user makes the call to Oracle in two (or more) connections. Each connection will start a new session.

      In first call, the user connects to the DB then execute a stored procedure to insert data into the parent table, which then returns a primary key.

      Next the user makes another call to Oracle (hence a new session), execute another stored procedure and pass the primary key, so that a row can be inserted into the child (dependency) table.

      In doing so, I need the second connection to share the same session as the first one. So for both the first and the second connections, I coded them to execute the following functions

      DBMS_session.set_identifier('100');
      DBMS_session.set_context('ACCOUNT_CTX', 'account_id', '0');

      But the second call (the one to insert a row in the child table) always failed by complaining that the foreign key does not exist in the parent table.

      This indicates that the system deemed the second connection as a separate and different session, hence the primary key inserted in the parent table (by the first call) is not seen by the second call.


      What could be the solution (from the Oracle side) or suggestion such that I can keep the two separate connections in the same session ?


      Thanks.

      Edited by: scottjhn on Dec 2, 2012 7:30 PM
        • 1. Re: maintain the same session identifier for multiple sessions
          sb92075
          scottjhn wrote:
          This issue started as a font end (java side) issue, but I need to properly understand the Oracle side. So I raise it here.

          Let's say a user makes the call to Oracle in two (or more) connections. Each connection will start a new session.

          In first call, the user connects to the DB then execute a stored procedure to insert data into the parent table, which then returns a primary key.
          if no COMMIT was ever issued, then no other session can "see" the parent record.
          Next the user makes another call to Oracle (hence a new session), execute another stored procedure and pass the primary key, so that a row can be inserted into the child (dependency) table.

          In doing so, I need the second connection to share the same session as the first one. So for both the first and the second connections, I coded them to execute the following functions

          DBMS_session.set_identifier('100');
          DBMS_session.set_context('ACCOUNT_CTX', 'account_id', '0');

          But the second call (the one to insert a row in the child table) always failed by complaining that the foreign key does not exist in the parent table.

          This indicates that the system deemed the second connection as a separate and different session, hence the primary key inserted in the parent table (by the first call) is not seen by the second call.


          What could be the solution (from the Oracle side) such that I can keep the two separate connections in the same session ?


          Thanks.

          Edited by: scottjhn on Dec 2, 2012 7:30 PM
          • 2. Re: maintain the same session identifier for multiple sessions
            rp0428
            >
            This issue started as a font end (java side) issue, but I need to properly understand the Oracle side. So I raise it here.
            >
            Yes it did - and I answered you there.
            When you cross-post you at least need to provide cross-links to the other threads so people can follow everything that is being discussed.
            JDBC mutiple Inserts from java side (want to keep the session same)

            Go edit your other thread and provide the link to this one.
            >
            What could be the solution (from the Oracle side) or suggestion such that I can keep the two separate connections in the same session ?
            >
            As I said in the other thread: either the same session needs to insert the parent and child records or you need to commit after the parent record is inserted.
            >
            In first call, the user connects to the DB then execute a stored procedure to insert data into the parent table, which then returns a primary key.

            Next the user makes another call to Oracle (hence a new session), execute another stored procedure and pass the primary key, so that a row can be inserted into the child (dependency) table.
            >
            The second call doesn't have to be a new session. You can use the same connection you used for the first call. But if you are using two connections or two sessions the first session has to commit its work or the second session won't see it.