Currently in my application we have view objects and also we have some stored procedure calls to insert and update data (in the procedure we don't commit it. Due to the volume and complexity we couldn't migrate all the procedures). After the stored procedure call we do refresh the VO (Also in the EO we have checked necessary attributes to refresh on update and insert) to pick up the changes that happened in the stored procedure call. It is working fine and i can be able to see the updated values in the VO after the procedure call. However
Now my question is what will happen on activation and passivation? In the passivation the pinned DB connection is released and used for another AM. Will the stored changes will be rolled back. I mean the DB transaction. If rollback then the VO updates based on the procedure call will be also rolled back? I have tested this behaviour after disabled the AM pooling and set the pool size to 1. It is working but failing some times (In the procedure i have data insert for the child table, after the refresh i could be able to see the child VOs, some times empty child). Did anyone try this approach before?
Also what will happen if AM goes to passivation during the stored procedure executing ( i am getting the DB connection from getDBTransaction(() method). When i was testing i got the resultset null and i checked the stack and it was due to the connection closed). If AM behaves it like that is it reliable to use procedure call inside the AM?
always commit after executing stored procedures calls that change data in your database. There is no guarantee that after passivation you get the same AM or database connection back. Passivation only keeps state, not data unless changed in the entity cache.
I agree with Frank.
In our application we always commit after calling PL/SQL procedures which performs DML-Statements (insert, update, delete).
Without commits you will - beside the passivation problem - face problems with locked records in the database.
To refresh the values from DB we use the "restore current row after rollback" approach (which is applicable for commit too).