Using the Bind Variable in the "IN" Section

Question

I want to query the list of numbers in the plsql variable and use this in the in clause in another sql query. I created a test file below what I want to do.

I made google for the solution, and I think it should be possible, but I just can't run it. Please help me with a compilation solution.

CREATE OR REPLACE PROCEDURE PROCEDURE1 as type t_id is table of number; v_ids t_id; v_user_ids number; BEGIN -- fill variable v_id with id's, user_id is of type number select user_id bulk collect into v_ids from user_users; -- then at a later stage ... issue a query using v_id in the in clause select user_id into v_user_ids from user_users -- this line does not compile ( local collection type not allowed in SQL statements) where user_id in ( v_ids ); END PROCEDURE1; 

Answer 1

using SQL type:

 SQL> create type t_id is table of number; 2 / Type created. SQL> CREATE OR REPLACE PROCEDURE PROCEDURE1 2 as 3 v_ids t_id; 4 v_user_ids number; 5 BEGIN 6 7 -- fill variable v_id with id's, user_id is of type number 8 select user_id 9 bulk collect into v_ids 10 from user_users 11 where user_id between 100 and 120; 12 13 select user_id into v_user_ids 14 from user_users 15 where user_id in (select /*+ cardinality(t, 10) */ t.column_value from table(v_ids) t) 16 and rownum = 1; 17 18 dbms_output.put_line(v_user_ids); 19 20 END PROCEDURE1; 21 / Procedure created. SQL> exec procedure1 100 

where cardinality(t, 10) should be a reasonable guess about how many elements are in your array.

Note: using unlimited volume collection, like you:

  8 select user_id 9 bulk collect into v_ids 10 from user_users; 

as a rule, it is small if your array can have many thousands or more lines, as you press too hard on the memory and eventually dump the code. You would be better off working with an explicit open x for .. cursor and bulk sampling in a loop with a limit clause, i.e. fetch x bulk collect into v_ids limit 100 , and process in batches, for example, 100-1000.