How to change VO Query dynamically in OAF:

Dynamically Changing the VO query in OAF:

Generally while creating the View Objects we define a query to it. While rendering the OAF page these view objects get executed (either by framework or by the java code).

In some scenarios we might have a requirement to change the query of the VO, this query can be modified dynamically.

Example: Lets say a VO(EMPVO) has the query as
SELECT empno,ename,sal
FROM EMP

Now I want to change the query as below
SELECT empno,ename,sal+nvl(comm,0)
FROM EMP

Sample code:

String query = “SELECT empno,ename,sal+nvl(comm,0) FROM EMP”;
try
{
OAApplicationModule oam = oapagecontext.getApplicationModule(oawebbean);

// get the handle for your view object

EMPVOImpl voimpl = (EMPVOImpl)oam.findViewObject(“EMPVO”); 

voimpl.setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION);
voimpl.setQuery(query);

// setQuery only sets the new query to the View Object, in order to effect the changes of the query we need to execute the equery using below statement.

voimpl.executeQuery();
}
catch (Exception e)
{

}

Always need to call setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION) before executing the query, If you won’t call this OA Framework will not append the where clause and Order by clauses correctly.
The above code is written in controller, you can write the code in either processRequest method or in processFormRequest based on your requirement or in AM.

(Note: The number of columns and the order of the column data types must be same as the original query, so this code cann’t be used for adding new columns to the original query, for this we need to go for extension of VO)