PL/SQL Collections as columns in oracle database

Intruduction

This time I will try to show, how you can put collection in database columns in relational table. This can be useful when you want to persist collection data, or you don't want to create additional detail table in you schema. Whatever is your reason, this can be quite useful.

Note that putting collection in table columns is nothing new for object databases (for instance for Google App Engine Bigtable).

Simple example

So in this example I will demonstrate use of collection (VARRAY in this case, but you can also use nested table - see this article about PL/SQL collections) as columns in a relational table.

First, I declare schema-level collection TYPE named varchar_collection_t that can contain up to four VARCHAR2 elements. Then I create a relational table, which will contain collection TYPE as columns (characters column). Finally I populate characters collection and INSERT that collection into the anime table.

CREATE TYPE varchar_collection_t IS VARRAY (4) OF VARCHAR2(100); 
/
CREATE TABLE animes (
    name VARCHAR2(255),
    characters varchar_collection_t
);

DECLARE
    anime_characters varchar_collection_t := varchar_collection_t();
BEGIN
    anime_characters.EXTEND(4);
    
    anime_characters(1) := 'Satsuki';
    anime_characters(2) := 'Mei';
    anime_characters(3) := 'Totoro';
    anime_characters(4) := 'Kanta';
    
    INSERT INTO animes 
            (name, characters)          
    VALUES  ('My Neighbor Totoro', anime_characters);    
            
END;

As you can see I use simple INSERT syntax for inserting collection in table. Simple as that!

Using VARRAY as columns datatype actually store collection data "inline" with the rest of the table's data (something like comma separated values), but using nested table it create behind the scene real database table. So Oracle says that VARRAY columns are intended for "small" arrays, and that nested tables for "large" data arrays.

One important note when storing collection in database is that you cannon directly manipulate with data stored in collection. You need to use CAST and TABLE syntax for this.
This in out of scope of this tutorial, I will just show how can you filter using data from "table" collection in plain SQL.

SELECT * FROM animes a
WHERE 'Mei' IN
       (SELECT * FROM TABLE(a.characters));

Please, note the use of TABLE syntax. Also I have only one row in table, but I think that is not relevant to understand the example.