Is there any analogue of ARRAY_AGG PostgreSQL function in Oracle?

Question

How in can I return an array in Oracle similar to PostgreSQL's ARRAY_AGG function?

SELECT
    ARRAY_AGG(EMAIL)
FROM
    EMPLOYEES
WHERE
    EMAIL IS NOT NULL
AND
    ORGANIZATION_ID IN (27623,27734)

Duplicate of [Equivalent of PostgreSQL's array\_agg in Oracle XE 11.2](https://stackoverflow.com/questions/47856440/equivalent-of-postgresqls-array-agg-in-oracle-xe-11-2) — Kaushik Nayak, Apr 01 '19 at 07:01
Well, if I use `LISTAGG` function which is recommended in this post it raise error: `ORA-01489: result of string concatenation is too long`. Do you have any other ideas? — Nurzhan Nogerbek, Apr 01 '19 at 08:33
In Google I found recommendation to use `XMLAGG` function but I don't know it is best practice in that case. I use `RTRIM(XMLAGG(XMLELEMENT(e, EMAIL, ',').EXTRACT('//TEXT()')).GetClobVal(), ',') EMAILS` and it works. — Nurzhan Nogerbek, Apr 01 '19 at 08:48
You have 2 options, either completely rethink the way you need to store and use such large data, i.e. avoiding concatenation altogether. If it isn't possible then go with the alternative ( like xmlagg, user defined functions using clob ,etc) — Kaushik Nayak, Apr 01 '19 at 08:52

score 1 · Accepted Answer · answered Apr 01 '19 at 07:01

1

use LISTAGG()

SELECT
    LISTAGG(EMAIL) WITHIN GROUP (ORDER BY EMAIL) as email
FROM
    EMPLOYEES
WHERE
    EMAIL IS NOT NULL
AND
    ORGANIZATION_ID IN (27623,27734)

answered Apr 01 '19 at 07:01

Fahmi

37,315
5
22
31

Hello! I just add this part to your code `LISTAGG(EMAIL, ',')`. It works if result is not huge. If result if huge I have such error: `ORA-01489: result of string concatenation is too long`. Do you have any ideas about this? – Nurzhan Nogerbek Apr 01 '19 at 08:24
@NurzhanNogerbek, you can LISTAGG(Desc, ' ' ON OVERFLOW TRUNCATE ) but not sure whether it will solve your problem or not – Fahmi Apr 01 '19 at 09:07
1

Finally, I decided to use `XMLAGG` function like that `RTRIM(XMLAGG(XMLELEMENT(e, EMAIL, ',').EXTRACT('//TEXT()')).GetClobVal(), ',') EMAILS`. In my case it return list of emails separated by comma for huge list. For example I have such result `[email protected],[email protected]`. Is it possible to return such result `'[email protected]','[email protected]'`. I want to add `'` symbol at start and end of each email. Do you have any ideas about that? – Nurzhan Nogerbek Apr 01 '19 at 10:34
you can use concat() for that - after concating u passed it to XMLAGG function – Fahmi Apr 01 '19 at 10:35
In what part of my code I need to put `concat` function. I am little bit confused. – Nurzhan Nogerbek Apr 01 '19 at 10:39
@NurzhanNogerbek concat('''',email,'''') – Fahmi Apr 01 '19 at 10:41
I have such error `ORA-00909 invalid number of arguments concat` ;( – Nurzhan Nogerbek Apr 01 '19 at 10:52
1

@NurzhanNogerbek you can check this - https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=8c6251ccae8ec1b5886fedf3ed1b20d9 – Fahmi Apr 01 '19 at 10:55
Thank you! I tested your code. It return me strange result: `'[email protected]','[email protected]'`. I don't know why instead of `'` symbol it return me `'`. Do you have any ideas about that? – Nurzhan Nogerbek Apr 01 '19 at 11:00
@NurzhanNogerbek, no actually I don't have any idea - but if I found anything I'll let u know – Fahmi Apr 01 '19 at 11:03
1

I found solution. You need to use `utl_i18n.unescape_reference(***)`. – Nurzhan Nogerbek Apr 01 '19 at 11:07

Is there any analogue of ARRAY_AGG PostgreSQL function in Oracle?

1 Answers1