I've created a REST data source in my APEX 23.2 on my Always Free database. The data source has pagination (100 rows at a time). I've setup the pagination settings in the data source (pagination type “Page Size and Page Number”.)
I've created a test interactive report using this data source, and the IR shows correctly all 700+ rows returned by the REST source, so within an IR the use of the REST pagination works fine calling the REST source multiple times to retrieve all rows one page at a time.
But then I try to do the same with an API call.
I use apex_exec.open_rest_source_query to get a context for the same data source and ask it for a maximum of a 1000 rows, then loop over rows using apex_exec.next_row. This only gets me a single page (100 rows), rather than retrieving all pages like the IR does.
As I have setup the pagination in the REST data source definition itself, the page is not a parameter to the data source, so a manual loop over the pages doesn't seem possible.
Is there a way to make the API call behave the same way as when the IR is using the REST data source?