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.

score 6 · Accepted Answer · answered Feb 11 '11 at 21:37

6

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;

answered Feb 11 '11 at 21:37

diederikh

25,221
5
36
49

Nice. 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! – Gerrat Feb 11 '11 at 21:48
In recent Oracle versions, the CAST should be unnecessary-- you can just do TABLE(v_table) – Justin Cave Feb 11 '11 at 21:52
Interesting... I'll have to try this out! – FrustratedWithFormsDesigner Feb 11 '11 at 22:00
@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 that `t_id_table` is declared in? – FrustratedWithFormsDesigner Feb 14 '11 at 16:38
@FrustratedWithFormsDesigner You can declare the pl/sql table in a package so it will retain the data during the session. – diederikh Feb 14 '11 at 18:06
@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. – FrustratedWithFormsDesigner Feb 15 '11 at 15:54

score 1 · Answer 2 · answered Feb 11 '11 at 20:44

1

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

answered Feb 11 '11 at 20:44

Gerrat

28,863
9
73
101

Unfortunately, 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... – FrustratedWithFormsDesigner Feb 11 '11 at 20:46
Right, but why not just do it in one statement? – Gerrat Feb 11 '11 at 20:48
Hm setting the bind variable to a query string does not seem to work either. – FrustratedWithFormsDesigner Feb 11 '11 at 20:57
No. 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 Feb 11 '11 at 20:58

score 1 · Answer 3 · answered Feb 11 '11 at 21:38

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

Harrison · Answer 4 · 2011-02-11T21:58:53.663

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

score 0 · Answer 5 · answered Feb 11 '11 at 21:14

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.

sqlplus - using a bind variable in "IN" clause

5 Answers5

Linked