SQL

Working with Arrays - VARRAY


Problem

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.


Solution

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;
num list
1 SCOTT.VARCHAR_ARRAY(Hello,World)

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);
id column_value
1 Hello
1 World

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

Comments




Please sign-in to post a comment