A well known “limitation” of Oracle is that you can only have 1000 elements within an IN-LIST style expression in your SQL predicates. I use the quotes because I’ve always found that if you are heading into the territory where you need more than 1000 items in an IN-LIST, then it is often indicative of something else being wrong. For example, if the code is backing a user interface, then where in the design phase did someone not go “Whoa…we are going to have 1000 select-able elements on the screen?”
In any event, you can find many tricks and techniques out there on the intertubes about how workaround this issue, such as:
- Breaking the elements into multiple lists https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause
- Using TUPLES instead of scalar values. https://stackoverflow.com/questions/39858904/using-tuples-to-put-more-than-1000-entries-in-sql-in-clause
- And finally putting the elements into a temporary table, and the performing a standard join or similar.
My preference is for the last one because once you use a temporary table, suddenly all of the power of SQL is at your fingertips. I had an AskTOM question recently which gave me an opportunity to display this. The requester had an IN-LIST set of values, but the values could contain the standard wild-cards. Using a temporary table approach, this becomes trivial with a small adjustment to the join condition.
Here’s my first solution using a familiar XMLTABLE technique.
SQL> create table t ( x varchar2(50));
Table created.
SQL> insert into t values ('stringasd');
1 row created.
SQL> insert into t values ('blah');
1 row created.
SQL> insert into t values ('more data');
1 row created.
SQL> insert into t values ('blah blah');
1 row created.
SQL> insert into t values ('some stuff with qwe in it');
1 row created.
SQL> insert into t values ('more with a double match on qwe');
1 row created.
SQL>
SQL> with
2 conditions as
3 ( select 'string%,more%,%qwe%' wildcard_list from dual ),
4 rowstyle as
5 (
6 select trim(column_value) inlist_item
7 from conditions,
8 xmltable(('"'||replace(wildcard_list, ',', '","')||'"'))
9 )
10 select *
11 from rowstyle;
INLIST_ITEM
--------------------
string%
more%
%qwe%
3 rows selected.
SQL>
SQL>
SQL> with
2 conditions as
3 ( select 'string%,more%,%qwe%' wildcard_list from dual ),
4 rowstyle as
5 (
6 select trim(column_value) inlist_item
7 from conditions,
8 xmltable(('"'||replace(wildcard_list, ',', '","')||'"'))
9 )
10 select *
11 from rowstyle r,
12 t
13 where t.x like r.inlist_item;
INLIST_ITEM X
-------------------- ----------------------------------------
string% stringasd
more% more data
%qwe% some stuff with qwe in it
more% more with a double match on qwe
%qwe% more with a double match on qwe
5 rows selected.
SQL>
SQL>
XMLTABLE has its limitations in terms of handling special characters as you can see from below:
SQL> set define off
SQL> with
2 conditions as
3 ( select 'string%,more%,%abc&xyz%' wildcard_list from dual ),
4 rowstyle as
5 (
6 select trim(column_value) inlist_item
7 from conditions,
8 xmltable(('"'||replace(wildcard_list, ',', '","')||'"'))
9 )
10 select *
11 from rowstyle r,
12 t
13 where t.x like r.inlist_item;
with
*
ERROR at line 1:
ORA-19112: error raised during evaluation:
XVM-01003: [XPST0003] Syntax error at '"%abc'
1 "string%","more%","%abc&xyz%"
- ^
but this does not rule out the temporary table technique – we just need to use a different mechanism to convert our list into rows. Here’s another common technique, using a regular expression and a hierarchy CONNECT BY syntax.
SQL> with
2 conditions as
3 ( select 'string%,more%,%abc&xyz%' wildcard_list from dual ),
4 rowstyle as
5 (
6 select trim(w) inlist_item
7 from conditions c,
8 lateral(
9 select trim(regexp_substr(c.wildcard_list, '[^,]+',1,level)) w
10 from dual
11 connect by level <= regexp_count(c.wildcard_list,'[^,]+')
12 )
13 )
14 select *
15 from rowstyle r
16 /
INLIST_ITEM
--------------------
string%
more%
%abc&xyz%
3 rows selected.
With the special characters issue fixed, it is then a simple join with the LIKE to obtain the desired result.
SQL> with
2 conditions as
3 ( select 'string%,more%,%abc&xyz%' wildcard_list from dual ),
4 rowstyle as
5 (
6 select trim(column_value) inlist_item
7 from conditions c,
8 lateral(
9 select regexp_substr(c.wildcard_list, '[^,]+',1,level) column_value
10 from dual
11 connect by level <= regexp_count(c.wildcard_list,'[^,]+')
12 )
13 )
14 select *
15 from rowstyle r,
16 t
17 where t.x like r.inlist_item;
INLIST_ITEM X
-------------------- ----------------------------------------
string% stringasd
more% more data
more% more with a double match on qwe
3 rows selected.
So before you look at the “tricks” to allow more than 1000 items in an IN-LIST, do not be too quick to dismiss the value of converting the values to rows, and therefore opening up all the power of SQL to perform your processing.
Leave a Reply