Friday, December 14, 2012

ADF Post Changes and PL/SQL Invocation Side Effect

If data will not be commited to the database in the same request - not recommended to use postChanges() method in ADF BC. ADF developers tend to bypass this recommendation, especially when integrating ADF with PL/SQL code and trying to keep temporary data in the database. From my experience, is not safe to use postChanges() method even if database commit is done in the same custom method (read - one request). ADF BC passivation may happen anytime unexpected and then posted data is simply lost. Typically this error is not reproduced in development environment, but only in test or production when there are more concurrent users accessing the system. Good practice - always run your application with Application Module Pooling disabled before sending code to the testing team - this would allow to simulare stress test environment and force ADF BC to switch Application Module pool instances.

I would like to demo most common ADF post changes and PL/SQL invocation side effect with this simple application - PostControlApp.zip. Inside archive you will find SQL file with PL/SQL function for this application - EMPL_SAL_SUM. Function is pretty simple, it calculates total salary for all employees. The idea is to show that after Application Module passivation happens, posted data is lost and total salary is still returned without changes:


There are two custom methods implemented on top of Application Module Implementation class. One method is responsible to post changes and second calls PL/SQL function and returns total salary:


Here is the source code implementation for these two methods:


UI fragment contains two buttons, designed to invoke two custom methods. Total calculated salary value  is displayed visually:


If you decide to run sample application, firstly invoke Show Total Salary button - it will display current sum (0.68M in my case):


Change salary value for one of the employees to be lower - 110 as for example and press Prepare For Update button (this calls postChanges() method):


Method - postChanges() is pushing data to DB, executing DML statement without commiting. Developer tend to think - this is great, we can push data to DB and invoke PL/SQL code over it. Not so fast - keep in mind ADF BC passivation behavior (DB connection from Application Module can be switched at any time and posted data will be lost for the next request):


Invoke Show Total Salary again - you will see a change in calculated total salary (0.67M). This time calculated value was retrieved from PL/SQL function correctly, simply because ADF BC passivation didn't happen:


Disable Application Module pooling - you will get different result:


Change salary value and press Prepare For Update button to invoke postChanges():


This time passivation happens, we can see that from the log:


Calculated total salary value stays the same after refresh - posted data was lost, because of change in database connection:


6 comments:

  said...

Hi,

I am biggest fan of your blog and I started to learn ADF.my biggest concern is that is it possible to use pl/sql with ADF ? could you please advice me a book or some that describes pl/sql usage through ADF deeeply ?

best regards,

Andrej Baranovskij said...

Hi,

I would suggest to read ADF Developer Guide from Oracle, it gives good info about calling PL/SQL from ADF.

Read here: http://docs.oracle.com/cd/E35521_01/web.111230/e16182/bcadvgen.htm#ADFFD1052

Andrejus

Unknown said...

Hi Andrejus,

I have used the post changes in my code, since I want to make the data to be reflected in a ReadOnlyVO and now I am facing the issue in UAT.Can you suggest some alternate solution to make the data reflected in ReadOnlyVO

Andrej Baranovskij said...

Only commit to DB and then reload readonly VO.

Andrejus

Unknown said...

Hi Andrejus,

I cannot commit the data to DB since user can cancel the transaction.We are going to commit the data one the transaction is completed fully.

Regards,
Manohar

Andrej Baranovskij said...

Hi,

Then you should insert data into another VO programmatically - without posting it to DB.

Andrejus