Watch out for impact of NLS_SORT and NLS_COMP settings on string comparisons (1645)

The 12 November quiz asked you to evaluate various implementations of ways to find matches on strings in a collection. Peter wrote with the following observation: "This is actually not a question about a quiz, nor an error/ambiguity report; just my intention to point out an environment-dependent behaviour in one of the quiz answers. It's about the 12 November's quiz and the answer choice using associative array copied from the original nested-table collection. Well, the thing is, the output of the given anonymous PL/SQL block is dependent on the setting of two parameters - NLS_COMP and NLS_SORT. Given the standard setting of NLS_COMP = BINARY, the block would yield the TRUE, TRUE, FALSE as requested in the quiz question. "However, if NLS_COMP is set to LINGUISTIC and NLS_SORT is set to e.g. BINARY_CI, the block would output TRUE, TRUE, TRUE, as the result of 'Steven' being case-insensitively equal to 'steven'. This behaviour is well described in Oracle DB documentation. I search the "Rules" about Oracle environment assumptions and found no mention about these specific settings. So I just would like to point it out; I don't really take this to be a quiz error. "The second (for me *the* more interesting thing about this quiz) is the behaviour of the MEMBER OF operator used in one of the other answer choices. I would think that this operator, as it's (I believe) merely comparing the array values against a single value, would be prone to produce different results depending on NLS_COMP/NLS_SORT parameters too. Yet, it is not. I cannot explain this, I merely point out again as an interesting fact. All my tests were done on a 11.2.0.1 version of Oracle EE DB. I don't have a 10g R2 to play with so cannot tell if the MEMBER OF thing is anyhow 11g specific." Many thanks, Peter! My sense is that our assumptions (default installation of Oracle) means that we do not have to add another assumption. But this is fascinating and useful information about which we should all be aware. Cheers, SF