Search code examples
sqloracle-databasevarray

SELECT Values from Varray ORACLE SQL

I am using apex.oracle and the error I get is [unsupported data type]. The explanation: I have a table named Playlist and I want to store an array of songs into the Songs field. For this reason I've defined a type named PlaylistSongs of varray of chars. The insertion works, but when I do the 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 bit of research, I've found the TABLE operator.

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

This works but instead of displaying the array as a regular array, it displays each entry on a new row.

enter image description here

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

Solution

  • Yes - see the listagg() aggregate function (goes with a group by clause):

    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;

    Also, use varchar2 for strings, not char which just applies blank-padding you don't need (actually, nobody needs it - char is for portability and ANSI completeness only and is not meant for use in new development. With a varchar2 array you won't need the rtrim().