Re: Is it possible to use bind variables with an 'IN'

You could construct the SQL by concatenating the IN list dynamically, but then you would not be using bind variable. The answer seems to be dropping the dynamic SQL all together. In stead, convert the IN list to an array with a function, such as this one by Tom Kyte:

create or replace function str2tbl (
str_in in varchar2
) return typ_tab_v2100 as
str long := str_in || ',';
i pls_integer;
tab typ_tab_v2100 := typ_tab_v2100();
begin
loop
i := instr(str,',');
exit when nvl(i,0) = 0;
tab.extend;
tab(tab.count) := trim(substr(str,1,i-1)); str := substr(str,i+1);
end loop;
return tab;
end;
/

and then use the TABLE function to get what you need. For example:

SQL> select * from tyu;

C1 C2 C3 C4
-- --- ---------- ----------

G  XX           1         20
O  XX           1         20
Q  XX           1         20
a  XX           2         10
b  XX           2         10
c  XX           2         10
d  ZZ           1         40
e  ZZ           1         40
f  ZZ           1         40
g  YY           2         40
h  YY           2         30

C1 C2          C3         C4
-- --- ---------- ----------
p               4          4
w               5          5

13 rows selected.

SQL> declare
  2 x varchar2(100);
  3 begin
  4 x:='1,2';
  5 delete from tyu where c3 in (select t.column_value from table(cast(str2tbl(x) as typ_tab_v2100)
) t);
  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select * from tyu;

C1 C2 C3 C4
-- --- ---------- ----------

p               4          4
w               5          5

SQL> rollback;

Rollback complete.

SQL> declare
  2 x varchar2(100);
  3 begin
  4 x:='XX,YY';
  5 delete from tyu where c2 in (select t.column_value from table(cast(str2tbl(x) as typ_tab_v2100)
) t);
  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select * from tyu;

C1 C2 C3 C4
-- --- ---------- ----------

d  ZZ           1         40
e  ZZ           1         40
f  ZZ           1         40
p               4          4
w               5          5

SQL> rollback;

Rollback complete.

SQL>

• Jusung Yang

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0301270557.57a0b694_at_posting.google.com>...
> the first part is simply looping through a PL/SQL table and building a
> comma-delimited string of files I want to use in my 'IN' statement.
>
>
> FOR i IN l_resourceID.FIRST.. l_resourceID.LAST
> LOOP
> IF v_resourceidString IS NOT NULL
> THEN
> v_resourceidString := v_resourceidstring
> ||','||to_char(l_resourceID(i).RESOURCE_ID);
> (i).RESOURCE_SEQ);
> ELSE v_resourceidString := l_resourceID(i).RESOURCE_ID;
>
> END IF;
> -----------------------------------------------------------------
> Oracle cant parse what is below.
> EXECUTE IMMEDIATE 'DELETE FROM RD_ASSOC_CONTAINER_TEST'||
> 'WHERE RESOURCE_ID IN (:1)'
>
> using v_resourceidString;
>
> Oracle cant parse this properly. Is there a way to do this with bind
> variables? I have done this before when I had to get a list of column
> names to search and put them in the 'SELECT' clause without bind
> variables?
Received on Mon Jan 27 2003 - 15:53:17 CST