Skip to main content

Globally setting the Row Fetch Limit for all ViewObjects

There is an interesting feature available with 11gR1PS3( release which may help you to set a default 'row fetch limit' for all ViewObjects at application level. This is very useful to avoid expensive table scan if you don't have maxfetchsize defined for individual ViewObjects. Apparently, this 'global setting' fits well for those ViewObjects where each query execution may result in large number of records. Please note that, even the 'row count' query issued by the framework may also result in performance issues if the table is having huge chunks of data.

How do you set the 'Row Fetch Limit' globally?

This value can be configured using 'rowLimit' under <adf-adfm-config> section of <adf-config> file from your application.

<adf-adfm-config xmlns="">
<defaults useBindVarsForViewCriteriaLiterals="true" rowLimit="10000"/>

If you would like to edit this configuration visually, then please use the <adf-config> editor to key in value for 'Row Fetch Limit', as shown in the following screen shot.

How does 'Row Fetch Limit' help to limit the fetch size at runtime?

From the UI perspective, when the user scrolls down beyond the 'row fetch limit' the run time would generate oracle.jbo.RowLimitExceededWarning and the same would be displayed as a message dialog to the user. The same exception is thrown if you try to access the row(s) programmatically as well, exceeding the fetch size.

oracle.jbo.RowLimitExceededWarning: JBO-25089: View object AppModule.EmployeesView1 attempted to fetch rows beyond the row limit
  at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(
  at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(
  at oracle.jbo.server.QueryCollection.populateRow(

How do you override the global Row Fetch Limit for a specific ViewObject?

It is always possible to override the global row fetch limit for specific ViewObject(s) based on your use cases. There are two possible approaches,

1. Specify maxfetchsize for the ViewObject. You can see this option under ViewObject's tuning section in the editor window.

2. Override ViewObjectImpl::getRowLimit() from your ViewOblectImpl to return -1.

* A limit may be applied on the number of rows in a query collection. When
* a new row is requested from the database and the limit is exceeded a
* warning is raised. By default the limit is configured as an application
* wide setting in adf-config.xml.
* @return a limit on the number of rows in a query collection. If no row limit is enforced
protected long getRowLimit()
  return -1;


Anonymous said…
We get this issue in the UI where RowCountThreshold is not set in an iterator binding. Should this be set to -1 instead of overriding getRowLimit()?
Jobinesh said…
getRowLimit() for a specifc VO overrides the the global setting on the 'max fetch limit'.
Reg 'RowCountThreshold' the value determines whether to execute count query based on the values.Copying the excerpts from from Dev guide...
Specify a value to determine if a result set returns the number of rows you specify as a value. If you set RowCountThreshold to 0, the iterator returns the estimated row count in the result set by executing the count query. If you set RowCountThreshold to less than 0, the iterator does not execute the count query.

Set RowCountThreshold to a value greater than 0 if you want the iterator to execute the count query with the maximum value equal to the value you specify for RowCountThreshold. If the estimated row count is less than the value of RowCountThreshold, return the number of rows in the estimated row count. If the estimated row count is greater than the value of RowCountThreshold, return -1.
S V Narayanan said…
Is there a way to know the value of the max fetch size programatically?

I mean, is there a method like getMaxFetchSize() that returns the value set in the adf-config.xml?
Jobinesh said…
Check this out -
Deepan said…
This comment has been removed by the author.
Anonymous said…
I could not understand to the exact point.

Consider in the pagedef we defined like

Iterator Binds ="PVO" RangeSize="25" DataControl="QAMDataControl" id="SIterator" RowCountThreshold="300"

In VO given these values for the properties :

Query Optimizer hint : FIRST_ROWS(10)
Access Mode: Scrollable
Range Size: 1
Fetch Mode: Fetch as needed

Suppose if i override the getRowLimit() in my VO, too avoid row limit error.And in the query am getting 600 rows and in the rowcountthreshold i have given 300 ..What will happen exactly..could you please clarify this?

Popular posts from this blog

How to set Bind Variable Values at runtime ?

In this post I'm sharing a couple of approaches for programmatically setting bind variables values at run time. This post is an attempt to explain 'When to use what ?'[ In case if you are familiar with 'Bind Variables' in ADF BC, please refer Section 5.10, Working with Bind Variables in Fusion Developer's Guide ]

1. Set the Bind Variable value using RowSet::setNamedWhereClauseParam(...)

You can use use the setNamedWhereClauseParam(...) method on the ViewObject interface (which extends oracle.jbo.RowSet) to set the value for bind variables. Please note this sets the value on default RowSet. In other words, this doesn't have any effect on the secondary RowSets that you/system generates.
ViewObject vo = am.findViewObject("EmployeesView1"); vo.setNamedWhereClauseParam("bindVarDeptId", new Number(10)); vo.executeQuery();
2. Set the Bind Variable value using ViewObject's VariableValueManager::setVariableValue(...)

VariableValueManager Ma…