apex_string.split is a great utility that allows you to take a delimited list and make it queryable. Here’s a small example:
You can read more about using
apex_string.split and how to parse CSVs here.
When I use the
split function to filter a table I used to do an explicit
join as shown below. There’s nothing wrong with this concept but it can be a bit clunky to read and write.
member of is a nice clean solution however since
apex_string.split is a PL/SQL function there’s the potential of a lot of context switching (between SQL and PL/SQL) that can slow your code down. To verify this I made a simple wrapper function which logs all the calls to the function:
create or replace function wrapper_split(
Using the previous example:
They’re 14 rows in the
emp table and
apex_string.split was called 14 times. When using this on larger tables it could have significant performance impacts. Thankfully there’s a simple solution: scalar subqueries.
This subtle difference limits it to one PL/SQL call regardless of the size of the table it’s checking. If using the
member of syntax don’t forget to also reference
apex_string.split in a scalar subquery.