This is a fairly minor thing, but it comes up periodically on the APEX forum. Most page items are easy to deal with; their values are placed in the variable just like you expect, so you can do things like
...where p.item_id = :P15_ITEM_ID...
But multi-selects and checkboxes are harder to deal with, because they allow the user to chose multiple values. So, how do you deal with them?
The APEX team decided to pack all of the selected values into the field, separated by colons. This means that a typical value could be something like “1:2:3”. An initial response might be to just use instr to search the returned value:
...where instr(:P15_ITEM_ID, p.item_id) > 1...
This may appear to work…as long as your values are sufficiently unique. But sooner or later, you run into an instance where your application thinks that “10” is in “99:100:101”, and everything goes pear-shaped very, very quickly.
The answer is to get creative with the like command:
...where ':'||:P15_ITEM_ID||':' like '%:'||p.item_id||':%'...
This may look horrendous (look at all those colons and concatenations!), and will not work well for huge datasets (since you can't use an index on item_id). But it's effective for 99% of the time. All it does is ensure that every value in the page item is bracketed by colons, and then searches for values bracketed by colons. So “:10:” is not in “:99:100:101:”, and everything works the way it should.
…which is all well and good, if you're using this to power a report. But what if you're trying to insert or update rows in a table? There, I'm afraid you're going to have to actually (gasp!) write some code. Happily, APEX gives us the apex_utl.string_to_table function, which gets you most of the way there (usually). Build a trigger and just loop through the return value, doing the Right Thingtm.