sqlplus - using a bind variable in "IN" clause

Question

I am setting a bind variable in a PL/SQL block, and I'm trying to use it in another query's IN expression. Something like this:

variable x varchar2(255)

declare
    x varchar2(100);
begin
    for r in (select id from other_table where abc in ('&val1','&val2','&val3') ) loop
    x := x||''''||r.id||''',';
    end loop;
    --get rid of the trailing ','
    x:= substr(x,1,length(x)-1);

    select x into :bind_var from dual;
end;
/

print :bind_var;

select *
from some_table
where id in (:bind_var);

And I get an error (ORA-01722: Invalid number) on the query that tries to use the bind variable in the "IN" list.

The print statement yiels '123','345' which is what I expect.

Is it possible to use the bind variable like this or should I try a different approach?

(using Oracle 10g)

---

Clarification:

This is for a reconcilliation sort of thing. I want to run

select *
from some_table
where id in (select id from other_table where abc in ('&val1','&val2','&val3'))

before the main part of the script (not pictured here) deletes a whole bunch of records. I want to run it again afterwards to verify that records in some_table have NOT been deleted. However, the data in other_table DOES get deleted by this process so I can't just refer to the data in other_table because there's nothing there. I need a way to preserve the other_table.id values so that I can verify the parent records afterwards.

Answer 1

I would store the other_table.id's in a PL/SQL table and reference that table in the query afterwards:

type t_id_table is table OF other_table.id%type index by binary_integer;
v_table t_id_table;

-- fill the table
select id
bulk collect into v_table
from other_table 
where abc in ('&val1','&val2','&val3');     

-- then at a later stage...    

select *
from some_table st
,    table(cast(v_table AS t_id_table)) idt
where st.id = idt.id;