1

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)
Nurzhan Nogerbek
  • 4,806
  • 16
  • 87
  • 193
  • 1
    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
  • Google the error : you will get several other alternatives – Kaushik Nayak Apr 01 '19 at 08:46
  • 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

1 Answers1

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)
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 `NNogerbek@gmail.com,BKim@gmail.com`. Is it possible to return such result `'NNogerbek@gmail.com','BKim@gmail.com'`. 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: `'NNogerbek@gmail.com','BKim@gmail.com'`. 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