How to use Replace/Substitution function in PL SQL

I’ve a doubt regarding replacing / substituting values in PLSQL. I’ve used listagg to segregate n number of values with each and every value being splitted by comma delimiter.

select listagg(column_name,',') with group (order by column_name)
Into new_variable
from table

For instance when executing the above query it returns 7digit alpha numeric values. For example

ABCD123,EFGH456,IJKL789

After storing the above values in a variable. When trying to replace the comma (,) with this (‘,’) using replace function.

Select replace(new_variable,',','','')
From dual;

It returns the error invalid number of arguments. Is there anyway other than replace function to replace (,) with (‘,’). Thanking you in advance.

Desired output: ABCD123′,’EFGH456′,’IJKL789

Answer

The escape is either a q' or 3 ' s If you wanted to do it in the listagg:

 select '('|| listagg(col,''',''') WITHIN  GROUP  (order by 1) ||')'

If you want the braces