Adding ARRAYs to table

Sometimes when you don't query for your data, it can be easier and more appropriate to use multi-value attributes instead of an additional table. Most SQL databases don't support this, but Oracle does.

Recipe #1 - Creating Array type and table with Array type

First, let's create a new type with 10 items of VARCHAR2(200) and a table using that type:

CREATE OR REPLACE TYPE varchar_array AS VARRAY(10) OF VARCHAR2(200);

And now the table:

CREATE TABLE varchar_array_tab (num NUMBER, list varchar_array);

When you insert some data, you'll have to use that type again to let Oracle know what you're trying to insert:

INSERT INTO varchar_array_tab VALUES (1, varchar_array('Hello', 'World'));

You can then query that table like any other table:

SELECT * FROM varchar_array_tab;

Both values we've inserted are shown in a single cell. Not that handy to work with? Let's cast that into a table:

SELECT num, column_value
FROM varchar_array_tab vat, TABLE(vat.list);

And now it's a table behaving like any other, but it's nested within the main table.