I created a PL-SQL dynamic content report in Oracle Apex, however I am having trouble figuring out how to add pagination to it. I have too many rows and therefore adding pagination to the table will provide a better user experience. My sample code looks as follows:
BEGIN
htp.p('<table> <tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Email</th></tr>'); for i in(select * from exampleTable) loop htp.p('<tr> <td>'||i.id||'</td> <td>'||i.first_Name||'</td> <td>'||i.last_name||'</td> <td>'||i.email||'</td> </tr>'); end loop; htp.p('</table>');
END;
Advertisement
Answer
Create two hidden items on the page, say,
Pxx_START_ROW
andPxx_PAGE_SIZE
.Modify your query to be paginated. That will require that you sort the data somehow assuming you want the results to be deterministic. So rather than
select * from exampleTable
you’d have
select * from exampleTable e order by e.id offset :Pxx_START_ROW rows fetch next :Pxx_PAGE_SIZE rows only
- Then create a process that updates
Pxx_START_ROW
when you click some sort of a “previous” or “next” button. You’ll probably want to update your dynamic process to generate those buttons because I’d expect you’d want them to be part of the table though functionally you could create those buttons in a different region.