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
– David Aldridge, Commented Nov 29, 2012 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
– opi, Commented Nov 29, 2012 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
– DazzaL, Commented Nov 29, 2012 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
– opi, Commented Nov 29, 2012 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
– DazzaL, Commented Nov 29, 2012 at 14:40

DazzaL · Accepted Answer · 2012-11-29 15:03:33Z

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, 2012 at 15:03

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

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

4 Comments

opi Over a year ago

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.

DazzaL Over a year ago

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 :)

David Aldridge Over a year ago

"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.

DazzaL Over a year ago

@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.

Collectives™ on Stack Overflow

Using bind variable in 'IN' clause

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related