sqlplus - using a bind variable in "IN" clause
Solution 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;
Solution 2
changed the loop to use listagg (sadly this will only work in 11gr2).
but for the variable in list, I used a regular expression to accomplish the goal (but pre 10g you can use substr to do the same) this is lifted from the asktom question linked.
variable bind_var varchar2(255)
variable dataSeperationChar varchar2(255)
declare
x varchar2(100);
begin
select listagg(id,',') within group(order by id) idList
into x
from(select level id
from dual
connect by level < 100 )
where id in (&val1,&val2,&val3) ;
select x into :bind_var from dual;
:dataSeperationChar := ',';
end;
/
print :bind_var;
/
select *
from (
select level id2
from dual
connect by level < 100
)
where id2 in(
select -- transform the comma seperated string into a result set
regexp_substr(:dataSeperationChar||:bind_var||','
, '[^'||:dataSeperationChar||']+'
,1
,level) as parsed_value
from dual
connect by level <= length(regexp_replace(:bind_var, '([^'||:dataSeperationChar||'])', '')) + 1
)
;
/*
values of 1,5, and 25
BIND_VAR
------
1,5,25
ID2
----------------------
1
5
25
*/
EDIT
Oops just noticed that you did mark 10g, the only thing to do is NOT to use the listagg that I did at the start
Solution 3
I would use a global temporary table for this purpose
create global temporary table gtt_ids( id number ) ;
then
...
for r in (select id from other_table where ... ) loop
insert into gtt_ids(id) values (r.id) ;
end loop;
...
and at the end
select *
from some_table
where id in (select id from gtt_ids);
Solution 4
You can't use comma-separated values in one bind variable.
You could say:
select * from some_table where id in (:bind_var1, :bind_var2)
though
You're better off using something like:
select * from some_table where id in ("select blah blah blah...");
Comments
-
FrustratedWithFormsDesigner about 4 years
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 inother_table
DOES get deleted by this process so I can't just refer to the data inother_table
because there's nothing there. I need a way to preserve theother_table.id
values so that I can verify the parent records afterwards. -
FrustratedWithFormsDesigner over 13 yearsUnfortunately, I don't always know how many values should be in the IN clause. Otherwise I wouldn't have tried to do this dynamically in the first place. ...wait, I could set the bind variable to the SQL text of a subquery? Hmm... give me a minute...
-
Gerrat over 13 yearsRight, but why not just do it in one statement?
-
FrustratedWithFormsDesigner over 13 yearsHm setting the bind variable to a query string does not seem to work either.
-
Gerrat over 13 yearsNo. That's not what I was getting at. You've clarified the question & shown exactly what I was getting at (to just select in 1 stmt).
-
Gerrat over 13 yearsNice. I was just looking up the syntax of the bulk collect, and didn't even know about the "table(cast(v_table AS t_id_table))" part. I think this is the ideal solution for the OP!
-
Justin Cave over 13 yearsIn recent Oracle versions, the CAST should be unnecessary-- you can just do TABLE(v_table)
-
FrustratedWithFormsDesigner over 13 yearsInteresting... I'll have to try this out!
-
FrustratedWithFormsDesigner over 13 years@Diederik Hoogenboom: Is it possible to execute the "later stage" SQL statement (the one that references
v_table
) outside of a PL/SQL block? The query that it replaces is at the end of the script and outside the PL/SQL block (to make spooling to output file simpler) and this gives me an invalid datatype error for t_id_table. Does it have to be executed in the same block thatt_id_table
is declared in? -
hesseux over 13 years@FrustratedWithFormsDesigner You can declare the pl/sql table in a package so it will retain the data during the session.
-
FrustratedWithFormsDesigner over 13 years@Diederik: Adding new packages at this stage (or modifying an existing one) just to get this type would not be practical, but I think this will work if I move most of the script to PL/SQL and then use dbms_output to print the data. Not what I initially wanted, but I'll probably move the next set of scripts in that direction.