ORACLE VARRAY Select output

Question

I am having a problem with VARRAY in ORACLE:

When I try to select a varray type in a table, the output in SQL Developer shows

VARRAY_TYPE
__________________________________________________
USERNAME.VARRYA_TYPE('TYPE1','TYPE2','TYPE3')

Can anyone point me out the way to get rid of the "USERNAME.VARRAY_TYPE()" and extract the result to be just

VARRAY_TYPE
__________________________________________________
'TYPE1','TYPE2','TYPE3'

My varray and table example are given here for your references:

CREATE TYPE varray_TYPE AS VARRAY(3) OF CHAR(20)
/

CREATE TYPE table_TYPE AS OBJECT (

tableID         number (6),
varray_var      GENRE_TYPE  
)
/

CREATE TABLE table1 as table_type(
tableID PRIMARY KEY
)
/

AND MY SELECT QUERY IS:

SELECT VARRAY_VAR AS VARRY_TYPE 
FROM TABLE table1
/

---

THANKS IN ADVANCE!

Answer 1

Disaggregate the results with cross join and table(), then reaggregate them with listagg to add custom formatting.

select
    tableID,
    listagg(trim(column_value), ',')
        within group (order by column_value) varray_type
from table1
cross join table(table1.varray_var)
group by tableID

TABLEID   VARRAY_TYPE
-------   -----------
1         TYPE1,TYPE2,TYPE3

I had to make a few minor changes to your syntax to get this to work:

CREATE TYPE varray_TYPE AS VARRAY(3) OF CHAR(20)
/

CREATE TYPE table_TYPE AS OBJECT (
tableID         number (6),
varray_var      varray_TYPE  
)
/

CREATE TABLE table1 of table_type(
tableID PRIMARY KEY
)
/

insert into table1 values(1, varray_type('TYPE1','TYPE2','TYPE3'));

SELECT VARRAY_VAR AS VARRY_TYPE 
FROM table1;