Adding ARRAYs to table
Sometimes when you don't query for your data, it can be easier and more appropriate to use a 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, have a look at "varchar_array":
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.