0

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?

noircc
  • 650
  • 10
  • 28
  • 1
    possible duplicate of [Assign a Select to a variable in a stored procedure](http://stackoverflow.com/questions/16260068/assign-a-select-to-a-variable-in-a-stored-procedure) – Ben Jul 29 '15 at 12:25
  • tl;dr you need to put the result of the select statement somewhere. – Ben Jul 29 '15 at 12:25
  • I don't think that this is a duplicatie the mentioned question, because I don't want to select anything into an variable (see edits plz, but thanks for your answer anyway :) ) – noircc Jul 29 '15 at 14:35

1 Answers1

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;
ksa
  • 403
  • 1
  • 5
  • 15