SELECT Values from Varray ORACLE SQL

Question

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.

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

Yes - see [`listagg()`](https://docs.oracle.com/database/121/SQLRF/functions101.htm). Also, use `varchar2` for strings, not `char` which 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). — William Robertson, Jan 04 '18 at 12:27
You are the hero. Thank you! Here is the solution just in case someone will need it: `SELECT PlaylistName, LISTAGG(column_value, ', ') WITHIN GROUP(ORDER BY column_value) as "Playlist" FROM Playlist, TABLE(Songs) GROUP BY PlaylistName` — Alex, Jan 04 '18 at 12:45

score 2 · Accepted Answer · answered Jan 04 '18 at 12:56

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().

SELECT Values from Varray ORACLE SQL

1 Answers1