Initializing the bind variables used for an LOV query

This post is continuation of one of my previous post on LOV run time. Today, I'm sharing another approach for initializing the bind variable used in an LOV query.

This example has an LOV defined for DepartmentId attribute of Employee View Object.


LOV query comes from Department View Object, and the 'where' clause has a bind variable defined as shown below.

SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
WHERE ( Departments.LOCATION_ID = :bindVarLocId OR :bindVarLocId IS NULL )

How do you set bind variable value in the above case (when used in a LOV query)?

One possibility is to let the bind variable to read the value from User Session Data. In this case, you can supply the bind variable value at run time by putting in to ApplicationModule's User Session Data HashMap as shown in the following code snippet. You may need to expose this method (defined on AM) to the client as well.

public void setLocationIDSessionData(Number locId){
this.getSession().getUserData().put("LocationId", locId);
}

How does the query get value from User Session Data?

ADF BC run time can access User Session values using groovy expressions. You may need to use the bind variable editor to associate the groovy expression with bind variable as shown in the screen shot.


When do you need to set 'User Session Data' with value for the bind variable ?

You may need to do it before the LOV query execution. If you use ChoiceList ListOfValues, then you may need to do it through a method activity before the view activity.
If you are using LOV component displayed as a pop up window, you can even rely on launchPopupListener of ListOfValues component.

You can download the sample workspace from here.
[Runs with Oracle JDeveloper 11g R1 PS3 + HR Schema]

How to run this sample?

1. Run lovInForm.jspx. Click on the LOV displayed for DepartamentId in the parameter form. You may notice that list is restricted to display only those records with LocationId=1700

2. Run lovInQuery.jspx. Click on the LOV displayed for DepartamentId in the search panel. As in the above case, list of values is restricted to display only those records with LocationId=1700

Comments

ram said…
Excellent post. This suited one of our requirements perfectly. Thanks
Siva said…
Very useful post as always.
swath said…
Hi Jobinesh.. my requirement of using the Bind variables is with Essbase Datacontrol. in this case I could not get the view object to follow this process..could you help me to assign the value to bind variable using Essbase Datacontrol.
-Swathi
Jobinesh said…
Sorry, I'm not familiar with Essbase DC
Anonymous said…
Hello,

Thank you for your post. It is very helpful.
I have a question. How did you make the method setLocationIdSessionData binding in your page testPageDefinition.xml and how did you create the xml page?. I am doing the same thing and I get a null pointer because I haven't found the place to bind the sessionData Method. I placed it in a button to make sure that was causing the null pointer and it runs with no data but no null pointer. So I clicked the extra button before I click the list of avlues and it runs but my binding variable is blank.

Thanks
Anonymous said…
Hello,

Thank you for your post. It is very helpful.
I have a question. How did you make the method setLocationIdSessionData binding in your page testPageDefinition.xml and how did you create the xml page?. I am doing the same thing and I get a null pointer because I haven't found the place to bind the sessionData Method. I placed it in a button to make sure that was causing the null pointer and it runs with no data but no null pointer. So I clicked the extra button before I click the list of avlues and it runs but my binding variable is blank.

Thanks
Jobinesh said…
1. Define a method in AM
2. Expose it to client
3. Drag the exposed method from data control panel to page
Anonymous said…
I did that. It asked me to drop it as a button or link or ADF Parameter Form. so last time I dropped as a button and I click on it before clicking on the query, but I am sure you didn't in a different fashion.
How did you droped it as?

thank you so mcuh!
Don Kleppinger said…
The bind variable can be set with a groovy expression in the view Accessor for the LOV. Select the View Accessors Tab and edit the view accessor for the LOV in question. There you can set the bind value for that LOV. The LOV might need to get the bind variable from a different place depending on where it is used so setting it in the view accessor is better than setting in the LOV view object unless the LOV view object always needs to gets the bind variable from the same place

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of my employer.