Categories
4.1 ApEx Bug Oracle Pagination

Oracle Application Express 4.1 pagination number of rows resets to 15 bug

So you create what appears to be a simple classic SQL report region. It contains a lot of rows so you specify a pretty high number in the “Number of Rows” value under “Report Attributes” “Layout and Pagination”. In this example, I set it to 100.

Layot and Pagination Number of Rows
Layot and Pagination Number of Rows

You run the report and all looks well… until you click “Next >” to get the next page!

ApEx report first page
ApEx report first page

All of a sudden, the number of rows dwindles back down to 15 and it stays that way! Imagine trawling through thousands of rows 15 at a time. How annoying!

Oracle ApEx report next page
Oracle ApEx report next page

Luckily for us, the patch fix is simple. All you have to do is:

1. Create a Hidden Item for the page where your report is.
2. Under source -> Source Used, set the Source Used to “Always, replacing any existing value in session state.
3. Under Source -> Source Type, leave the value set to “Static Assignment (value equals source attribute).
4. Under Source -> Source value or expression, set the number you would like for the number of rows. In this instance, I set it to 100.

New hidden page item source
New hidden page item source

5. Apply changes then go to your report’s Report Attributes – Layout and Pagination. Under Number of Rows (item), enter you hidden item name.

Oracle ApEx report attributes' Layout and Pagination Number of Rows (Item) value
Oracle ApEx report attributes’ Layout and Pagination Number of Rows (Item) value

6. Apply changes, and the problem should be fixed