{"id":42,"date":"2012-06-25T12:07:00","date_gmt":"2012-06-25T10:07:00","guid":{"rendered":""},"modified":"2015-11-19T13:41:08","modified_gmt":"2015-11-19T12:41:08","slug":"binding-in-lists-as-comma-separated-values","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2012\/06\/binding-in-lists-as-comma-separated-values\/","title":{"rendered":"Binding IN-lists as comma-separated values"},"content":{"rendered":"<p>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 <a href=\"http:\/\/laurentschneider.com\/wordpress\/2009\/07\/select-from-column-separated-list.html\">this<\/a>, but in this post I&#8217;d like to expand it a little, so it would work even when the string contains special XML characters.<\/p>\n<p>For numbers, the usage is simple:<\/p>\n<pre>\n> var num_list varchar2(100)\n> exec :num_list := &#039;2668,2669,2670&#039;\n\nPL\/SQL procedure successfully completed.\n\n> SELECT id FROM ath_case WHERE id IN (\n SELECT (column_value).getNumberVal() FROM xmltable(:num_list)\n );\n\n        ID\n----------\n      2668\n      2669\n      2670\n\n> exec :num_list := &#039;2671,2672,2673,2674&#039;\n\nPL\/SQL procedure successfully completed.\n\n> SELECT id FROM ath_case WHERE id IN (\n SELECT (column_value).getNumberVal() FROM xmltable(:num_list)\n );\n\n        ID\n----------\n      2671\n      2672\n      2673\n      2674\n<\/pre>\n<p>If the binded list consists of strings, then some extra steps are needed &#8211; the comma-separated has to be enclosed with double-quotes and the values have to be XML-encoded (XML special characters, like <b>&quot;<\/b> replaced with codes).<\/p>\n<pre>\n> var str_list varchar2(100)\n> exec :str_list := &#039;&quot;GI1&quot;,&quot;BI1&quot;&#039;\n\nPL\/SQL procedure successfully completed.\n\n> SELECT u.first_name FROM ath_user u \n JOIN ath_team t ON u.id = t.manager_id \n WHERE t.name IN (\n SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)\n );\n\nFIRST_NAME\n-----------\nRiho\nKaur\n\n> exec :str_list := &#039;&quot;OS1&quot;,&quot;OS2&quot;&#039;\n\nPL\/SQL procedure successfully completed.\n\n> SELECT u.first_name FROM ath_user u \n JOIN ath_team t ON u.id = t.manager_id \n WHERE t.name IN (\n SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)\n );\n\nFIRST_NAME\n-----------\nMarkko\nAive\n\n> set define off\n> exec :str_list := '\"value1\",\"value2\",\"value &amp;quot; with quot\",\"value &amp;amp; with amp\"';\n\nPL\/SQL procedure successfully completed.\n\n> SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list);\n\nDBMS_XMLGEN.CONVERT((COLUMN_VALUE).GETSTRINGVAL(),1)\n-------------------------------------------------------------------------\nvalue1\nvalue2\nvalue \" with quot\nvalue & with amp\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,10],"class_list":["post-42","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/42","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=42"}],"version-history":[{"count":2,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/42\/revisions"}],"predecessor-version":[{"id":173,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/42\/revisions\/173"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=42"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=42"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=42"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}