What analog for separator in Oracle?

Question

I have an SQL-request, working correct in H2 DB:

update task_display td
set comments = (
    select group_concat(co.text separator '\n\n')
    from comments co
             left join ticket ti on co.ticket_id = ti.id
    where td.ticket_id = ti.id
    );

As we see, I use separator to separate text. What analog of this query for Oracle database?

UPD: While using script

update task_display td
    set comments = (select listagg(co.text, '\n\n') within group (order by co.id)
                    from comments co
                    where td.ticket_id = co.ticket_id
                   );

I reseive this error:

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

Answer 1

Assuming your strings are big enough to store the values:

update task_display td
    set comments = (select listagg(co.text, '\n\n') within group (order by co.id)
                    from comments co
                    where td.ticket_id = co.ticket_id
                   );

I don't see a need to join to ticket in either database.

The order by co.id is because listagg() requires an ordering. I am guessing there is a column with that name, but any column can be used.