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

[Ryan Gaffuri]

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?

[Jusung Yang]

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

rgaf...@cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.03012...@posting.google.com>...

[Jusung Yang]

Oops, forgot in my previous post to include this type creation statement:

create type typ_tab_v2100 as table of varchar2(100);
/

- Jusung Yang

rgaf...@cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.03012...@posting.google.com>...

[Kenny Yu]

I see no need for the in-list nor the dynamic sql. The resource id's must be
from a sql. ALl you need is

DELETE FROM RD_ASSOC_CONTAINER_TEST
WHERE RESOURCE_ID IN
(

select resource_id from a_table
)

Kenny

----

"Ryan Gaffuri" <rgaf...@cox.net> wrote in message
news:1efdad5b.03012...@posting.google.com...