In this post, we will see how to resolve Oracle: Paginating LISTAGG()
Question:
I am trying to pull a set of quoted UUIDs from a database, and am usingLISTAGG()
to do so, thus:ON OVERFLOW ERROR
). In these cases, I would like to iterate LISTAGG()
over batches of returned results. I feel like I am on the right track with something like this:OFFSET
until I run out of records. Ideally I would be able to in one shot get an aggregated list in batches of all of the returned records. Is this doable?(My access to this DB is limited; I cannot
CREATE
views, temporary tables, functions, or any of that fun stuff for Reasons).Best Answer:
UseGROUP BY CEIL(ROWNUM/99)
to “paginate” the UUIDs into groups of 99 rows and then aggregate those rows. Additional “pages” will be in the next group(s) so you will get multiple rows:If you have better answer, please add a comment about this, thank you!
Source: Stackoverflow.com