Fix Rowset is Forward Only Error for ADF BC LOV Range Paging (11.1.2.1.0)

This post is based on latest production JDeveloper - 11g R2 (11.1.2.1.0). ADF BC applications typically are working with database, when SQL statements are complex and slow - its good to apply Range Paging for VO's in ADF BC. Range Paging enables ADF BC to prepare SQL statement to retrieve only predefined subset of rows from database - significantly improving data retrieval performance. LOV is one of the elements in ADF applications where we can expect lots of rows to be retrieved - means good candidate to apply Range Paging.

However, if you try to apply Range Paging for LOV - it will not work. Wait wait..., but there is a workaround I will describe today. Download sample application where Range Pagine for LOV is working - LOVRangePagingFix.zip.

First, how to define Range Paging for LOV? Open LOV VO (or alternatively you can define it directly through LOV View Accessor - no difference) and expand Tuning section. Change Access Mode property to Range Paging and set Range Size (in this example is set to 30, means ADF BC will prepare SQL select to query 30 rows only for current rowset):

Next define LOV, by specifying List Data Source, etc.:

Choose Input Text with List of Values as list type:

Drag and drop LOV on ADF UI and run application. You will get Rowset is forward only error:

I will tell you, how to fix this. Open VO, where LOV is defined - from structure window locate LOV definition in the source code:

Here is the trick, even we have defined our LOV to work with Range Paging, LOV wizard ignores this and sets default ListRangeSize = -1 (inside List Binding):

Its why it fails on runtime with Rowset is forward only error - LOV VO is enabled with Range Paging, but LOV binding itself gets configured by wizard to retrieve all rows (-1). In order to fix this, set ListRangeSize = 30 by yourself (same as Range Paging):

It works well now, form is rendered:

LOV is opening, Range Paging is applied:

Selected LOV value is retrieved and set: