{"id":7760,"date":"2021-05-04T23:11:58","date_gmt":"2021-05-05T04:11:58","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=7760"},"modified":"2021-05-06T10:00:37","modified_gmt":"2021-05-06T15:00:37","slug":"oracle-collections-and-in-or-like-queries","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=7760","title":{"rendered":"Oracle – Collections and IN or LIKE Queries"},"content":{"rendered":"\n

I’ve been retrofitting a lot<\/em> of PHP\/SQL queries to use oci_bind_by_name recently. When using “IN” clauses, you can<\/em> iterate through your array twice, but it’s an inefficient approach.<\/p>\n\n\n

\n\/\/ Build the query string with a bunch of placeholders\n$strQuery = "select Col1, Col2, Col3 from TableName where ColName IN (";\nfor($i=0; $i < count($array); $i++){\n    if($i > 0){\n        $strQuery = $strQuery . ", ";\n    }\n    $strQuery = $strQuery . ":bindvar" . $i;\n}\n$strQuery = $strQuery . ")";\n... \n\/\/ Then bind each placeholder to something\nfor($i=0; $i < count($array); $i++){\n    oci_bind_by_name($stmt, ":bindvar".$i, $array[$i]);\n}\n\n<\/pre><\/div>\n\n\n
Building a table from the array data and using an Oracle collection object creates cleaner code and avoids a second iteration of the array:<\/p>\n\n\n
\n$strQuery = "SELECT indexID, objName FROM table WHERE objName in (SELECT column_value FROM table(:myIds))";\n$stmt = oci_parse($conn, $strQuery);\n\n$coll = oci_new_collection($kpiprd_conn, 'ODCIVARCHAR2LIST','SYS');\nforeach ($arrayValues as $strValue) {\n     $coll->append($strValue);\n}\noci_bind_by_name($stmt, ':myIds', $coll, -1, OCI_B_NTY);\noci_set_prefetch($stmt, 300);\noci_execute($stmt);\n<\/pre><\/div>\n\n\n
A simple like clause is quite straight-forward<\/p>\n\n\n
\n$strNameLikeString = "SomeName%";\n$strQuery = "SELECT ds_dvrsty_set_nm from ds_dvrsty_set WHERE ds_dvrsty_set_nm LIKE :divsetnm ORDER BY ds_dvrsty_set_nm DESC fetch first 1 row only";\n\n$stmt = oci_parse($connDB, $strQuery);\noci_bind_by_name($stmt, ":divsetnm", $strNameLikeString);\noci_set_prefetch($stmt, 300);\noci_execute($stmt);\n\n<\/pre><\/div>\n\n\n
But what about an array of inputs essentially reproducing the LIKE ANY predicate in PostgreSQL? There’s not a direct equivalent in Oracle, and iterating through the array twice to build out a query WHERE (Field1 LIKE ‘Thing1%’ OR Field1 LIKE ‘Thing2%’ OR Field1 LIKE ‘Thing3%’) is undesirable. The with EXISTS allows me to create a LIKE ANY type query and only iterate through my array once to bind variables to placeholders using the same collection approach as was used with the IN clause. <\/p>\n\n\n
\n$arrayLocs = array('ERIEPAXE%', 'HNCKOHXA%', 'LTRKARXK%');\n$strQuery = "SELECT location_id, clli_code FROM network_location WHERE EXISTS (select 1 FROM TABLE(:likelocs) WHERE clli_code LIKE column_value)";\n$stmt = oci_parse($connDB, $strQuery);\n\n$coll = oci_new_collection($connDB, 'ODCIVARCHAR2LIST','SYS');\nforeach ($arrayLocs as $strLocation) {\n    $coll->append($strLocation);\n}\noci_bind_by_name($stmt, ':likelocs', $coll, -1, OCI_B_NTY);\noci_execute($stmt);\nprint "<table>\\n";\nprint "<tr><th>Loc ID<\/th><th>CLLI<\/th><\/tr>\\n";\nwhile ($row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {\n    print "<tr><td>" . $row['LOCATION_ID'] . "<\/td><td>" . $row['CLLI_CODE'] . "<\/td><\/tr>\\n";\n}\nprint "<\/table>\\n";\n<\/pre><\/div>\n\n\n
There are many different collection types in Oracle which can be used with oci_new_collection. A full list of the system collection types can be queried from the database. <\/p>\n\n\n
\nSELECT * FROM SYS.ALL_TYPES WHERE TYPECODE = 'COLLECTION' and OWNER = 'SYS';\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
I’ve been retrofitting a lot of PHP\/SQL queries to use oci_bind_by_name recently. When using “IN” clauses, you can iterate through your array twice, but it’s an inefficient approach. Building a table from the array data and using an Oracle collection object creates cleaner code and avoids a second iteration of the array: A simple like …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,1004],"tags":[595,35],"class_list":["post-7760","post","type-post","status-publish","format-standard","hentry","category-coding","category-oracle","tag-oracle","tag-php"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7760","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=7760"}],"version-history":[{"count":3,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7760\/revisions"}],"predecessor-version":[{"id":7789,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/7760\/revisions\/7789"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7760"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7760"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7760"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}