Forum Stats

  • 3,816,536 Users
  • 2,259,202 Discussions
  • 7,893,507 Comments

Discussions

Controlling Transaction commit when using sqlProcedure

adfLearner
adfLearner Member Posts: 332
edited Feb 26, 2013 2:25AM in JDeveloper and ADF
Please suggest me if the below usecase can be achieved :

When using sqlProcedure application will not be passivation safe, so we need to do the SqlProcedure and Commit call in same request.

1. SqlProcedure AddPersonProc adds a person in Person table and returns the Success , Warning and Failure status with list of Message and Rule in case of Warning and Failure.
2. Now based on the different status, I want to do following :
a) Success - Commit . ( Pretty Easy )
b) Error - RollBack ( Pretty Easy ) .. and show the Errors.
c) Warning - Show Warnings with AcceptAndContinue and Cancel button ( This i have done ).
On Cancel - Roll Back ( Pretty Easy )
On AcceptAndContinue - Commit the new record to the database.

Problem faced : For warning ... 1) if I do the rollBack... then my VO looses all data.
2) If I don't do rollback and simply call AM commit then there is no data committed in case AM passivate ( point highlighted in bold )
3) If I don't do rollBack and again calls SqlProcedure AddPersonProc and then commit then two records inserted in case AM doesn't passivate .

Please suggest how do I implement this with available SqlProcedure AddPersonProc .

Thanks,
Rajdeep

Answers

  • Subramanian Meyyappan
    Subramanian Meyyappan Member Posts: 3,010 Bronze Trophy
    hi user,

    above step will be easy for you(as you said). when will be easy for me? means, you should give proper details. which stack (adfbc,ejb).? jdev which release/version.?

    you said that you had ah problem
    Problem faced : For warning ... 1) if I do the rollBack... then my VO looses all data.
    If I don't do rollback and simply call AM commit then there is no data committed in case AM passivate ( point highlighted in bold )
    If I don't do rollBack and again calls SqlProcedure AddPersonProc and then commit then two records inserted in case AM doesn't passivate .
    these things framework nature: if you rollback "Application Module" means you cant get back your data. if you rollback means there is no need commit.

    i had few doubts?.

    1. option(ok,cancel,AcceptAndContinue) do you want to do this af:popup.

    2. i know about successful execution of sqlprocedure and error in executing sql procedure in "ADF". where is the warning comes from...

    3. are you speaking about dbprocedure created with warning means. the framework should find out and intimate to user. is't.... As Far As i Know if a procedure created with warning never been going to perform successful
    execution.

    please clarify me.
  • adfLearner
    adfLearner Member Posts: 332
    Jdev version 11.1.2.2.0
    ADF BC is the business service layer used.

    option(ok,cancel,AcceptAndContinue) do you want to do this af:popup I already implemented popup for this.

    Success , warning and Failure - There is one IN/OUT parameter which is table of complex object type. Procedure does some validation checks for the other parameters and based on that populates the IN/OUT parameter with status along with rule and message.

    I hope this makes it more clear.

    Thanks,
    Rajdeep
  • SantoshVaza
    SantoshVaza Member Posts: 1,128
    Hi,

    use pragma autonomous_transaction inside procedure and commit or rollback inside procedure itself..
    http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems002.htm
  • Frank Nimphius-Oracle
    Frank Nimphius-Oracle Member Posts: 29,689 Bronze Badge
    Hi,

    note that passivation does not include changes applied to the database session. In fact recommendation is to always commit database changes in the database at the end of the call to the stored procedure as there is no guarantee that the same database session is handed back on subsequent request. In your case this means that you don't rely on a pending state of a stored procedure class but in case of a warning re-submit the same stored procedure with a flag indicating that despite of conflicts the stored procedure should be executed. This means however that in case your stored procedure added a row that you want the user to decide on, you remove it from the table.

    ADF BC is no container for PLSQL and as such doe snot passivate or add PLSQL changes to its transaction context. So here's the rule for what you want to achieve

    1. PLSQ goes through --> commit in PLSQL
    2. PLSQL fails with no update to database -> show error dialog
    3. PLSQL succeeds but there is user confirmation required --> undo PLSQL outcome and show dialog. On use OK re-run PLSQL with enforce flag and commit change

    Frank
This discussion has been closed.