Sqlplus - Using A Bind Variable In "in" Clause

August 20, 2024 Post a Comment

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

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 istableOF other_table.id%type index by binary_integer;
v_table t_id_table;

-- fill the tableselect id
bulk collectinto 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:

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

Solution 3:

I would use a global temporary table for this purpose

createglobal temporary table gtt_ids( id number ) ;

then

...
for r in (select id from other_table where ... ) loop
   insertinto 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:

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);
beginselectlistagg(id,',')  withingroup(orderby id) idList
into x
  from(select level id 
         from dual  
        connectby 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
         connectby 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
            connectby 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 5:

Ok, I have a kind of ugly solution that also uses substitution variables...

col idList NEW_VALUE v_id_list /* This is NEW! */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 :x idList from dual;  /* This is NEW! */select *
from some_table
where id in (&idList);  /* This is CHANGED! */

It works, but I'll accept an answer from someone else if it's more elegant.

Lab Sql