Oracle - How to read from VARRAY

Question

I have a column in my table that contains a VARRAY of VARCHAR2, So I want to create a select-statement that gives me all the columns and all the objects of the VARRAY next to eachother, is there a possible way to do that?

Example:

CREATE TYPE arr AS VARRAY(5) OF VARCHAR2(10);

CREATE TABLE table1(
    v1 VARCHAR2(10)
    v2 VARCHAR2(20)
    v3 arr);

SELECT t.v1, t.v2, ??? FROM table1 t;

Thank You!

Answer 1

the one you wanted is this.!

SELECT t.v1, t.v2, nt.COLUMN_VALUE
FROM table1 t, TABLE(t.v3) nt

result

V1  V2  COLUMN_VALUE
a   b   c
a   b   d
a   b   e
f   g   h
f   g   i

Including t1.v3 also gives the The comma seprated values as well.

SQL Fiddle