Thursday, August 22, 2013

ADF Essentials 12c ADF BC 'BO_SP' ORA-01086 Error

It seems like there are differences running same ADF application on WebLogic and on Glassfish runtime. These differences are minor but could cause some headache to the developers. In this particular case, I would like to describe ORA-01086: savepoint 'BO-SP' never established error. Typically this error could happen, if PS_TXN table is unavailable. However, this is not the case with ADF Essentials 12c - PS_TXN table exists and application is able to access it. 'BO_SP' error is generated when DB constraint is triggered, instead of returning DB error message - ADF Essentials 12c displays ORA-01086: savepoint 'BO-SP' never established error.

How to reproduce: set Salary attribute value to -1, there is constraint in the DB to check for positive values. Constraint is invoked, but instead of getting constraint message - 'BO_SP' error happens:


Workaround is to enable DB pooling for the Application Module, by setting Disconnect Application Module Upon Release property:


This is recommended setting for ADF BC performance and Data Source connections usage optimization: Stress Testing Oracle ADF BC Applications - Do Connection Pooling and TXN Disconnect Level. Is hard to say why exactly setting DB pooling fixes 'BO_SP' error for ADF Essentials 12c, I guess is related to DB driver used in Glassfish. With DB pooling enabled, DB connection is released after request - perhaps this allows to avoid rolling back transaction to BO_SP and in turn fixes the error.

DB constraint is displayed correctly with DB pooling enabled:


Download sample application - ListViewAppEssentials.zip.

3 comments:

Jesús Recio said...

Hi Andrejus,
We are migrating our application to ADF Essentials 12 in Glassfish 3.1.2.
We have tested the solution you describe for this problem and it works in taskflows with pages that binds to only one AM, but not
when we use two AM's.
Thanks a lot

Andrej Baranovskij said...

Yes, I will need to test that.

Andrejus

giovanni said...

Hi Andrejus,
post here my little contribution,
I solved the problem of the multiple commit in Glassfish 3.2.1, in my task flow train, where I need perform the commit in every train stop (because in jdev 12.1.2 sometimes lose data from the pages in mozilla firefox).
So after every commit I must execute the executequery of the iterator as following code:

//agg 08.04.15 ORA ESEGUO IL SAVATAGGIO FORZATO
if(ControllerContext.getInstance().getCurrentViewPort().isDataDirty()){
OperationBinding method = getBindings().getOperationBinding("Commit");
method.execute();

//ATTENZIONE EVITALE INSERIRE IL REQUERY AL FINE DI EVITARE L'ERRORE
//DOVUTO ALLE COMMIT A CASCATA CHE FANNO PERDERE I RIFERIMENTI
//ORA-01086: savepoint 'BO-SP' never established error.
DCIteratorBinding parentIter =
(DCIteratorBinding) BindingContext.getCurrent().findBindingContainerByPath("/Faces/mwp/train/mwp_train_ps.jsff").get("WeMwpCwpIterator");

parentIter.executeQuery();


}

Best regards
Giovanni