How to convert my email ids into horizontal comma separated

yyy62103 0
Viewed 14

I have written a query where I am passing multiple values from my front end via POP LOV (Oracle APEX 20.x)

select column_value as val from table(apex_split(:MYIDS));

It will be like this from above query

select column_value as val from table('3456,89000,8976,5678');

My Main query :

SELECT email
FROM   student_details
WHERE  studid IN (SELECT column_value AS val
                  FROM   TABLE(apex_split(:MYIDS));

My main query gives me below details as an output

xyz@gmail.com
vbh@gmail.com
ghj@gmail.com
uj1@gmail.com
xy2@gmail.com
vb4@gmail.com
g2j@gmail.com
u8i@gmail.com
x3z@gmail.com
v9h@gmail.com
g00j@gmail.com
uj01@gmail.com

But I want this above output as comma seperated in one line like below

xyz@gmail.com,vbh@gmail.com,ghj@gmail.com,uj1@gmail.com,xy2@gmail.com,vb4@gmail.com,g2j@gmail.com,u8i@gmail.com,x3z@gmail.com,v9h@gmail.com,g00j@gmail.com,uj01@gmail.com

I want it by using xmlelement cast method as listagg as some 4000 char length issue

apex-code oracle-sqldeveloper oracle-apex plsqldeveloper oracle
1 Answers

Slightly adjust your "main query":

SELECT listagg(email, ',') within group (order by null) 
FROM   student_details
WHERE  studid IN (SELECT column_value AS val
                  FROM   TABLE(apex_split(:MYIDS));

If you hit listagg's 4000-character in length restriction, switch to xmlagg:

with your_main_query as
  (SELECT email 
   FROM   student_details
   WHERE  studid IN (SELECT column_value AS val
                     FROM   TABLE(apex_split(:MYIDS))
  )
SELECT RTRIM (
            XMLAGG (XMLELEMENT (e, email || ',') ORDER BY NULL).EXTRACT (
               '//text()'),
            ',') AS result
    FROM your_main_query;
Littlefoot0
Related