Back in 2009 Andrejus Baranovskis blogged about how to prevent the execution of a (default) query when ADF loads a page here. This is sometimes necessary if the query defined for a page consumes a lot of time or return a lot of rows.
Lately some users reporting that the solution provided in the blog mentioned does not work. I have to confess, that I did not try out the method, so I can’t really comment on that.
In this blog I show a different way to archive this. The idea I implement is to add a part to the where clause of the VO which, when executed, return no rows. So I don’t prevent the execution but ensure that no row is returned.
To archive this I add the following where clause:
1 = 0
Now, if I add this as is, the query never will return any row. Instead I use a bind variable
1 = :bindDummy
This allows to use the bind variable to enable the query or in fact disable it. The full query defined in the VO looks like
SELECT Employees.ACTION_COMMENT, Employees.COMMISSION_PCT, Employees.DEPARTMENT_ID, Employees.EMAIL, Employees.EMPLOYEE_ID, Employees.FIRST_NAME, Employees.HIRE_DATE, Employees.JOB_ID, Employees.LAST_NAME, Employees.MANAGER_ID, Employees.PHONE_NUMBER, Employees.SALARY FROM HR.EMPLOYEES Employees WHERE 1=:bindDummy
The next challenge is to control the bind variable from the UI. Here ADF Task Flows comes to help. A bounded task flow has a start activity which is executed whenever the bounded task flow is started. I use this start activity to set the bind variable to ‘0’ to prevent the return of any row. Then, on the page I have a button which sets the bind variable to ‘1’ and execute the query. This time I get the desired result.
I use the EWPTest ViewId to start the sample. On this page I add a button ‘Show Emplyoees’ which navigates to the bounded task flow ‘show-emp-bft’. In this task flow the start activity is ‘ExecuteWithParams’ which sets the bind variable to ‘0’.
Here is the page def file for the start activity. As you see this method call activity call ‘ExecuteWithParams’ and sets the bind variable to ‘0’ thus preventing the return of any row from the query.
As a result the next page only shows an empty table.
In the toolbar I placed a button ‘Execute with bindDummy set to 1′ which calls the executeWithParams’ method, this time with the bind variable set to ‘1’
As the result I get the desired result
The ‘Back’ button in the toolbar return from the task flow to the first page. If I hit the ‘Show employees’ again the bind variable is set to ‘0’ again and the query again does not return any row.
You can download the sample workspace, build with JDev 184.108.40.206.0 and depending on the HR db schema, from here: BlogExecWithParams_V2.zip.doc
Please rename the file to ‘.zip’ after downloading it!