LISTAGG query in Oracle split in batches

Question

I want to get a list of tables group by Schema and split the list of tables in batches, in Oracle Database (19.3).

Example: I have 10 tables (A,B,C,D,E,F,G,H,I,J) which belong to HR schema. with the query that I have I get this output Select Schema, LISTAGG(Table_Name) FROM SchemaInfo GROUP BY Schema

Schema	Tables
HR	A,B,C,D,E,F,G,H,I,J

I would like split the group in batches of 5 elements and get an ouptput like this:

Schema	Tables
HR	A,B,C,D,E
HR	F,G,H,I,J

Are there any way to get this output? I have something similar for SQL but in Oracle is not working the same approach.

Answer 1

There's probably an easier way, but here's an example I did with all_tables:

select owner, listagg(table_name, ',') 
from (
    select owner, table_name, floor(row_number() over (partition by owner order by table_name)/5) as batch
    from all_tables)
group by owner, batch
order by owner, batch

With your table, it would look like:

select Schema, listagg(table_name, ',') as Tables
from (
    select Schema, table_name, floor(row_number() over (partition by Schema order by table_name)/5) as batch
    from SchemaInfo)
group by Schema, batch
order by Schema, batch