Skip to main content

Programmatically switching LOV queries at run time

In today's post, I'm Sharing a simple application which make use of ViewObjectImpl::prepareRowSetForQuery(ViewRowSetImpl vrs) on LOV source view object to alter the query at run time. LOV switcher is the ideal solution to switch LOVs at runtime. However there are odd scenarios where you may have N number of data sources for LOV which is known only at run time. This approach may work for you in such cases. [ The concept used in this post is inspired by a solution provided by Steve Muench in an internal discussion thread ]


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

A glance at the implementation

1. Run test.jspx
2. Data source for LOV defined on ManagerId changes based on the values keyed in for DepartmentId field. If you leave Department empty, data is queried from EMPLOYEES table. If DepartmentId present for the row data for LOV is queried from DEPARTMENTS table for DepartmentId. The query switching is done programmatically from the over ridden prepareRowSetForQuery(...) method. Note that ViewObject definition doesn't change in the implementation, query alone changes.

Please see ManagerViewObjImpl::prepareRowSetForQuery(ViewRowSetImpl vrs) to learn more about the query switching details.


Tiah said…
Thanks a lot. Very nice article.
Anonymous said…
I have a problem, when i try to execute select :a anio from dual union select :a+1 anio from dual, I have the following mistake Exception in thread "AWT-EventQueue-0" oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. I apreciate that you can help me. my email is
Stanislav said…
Hi, Jobinesh! Thanks for great post!
Can you suggest how to rewrrite your code, that list of values in UI was changed on event(For example, when I double click on LOV, list model1 must
invoke. When I right click(context menu) on LOV, list model2 must invoke)

Regards, Stanislav

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…