PL/SQL - ORACLE APEX: Why does the INSTR function not return anything for the query when given a Page Item as input?

Question

I'm currently struggling to get the following query to work:

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE INSTR(:P8_MEM, USER_ID) > 0;

where :P8_MEM is a page item in the apex environment. The page item is a check box group which contains the USER_IDs of every employee selected by the user formatted in a single colon-delimited string. From my understanding, INSTR should return a value higher than zero if the string is contained in the page item and zero if not.

I already tried to hard code and replace the page item, which resulted in the expected behaviour of updating the HT_USERS table to contain the foreign key ZONE. I have another example of a check box working with a INSTR to check for certain numbers saved as characters. Converting either input to instr to varchar2 does not yield any different results.

Am I doing something wrong here?

Thanks for the feedback and have a great day!

Answer 1

Apart from the problem of where in/from the page your query is running (that was answered in comments), your query is wrong because it is likely to update the wrong users as you never check that a complete term is matched in the delimited list.

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE INSTR(:P8_MEM, USER_ID) > 0;

If :P8_MEM is 13;15 then this will match USER_IDs with the values 1, 3, 5, 13 and 15 as they are all sub-string matches but only 13 and 15 are complete terms and should be matched.

What you need to do is check for a complete term with the surrounding delimiters:

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE INSTR(';' || :P8_MEM || ';', ';' || USER_ID || ';') > 0;

or

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE ';' || :P8_MEM || ';' LIKE '%;' || USER_ID || ';%';