Using bind variable in 'IN' clause

Question

I want to query a list of numbers into a plsql variable and use that in a in clause in another sql query. I created a testcase below of what I want to do.

I did google for the solution and I think it must be possible somehow but I just don't get it to run. Please help me out with a compiling 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;

Maybe this is a simplified demonstration, but in the case you give would you not just combine the two queries into one? "Select ... from ... where user_id in (select user_id from ...)"? Or perform a join? — David Aldridge, Nov 29 '12 at 14:08
yes this is dumbed down and may not make sense. I need the example to compile - I get an error on the line: where user_id in ( v_ids ) — opi, Nov 29 '12 at 14:14
is using an sql array vs the plsql nested table not an option for you? if it is, we can use the TABLE() function to solve the issue (though your last statement doesn't make sense as its a select into, so you're ending up with one row? fine if your real sql is select max() or rownum = 1 or something). — DazzaL, Nov 29 '12 at 14:29
slq array: I declare 'type t_id is varray(1000) of number;' and use 'where user_id in ( table( v_ids ) );' but this don't compile either — opi, Nov 29 '12 at 14:38
@user1863377 i mean, `create type t_id as table of number; ` instead and not have it in your PLSQL block. Is that acceptable? — DazzaL, Nov 29 '12 at 14:40
@user1863377 please also explain the purpose of the final sql `select user_id into v_user_ids` as with > 1 row return this will fail as written even if the v_ids worked. what are you trying to achieve overall here? are you trying to get 1 row with "1,3,10,16" in the variable? — DazzaL, Nov 29 '12 at 14:49
I need to understand how to technically use a array with the in clause. My real use case is more complex. if I `create type t_id ..` I get the error: `expected NUMBER got t_id` — opi, Nov 29 '12 at 15:06

score 4 · Accepted Answer · answered Nov 29 '12 at 15:03

4

using a 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 on how many elements are in your array.

note: using an unbounded bulk collect like you have:

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

is generally not great if your array can end up with many thousand or more rows, as you're putting too much stress on the memory and will eventually crash the code. You'd be better served with an explicit cursor open x for .. and a bulk fetch in a loop with the limit clause ie fetch x bulk collect into v_ids limit 100 and process in batches of say 100-1000.

answered Nov 29 '12 at 15:03

DazzaL

21,638
3
49
57

great this works the inner select does the trick! Can I avoid the `create type ...`? is it possible to reuse a type already provided by oracle? - the real code does go through 100 of millions of records in batches of 1000. I am a java guy and need to do this in plsql for performance reasons. – opi Nov 29 '12 at 15:23
well you could check to see whats available in your database `select owner, type_name, coll_type, upper_bound, precision, scale from all_coll_types where elem_type_name = 'NUMBER';` and pick one (eg `v_ids KU$_OBJNUMSET;`). just remember that by using an Oracle owned (SYS) one its subject to disappear or change on a future release of Oracle :) – DazzaL Nov 29 '12 at 15:40
"Batches of 1,000" and "performance" sound mutually exclusive to me. If you really have a very large number of records then consider inserting the values that you need to pass to the code into a Global Temporary Table and joining to it, using as few SQL statements as possible. – David Aldridge Nov 29 '12 at 15:43
@DavidAldridge true if you can do it as 1 sql then better, but if not then bulk collect with a limit is better than looping and safer than without a limit. – DazzaL Nov 29 '12 at 16:04

Using bind variable in 'IN' clause

1 Answers1