Oracle select in array

Question

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

"but it not work" - that's not a helpful description...

Mitch Wheat
– Mitch Wheat

2014-03-21 09:06:19 +00:00
Commented Mar 21, 2014 at 9:06 — Mitch Wheat
– Mitch Wheat, Commented Mar 21, 2014 at 9:06

Cs 8128 · Accepted Answer · 2014-03-21 10:14:52Z

1

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;

answered Mar 21, 2014 at 10:14

Cs 8128

1139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle select in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related