Written by: ilmarkerm

Category: Blog entry

Published: June 25, 2012

One link that I have to send to developers quite frequently is how to use XMLTABLE in SQL queries to bind comma separated list of values instead of generating large IN list directly to the query (and this way avoid new sqlid/cursor/wasted memory for each different value combination provided). The link that I usually send is this, but in this post I’d like to expand it a little, so it would work even when the string contains special XML characters.

For numbers, the usage is simple:

> var num_list varchar2(100)
> exec :num_list := '2668,2669,2670'

PL/SQL procedure successfully completed.

> SELECT id FROM ath_case WHERE id IN (
 SELECT (column_value).getNumberVal() FROM xmltable(:num_list)
 );

        ID
----------
      2668
      2669
      2670

> exec :num_list := '2671,2672,2673,2674'

PL/SQL procedure successfully completed.

> SELECT id FROM ath_case WHERE id IN (
 SELECT (column_value).getNumberVal() FROM xmltable(:num_list)
 );

        ID
----------
      2671
      2672
      2673
      2674

If the binded list consists of strings, then some extra steps are needed – the comma-separated has to be enclosed with double-quotes and the values have to be XML-encoded (XML special characters, like " replaced with codes).

> var str_list varchar2(100)
> exec :str_list := '"GI1","BI1"'

PL/SQL procedure successfully completed.

> SELECT u.first_name FROM ath_user u 
 JOIN ath_team t ON u.id = t.manager_id 
 WHERE t.name IN (
 SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)
 );

FIRST_NAME
-----------
Riho
Kaur

> exec :str_list := '"OS1","OS2"'

PL/SQL procedure successfully completed.

> SELECT u.first_name FROM ath_user u 
 JOIN ath_team t ON u.id = t.manager_id 
 WHERE t.name IN (
 SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)
 );

FIRST_NAME
-----------
Markko
Aive

> set define off
> exec :str_list := '"value1","value2","value " with quot","value & with amp"';

PL/SQL procedure successfully completed.

> SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list);

DBMS_XMLGEN.CONVERT((COLUMN_VALUE).GETSTRINGVAL(),1)
-------------------------------------------------------------------------
value1
value2
value " with quot
value & with amp