Forum Stats

  • 3,782,455 Users
  • 2,254,650 Discussions
  • 7,880,081 Comments

Discussions

AM Pooling on non-commited sessions running PL-SQL procedures

Srini Mad
Srini Mad Member Posts: 631
edited Apr 5, 2018 7:29AM in JDeveloper and ADF

Jdev: 12.2.1.2

We have a functionality that samples records from staging tables and then bulk inserts into another table. Since it is data intensive and volume may run into few hundreds to may be a thousand records, we are considering implementing this in PL-SQL than in ADF-BC. We were hoping, we could make a stored procedure call to effect this and then display the results to the user and if all is good, the user clicks on "Agree" to COMMIT in database.

This triggered off some interesting discussions internally in the team on how AM pooling (Passivation/Activation) would impact this.Since there is going to be a gap in commit, there is a chance that the AM is passivated between the stored procedure execution & the user hitting "Agree" to COMMIT. So, if a new AM is picked up and alongwith it a different DB connection, would this not fail?

I am guessing it will fail and passivation/activation works for middle tier based cache and it does not have any means to know that the previous DB session has uncommitted records.

Tagged:

Answers

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,782 Red Diamond
    edited Apr 5, 2018 6:01AM

    Why don't you test this by switching am pooling off and run the test case?

    Timo

  • Srini Mad
    Srini Mad Member Posts: 631
    edited Apr 5, 2018 6:39AM

    Because that does not guarantee a different DB connection being picked up?

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,782 Red Diamond
    edited Apr 5, 2018 7:29AM

    This would not matter. When you turn AM pooling off and work with two users (or browsers) on the app, each call will need to reset the db connection as there can't be a pending db transaction. The state is held in the framework (or hteps_txn table or file). When you do some pl/sql procedure outside the framework, and the framework resets the connection (by calling rollback on it), your work is lost. The framework doesn't know about your changes, so I assume the changes are lost.

    But, as I have not tested this I'm not 100% sure.

    Timo

    Srini Mad
This discussion has been closed.