Resolved: Oracle: Paginating LISTAGG()

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 using LISTAGG() to do so, thus:

SELECT
  COUNT(uuid) "Entries",
  '( ' || LSITAGG( DISTINCT '''' || uuid || '''', ', ' ON OVERFLOW ERROR ) || ' )' "UUIDs"
FROM
  table_with_uuids
/* 
WHERE
  criteria omitted for brevity
 */
;

This works perfectly well, but there are some use-cases where the number of UUIDs presented creates a string that is too long (hence the usage of 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:

WITH active_uuids AS (
  SELECT DISTINCT
    uuid
  FROM
    table_with_uuids
  -- [ WHERE clause omitted ]
  OFFSET 0 ROWS FETCH NEXT 99 ROWS ONLY -- Fetching 100 will overflow LISTAGG
)
SELECT
  count(uuid) "Entries",
  '( ' || LISTAGG( '''' || uuid || '''', ', ' ON OVERFLOW ERROR ) || ' )' "UUIDs"
FROM
  active_uuids
;

But I don’t want to have to manually increment the 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:

Use GROUP 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:

WITH active_uuids (uuid) AS (
  SELECT DISTINCT
         uuid
  FROM   table_with_uuids
  ORDER BY uuid             -- Optional
)
SELECT count(uuid) AS Entries,
       '( '
       || LISTAGG( '''' || uuid || '''', ', ' ) WITHIN GROUP (ORDER BY ROWNUM)
       || ' )' AS UUIDs
FROM   active_uuids
GROUP BY CEIL(ROWNUM/99);

fiddle

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Resolved: Oracle: Paginating LISTAGG()

Resolved: How to stop a thread that has a blocking function from easygui in python

Resolved: Removing null keys from a json array of objects

Resolved: How can I generate at compile-time a separate OpenAPI Swagger.json file for each Controller in my ASP.NET project?

Question:

Best Answer:

Related Posts

Resolved: How to stop a thread that has a blocking function from easygui in python

Resolved: Removing null keys from a json array of objects

Resolved: How can I generate at compile-time a separate OpenAPI Swagger.json file for each Controller in my ASP.NET project?