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

Srini Mad

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.

Timo Hahn

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

Timo

Srini Mad

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

Timo Hahn

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