Try to use the listagg function. It is necessary to get the values ​​of the column without repetitions.

Is it possible without creating additional functions?

with data (col1, col2, creator) as (
  SELECT 1, 2, 'SMITH' FROM DUAL UNION ALL
  SELECT 1, 2, 'JOHN' FROM DUAL UNION ALL
  SELECT 1, 3, 'AJAY' From Dual Union All
  SELECT 1, 4, 'RAM' FROM DUAL UNION ALL
  SELECT 1, 5, 'JACK' FROM DUAL
  )
SELECT '[' || Listagg (Col2, ',') Within Group (Order by Col2) || ']' res
FROM DATA.

You need to get the column column and the list of Col2 Listagg function.
I get [2,2,3,4,5] , and the desired result [2,3,4,5] .

Answer 1, Authority 100%

select listagg (col, ',') within group (Order by Col)
from (
  SELECT DISTINCT COL2 COL
  FROM DATA.
) T.

If you need more columns, then something will be the solution:

select col1, listagg (col2, ',') within group (Order by Col2)
from (
 SELECT COL1, COL2, ROW_NUMBER () OVER (Partition by Col1, Col2 Order by col1) AS RN
 FROM DATA.
 Order by col1, col2
)
WHERE RN = 1
group by col1;

Source: @a_horse_with_no_name

Answer 2, Authority 100%

In the current version of Oracle 19C in ListAgg Added option Distinct .

Distinct option for listagg aggregate

The Listagg Aggregate Function Now Supports Duplicate Elimination by Using the New Distinct Keyword.

select col1, listagg (distinct col2, ') within group (Order by Col2) Res
FROM DATA.
group by col1;

give the desired result:

col1 res
---------- ----------
     1 2,3,4,5