How to convert my email ids into horizontal comma separated-CodePudding

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

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

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

[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]

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

CodePudding user response：

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;