Oracle format String output from Aggregate Collect function

Question

Below is my working Oracle query-

-- User-Defined TYPE
create or replace TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

-- This works
select emp.DEPT_NAME,
        CAST(
            COLLECT(emp.EMP_ID || ':' || emp.EMP_JOIN_DATE) 
        AS varchar2_ntt) AS EMPS
from  employee emp 
    group by  emp.DEPT_NAME;
    
-- Output
DEPT_NAME   EMPS

SALES       TEST_DB.VARCHAR2_NTT('750127:20-JAN-23', '750228:20-JAN-23')
FINANCE     TEST_DB.VARCHAR2_NTT('548834:10-JAN-19', '802850:14-MAR-23', '802849:19-OCT-23')

This output I want to format as below

Output- TEST_DB.VARCHAR2_NTT('548834:10-JAN-19', '802850:14-MAR-23', '802849:19-OCT-23')

After Formatting- 548834:10-JAN-19,802850:14-MAR-23,802849:19-OCT-23

How do I do it? I tried using regexp_substr, but without much success.

Answer 1

The varchar2_ntt is a collection data type containing many strings; it is NOT a string data-type, it is an array, and it does not have a specific format.

When you use the query and it displays:

TEST_DB.VARCHAR2_NTT('548834:10-JAN-19', '802850:14-MAR-23', '802849:19-OCT-23')

That is the client application that you are using (i.e. SQL Developer, TOAD, Java, etc.) displaying the collection data-type in a manner it thinks will be understandable to you, the user. It is not any formatting that is controlled by the SQL statement.

If you want to change how it is displayed then you either:

1. Need to change the preferences on the client application to change how it displays collections (however, most client applications do not have a setting that allows you to control specific formatting of the collection so you are unlikely to be successful using this). Refer to the documentation for your client application.

2. Do not use a collection and use LISTAGG, which does return a string and you can control the formatting within the SQL statement:

   SELECT dept_name,
          LISTAGG(emp_id|| ':' || emp_join_date, ',')
            WITHIN GROUP (ORDER BY emp_id, emp_join_date) AS emps
   FROM   employee
   GROUP BY dept_name;