CODEWITHSUNDEEP
oracle-databaseselectprocedure
dotnetPiotr
dotnetPiotr

Reputation: 49

Oracle select in array

I want to write query to database in Oracle stored procedure. I send to procedure array

TYPE NUMBER_ARRAY IS TABLE OF NUMBER;

in my query i want to limit result like that:

SELECT *
    FROM TABLE T
   WHERE ID IN
         (SELECT * FROM TABLE(CAST(NUMBER_ARRAY AS TABLE)))

but it returns errors:

PLS-00642 ORA-22906

Upvotes: 0

Views: 1041

Answers (1)

Cs 8128
Cs 8128

Reputation: 113

create or replace type NUMBER_ARRAY as TABLE OF NUMBER;

declare 
numvar number_array := number_array(); 
numvar1 number_array; 
begin 
for i in 1..10 loop 
  numvar.extend(); 
  numvar(i) := i; 
end loop; 
SELECT * bulk collect into numvar1 FROM TABLE(numvar);   

for i in numvar1.first..numvar1.last loop 
  dbms_output.put_line(numvar1(i)); 
end loop; 
end;

Upvotes: 1

Related Questions