{"id":7805,"date":"2021-05-11T23:38:47","date_gmt":"2021-05-12T04:38:47","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=7805"},"modified":"2021-05-11T23:40:54","modified_gmt":"2021-05-12T04:40:54","slug":"oracle-collection-instead-of-dual","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=7805","title":{"rendered":"Oracle Collection Instead of Dual"},"content":{"rendered":"<p>I&#8217;m <em>still<\/em> retrofitting a bunch of SQL queries to use bind_by_name and came across a strange scenario. I created a recursive query (STARTS WITH \/ CONNECT BY PRIOR) but I needed to grab the original value too. The quickest way to accomplish this was to union in something like &#8220;select 12345CDE as equipment_id from dual&#8221;. But the only way to get a bunch of these original values grafted onto the result set is to iterate through the array once to build my :placeholder1, :placeholder2, &#8230;, placeholderN placeholders and then iterate through the array again to bind each placeholder to its proper value.<\/p>\n<p>I&#8217;ve been working with Oracle collections for LIKE and IN queries, and thought I could use a table that only exists within the query to glom the entire array into a single placeholder. It works! A query like<\/p>\n<pre>select column_value equipment_id from TABLE(sys.ODCIVARCHAR2LIST('12345CDE', '23456BCD', '34567ABC') );<\/pre>\n<p>Adds each of the values to my result set.<\/p>\n<p><a href=\"https:\/\/www.rushworth.us\/lisa\/?attachment_id=7806\" rel=\"attachment wp-att-7806\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7806\" src=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/05\/TableCollectionInsteadOfDual.png\" alt=\"\" width=\"782\" height=\"202\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/05\/TableCollectionInsteadOfDual.png 782w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/05\/TableCollectionInsteadOfDual-300x77.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/05\/TableCollectionInsteadOfDual-768x198.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2021\/05\/TableCollectionInsteadOfDual-750x194.png 750w\" sizes=\"auto, (max-width: 782px) 100vw, 782px\" \/><\/a><\/p>\n<p>Which means I can use a query like &#8220;select column_value as equipment_id from TABLE(:myIDs)&#8221; and bind the collection to :myIDs.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m still retrofitting a bunch of SQL queries to use bind_by_name and came across a strange scenario. I created a recursive query (STARTS WITH \/ CONNECT BY PRIOR) but I needed to grab the original value too. The quickest way to accomplish this was to union in something like &#8220;select 12345CDE as equipment_id from dual&#8221;. &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1004],"tags":[1360,595,922],"class_list":["post-7805","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-database-query","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7805"}],"version-history":[{"count":3,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7805\/revisions"}],"predecessor-version":[{"id":7809,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7805\/revisions\/7809"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}