Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Controlling Transaction commit when using sqlProcedure

adfLearner
Member Posts: 332
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
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
Tagged:
Answers
-
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 problemProblem faced : For warning ... 1) if I do the rollBack... then my VO looses all data.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.
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 .
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. -
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 -
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 -
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.