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

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'

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

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

ExecuteWithParams bind variable set to 1

As the result I get the desired result

Result after ExecuteWithParams with bind variable set to 1

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!

Advertisements

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

  1. Hi Timo,

    Please explain, what problem your users were facing with original solution? Same approach works on latest JDev 11g R2. Unless they have specific requirements.

    Regards,
    Andrejus

    • Andrejus,
      I’m not sure what the exact problem was at the moment. I need to dig it up which may need some time as I don’t have access to the bug tracker any more (changed jobs). I keep you posted…

      Timo

      • Sounds good. Let me know, if you will hear it. Generally executeEmptyRowSet method works, I guess they were experiencing some issues with Required fields or something similar.

        Andrejus

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s