SELECT Values ​​from Varray ORACLE SQL

Question

I am using apex.oracle and I am getting an error [unsupported data type]. Explanation: I have a table named Playlist, and I want to save an array of songs in the field Songs. For this reason, I defined a type called PlaylistSongsof varrayof chars. The insert works, but when I do a SELECT, I get [unsupported data type]instead of my array with values.

Here is the code

CREATE OR REPLACE TYPE PlaylistSongs AS VARRAY(4) OF CHAR(16);

CREATE TABLE PLAYLIST (
    IDPlaylist              NUMBER(4) NOT NULL,
    PlaylistName            CHAR(64),
    PlaylistAuthor          NUMBER(2),
    PlaylistDuration        NUMBER,
    ActivePlaylist          NUMBER(1),
    Songs                   PlaylistSongs,

    CONSTRAINT PLAYLIST_PRIMARY_KEY PRIMARY KEY (IDPlaylist),
    CONSTRAINT PLAYLIST_FOREIGN_KEY FOREIGN KEY (PlaylistAuthor) REFERENCES DJ (IDDJ)
);

INSERT INTO PLAYLIST VALUES (1, 'Rap', 1, 153, 1, 1, PlaylistSongs('River', 'Lose Yourself', 'Till I Collapse', 'Walk On Water')); 

After a little research, I found an operator TABLE.

SELECT *
FROM PLAYLIST p, TABLE(p.Songs) ps

This works, but instead of displaying the array as a regular array, it displays each record in a new line.

I need him to look like ['River', 'Lose Yourself', 'Till I Collapse', 'Walk On Water']. It can be done?

Answer 1

- . listagg() ( group by):

select idplaylist
     , playlistname
     , playlistauthor
     , playlistduration
     , activeplaylist
     , listagg(rtrim(ps.column_value), ', ') within group (order by ps.column_value) as songs
from   playlist p
       cross join table(p.songs) ps
group by
       idplaylist
     , playlistname
     , playlistauthor
     , playlistduration
     , activeplaylist;

, varchar2 , char, , ( - char ANSI . varchar2 rtrim().