Using an varray type in a select statement

Question

I am trying to use an varray-type in a select statement:

CREATE OR REPLACE PROCEDURE ARRAYTEST IS
  type array_t is varray(2) of int;
  array_test array_t := array_t(10,11);
BEGIN
  select * from STATISTIK where abschluss1 in array_test;
END;

But it is giving me an error:

PLS-00428: an INTO clause is expected in this SELECT statement
PLS-00642: local collection types not allowed in SQL statement

The first Exception seems to be misleading, I don't want to select something into a variable I want an aquivalent of:

select * from STATISTIK where abschluss1 in (10,12);

But (10,12) substituted by an array (varray).

Is it possible to convert the varray to be used in a select-statement?

Answer 1

It is possible but your type must be global

create type array_t is varray(2) of int;

Then use array as a table (open p for only for compiling)

 declare
    array_test array_t := array_t(10,11);
p sys_refcursor;
    begin
open p for
       select * from STATISTIK where abschluss1 in (select column_value from table(array_test ));
    end;