Controlling Transaction commit when using sqlProcedure

adfLearner

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

Subramanian Meyyappan

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

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

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

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