Home » SQL & PL/SQL » SQL & PL/SQL » Using bind variables with a variable length IN clause (ALL versions, oracle 10g, oracle 11g)
Using bind variables with a variable length IN clause [message #294537] Thu, 17 January 2008 23:36 Go to next message
stevekerver
Messages: 19
Registered: January 2008
Junior Member
This may seem like a novice question, but, the more I think about it, the more I'm scratching my head...
I'm trying to maximize the re-use of an SQL statement with an IN clause that has a variable number of parameters.

Assume the following:
-----------------------------------------
create table test (test_id number, test_text nvarchar2(30));
insert into test (test_id, test_text) values (1, 'red');
insert into test (test_id, test_text) values (2, 'blue')
insert into test (test_id, test_text) values (3, 'green')
insert into test (test_id, test_text) values (4, 'yellow')
commit;

SQL> select * from test;

TEST_ID TEST_TEXT
---------- ------------------------------
1 red
2 blue
3 green
4 yellow

SQL> select * from test where test_text IN('red', 'yellow', 'gray');

TEST_ID TEST_TEXT
---------- ------------------------------
1 red
4 yellow

Ok- so far, so good.
And, even if I do this, it works:

SQL> variable in_list nvarchar2(999);
SQL> exec :in_list := 'yellow';
SQL> select * from test where test_text IN(:in_list);

TEST_ID TEST_TEXT
---------- ------------------------------
4 yellow

That's the behavior I'm looking for.
...But now, suppose I want to use a bind variable with the IN clause, and the number of values passed to the bind variable can be anywhere from 1 to n
(where n = the number of distinct values in the test_text column).
For example, instead of passing any of the literal statements:

SELECT * from test where test_text IN('yellow', 'blue') <--Passing 2 parameters...
or
SELECT * from test where test_text IN('yellow', 'blue', 'red') <--Passing 3 parameters...

What I want to do is use just one bind variable. ...I don't want to use a variable number of bind variables every time the number of parameters passed to the IN clause changes.
For example- I don't want to have to do something like this:

SELECT * from test where test_text IN(:b1);
SELECT * from test where test_text IN(:b1, :b2);
SELECT * from test where test_text IN(:b1, :b2, :b3, ...:bn);

The reason being, everytime the optimizer sees that a new bind variable is added to the IN clause, it has to parse the statement anew, and will not re-use it effectively from the library cache.
So- that won't work.

What I need is something like:

SELECT * from test where test_text IN(:in_list);

That way, no matter how many values are in the :in_list, the optimizer parses just once.

But the problem is that when I try to use more than one value with the bind variable, it bombs out.

I tried the following, with no success:

SQL> exec :in_list := chr(39)||'red'||chr(39)||', '||chr(39)||'yellow'||chr(39)||', '||chr(39)||'grey'||chr(39);

PL/SQL procedure successfully completed.

SQL> print :in_list;

IN_LIST
-----------------------------------
'red', 'yellow', 'grey'

SQL> select * from test where test_text IN(:in_list);

no rows selected


...No rows selected?? Hmmm.


-----------------------------
Re: Using bind variables with a variable length IN clause [message #294541 is a reply to message #294537] Thu, 17 January 2008 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AskTom varying elements in IN list

Regadrs
Michel
Re: Using bind variables with a variable length IN clause [message #294740 is a reply to message #294537] Fri, 18 January 2008 14:18 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
the short version of Tom's postings is this:

create table test (test_text varchar2(1000))
/

insert into test_text values ('a');
insert into test_text values ('b');
insert into test_text values ('c');
insert into test_text values ('d');

--
-- using 'b,c' as the list
--
select *
from test
where test_text in (
                     select substr(
                                          ','||'b,c'||','
                                   ,instr(','||'b,c'||',',',',1,rownum)+1
                                   ,instr(','||'b,c'||',',',',1,rownum+1)-instr(','||'b,c'||',',',',rownum)-1
                                  )
                     from dual
                     connect by level <= (length('b,c')-length(replace('b,c',','))+1)
                   )
/

Normally I don't like posting code for people just off the top, and I am not suggesting you should not read the idea exchanges at Tom's site; as you should. But... so many people has added to this particular dialog at Tom's site, that is has become a long read, and gets somewhat off topic about half way through. So in this case I make an exception and give you the reader's digest version.

Good luck, Kevin
Previous Topic: Full Outer Joins with more than 2 tables
Next Topic: counting
Goto Forum:
  


Current Time: Sun Dec 04 12:35:24 CST 2016

Total time taken to generate the page: 0.04495 seconds