Extending in-lists

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:

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.

One Comment on “Extending in-lists

  1. Hi Connor,

    The “black magic” of XML …..
    Looks like the ampersand character drove the XML engine crazy ….

    Just for my XML (poor) knowledge: Can it be escaped somehow ?

    Of course, in addition to your alternative nice solution 🙂

    Cheers & Best Regards,
    Iudith

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: