2 Replies Latest reply on Mar 27, 2013 2:42 PM by Leon Dorfling

    Calling DB Stored Procedures Before Commiting Changes.

    Leon Dorfling

      I am using JDeveloper

      I have a use case that requires me to do the do the following;

      When creating a new master/detail transaction, I must call a stored procedure that must select the entire master/detail transaction and insert it into an archive table. To solve this I have overridden the beforeCommit method in the AM implementation class and called the stored procedure passing in the PK of the current row in my header VO. I arrived at this solution after reading [http://www.oracle.com/technetwork/developer-tools/jdev/index-092937.html] written by Steve Meunch. Specifically the following, as I needed the entire transaction available in the database before calling the stored procedure.


      Execute code after Forms has "posted" all necessary rows to the database, but before issuing the data COMMIT to end the transaction. If you want a single block of code for the whole transaction, you can override the doCommit() method in a custom DBTransactionImpl object and write some code before calling super. To execute entity-specific code before commit for each affected entity in the transaction, override the beforeCommit() method on your entity object and write some code there.+

      The second part of the use case allows a delete on the header only. This action should also call the stored procedure and insert the entire master/detail transaction into the archive table. For this, my previous solution does not work. The current row is not the row that was deleted. In the article referenced above I assumed the following would offer me the solution,


      Execute code before row removed from the datablock is DELETED from the database during "post" processing. Override doDML() method in your entity class and if the operation equals DML_DELETE then write code before calling the super.+

      Effectively this does work, but the overridden beforeCommit also executes, so I have the deleted record as well as the next record in the VO inserted into my archive table.

      I have tried a few options by exposing the method that calls the stored procedure to my UI project, but this also has some problems, as my PK are populated using DBSequences and at the point in my task flow when I execute the method the sequence is not yet correctly populated.

      Ideally I would like some input into how I could solve this at my business services layer.