JDeveloper: Preventing return of large row sets on page load of VO using bind variable

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.

adfc-config.xml

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’.

Bounded Task Flow 'show-emp-bft'

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.

Start Activity in Task Flow 'show-emp-btf'

As a result the next page only shows an empty table.

Show Employees after entering the page

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’

ExecuteWithParams bind variable set to 1

As the result I get the desired result

Result after ExecuteWithParams with bind variable set to 1

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 11.1.2.1.0 and depending on the HR db schema, from here: BlogExecWithParams_V2.zip.doc
Please rename the file to ‘.zip’ after downloading it!