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.