Sunday, May 27, 2007

ExecuteWithParams in your Oracle ADF application

Recently I was researching Google Analytics data for my blog. I have noticed, that there are many search engine keywords with 'ExecuteWithParams' word. Really, this action plays one of the central roles in Oracle ADF BC application, it is used quite frequently and people want to know more about it. So, I have decided to build sample application focused on 'ExecuteWithParams' functionality.

Sample application - ExecuteWithParams.zip, is based on DEPARTMENTS table from standard HR schema. Business logic of developed sample is based on idea to allow edit operation only for departments from specified geographic region. Edit form allows to change department location, but only within specified region. And finally, region is specified in property file called UIResources.properties.

In order to achieve business logic requirements I have changed default SQL for Departments View Object - I have joined Departments table with Locations and Countries, in order to retrieve departments only from region specified in :regionIdVal bind variable:

Value for :regionIdVal is assigned in ExecuteWithParams action.

Departments edit form contains location component, I have implemented this component as af:selectOneChoice. It allows to select only locations within specified region. This means, ExecuteWithParams should be also applied for this component. I have edited default SQL for Locations View Object, this View Object is used as a List Data Source for af:selectOneChoice. Modified SQL statement:


Value for :regionIdVal is assigned in ExecuteWithParams action also. This value is defined in UIResources.properties file, for example: regionId=2, you can change it's value at any time.

Let's switch to ExecuteWithParams action. I have created required actions in such way - at first I have dragged and dropped needed data controls and after that I have created actions in page definition file manually. ExecuteWithParams action for Departments View Object definition:


You can notice, that action have one parameter - regionIdVal. When action is created, don't forget to add executable - invokeAction into page definition file for ExecuteWithParams. This will enable automatic execution of defined action. Executable for DepartmentExecuteWithParams action:


Action and executable for Locations View Object can be created in the same way.

And..., how it works:


Edit form shows departments only from region specified in property file, at the same time af:selectOneChoice allows to choose locations only from specified region also.

When running sample application, don't forget to add adf-faces-impl.jar and jsf-impl.jar to application's WEB-INF\lib directory.

9 comments:

Anonymous said...

Thanks Andrejus for this article. In this case the value for the bind variable is set and coming from a property file. Can you expand this example to illustrate how selecting a value on a CoreSelectOneChoice set the value for a bind variable of another CoreSelectOneChoice?

Thx!

Andrej Baranovskij said...

Hi,

Probably, Frank Nimphius post will be useful in your case - ADF Faces: How-to build dependent lists boxes with ADF and ADF Faces Part - II.

Regards,
Andrejus

Anonymous said...

Hello Andrejus, and sorry to bother you again.

This code from Frank doesn't work:

http://www.orablogs.com/fnimphius/archives/001894.html

...for this reason:

http://forums.oracle.com/forums/thread.jspa?threadID=526479

...and I haven't gotten a response.

The if condition is never true because the instance of the class is "UISelectItems". Casting to CoreSelectOneChoice produces a cast exception.

Can you help?

Andrej Baranovskij said...

Hi,

May be my older blog post will be useful - Multilanguage in fixed af:selectOneChoice ADF Faces component.

If no, you can send me your app with this problematic functionality, I will try to look into it.

Regards,
Andrejus

Anonymous said...

Thanks Andrejus for your kind help. I sent you a small zip file with the app workspace. Thx!

Corneliu said...

does execwithparams work with MySql database? Because I try to use this with MySql and I can't add bind variable.
Regards
Corneliu

Andrej Baranovskij said...

Never tested with MySQL...

Andrejus

Jaya Sharma said...

Hi,
I am trying to implement the same thing. pass a user provided value to the bind variable of the VO, which is bound to the ui page.

I added the executeWithParams action in pageDef. For now passing the hardcoded value. bind variable is the mandatory variable of the query.

But the value is not getting through. Hence UI is displaying empty page. Please help

Andrej Baranovskij said...

If you are using ADF 11g, make sure to add Method Call activity into ADF task flow to invoke ExecuteWithParams, or invoke it from button directly in the page.

Also enable detail ADF log and check if bind variable value was set.

Andrejus