SQL> create type myarray is VARRAY(5) of varchar2(10); 
  2  /

Type created.

SQL> create table mytable  (c1 number, c2 myarray);

Table created.

SQL> insert into mytable values (1, myarray('magvivek','haddock','pgwoodhous')) ;

1 row created.

SQL> column c2 format a50
SQL> select * from mytable;

        C1 C2
---------- --------------------------------------------------
         1 MYARRAY('magvivek', 'haddock', 'pgwoodhous')

SQL> create type mytype_table is table of varchar2(10); 
  2  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  select column_value
  2* from THE (select CAST(c2 AS mytype_table) from   mytable)
  3  /

COLUMN_VAL
----------
magvivek
haddock
pgwoodhous

SQL> insert into mytable values (2, myarray('ss','aa','xx')) ;

1 row created.

SQL> commit;

Commit complete.

SQL> ed
Wrote file afiedt.buf

  1  select column_value
  2* from THE (select CAST(c2 AS mytype_table) from   mytable)
SQL> /
from THE (select CAST(c2 AS mytype_table) from   mytable)
          *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

SQL> ed
Wrote file afiedt.buf

  1  select column_value
  2* from THE (select CAST(c2 AS mytype_table) from   mytable where c1=2)
SQL> /

COLUMN_VAL
----------
ss
aa
xx