How to delete multiple rows using shuttle page item in Oracle Apex?

Question

My requirement is that users must be able to select from a shuttle page item the employee numbers that they need to delete from a table. In the back end I have a plsql code that is supposed to delete the selected employee numbers as follows:

BEGIN
    delete from employees where empno in (:P7_EMPLOYEE_NUMBER);
END;

Additionally I will have more logic in the code to do other stuff, however I am not able to delete multiple rows, if I select 1 employee number in the shuttle item, I am able to delete the record successfully, when I try to delete more than one record I keep getting the following error:

Ajax call returned server error ORA-01722: invalid number for Execute Server-Side Code

I changed the code to:

BEGIN
    delete from employees where empno in (to_number(:P7_EMPLOYEE_NUMBER));
END;

and I keep getting the same error message.

How can I make this work?

Answer 1

The API APEX_STRING has a number of utility functions to deal with multi-value page items (select lists, checkbox, shuttles). To convert a colon separated list to an array, use APEX_STRING.SPLIT.

DELETE 
  FROM     
       employees 
 WHERE empno IN 
      (SELECT column_value 
         FROM table(apex_string.split(: P7_EMPLOYEE_NUMBER,':'))
      ); 

Answer 2

A shuttle item contains colon-separated values, which means that you have to split it to rows, e.g.

delete from employees 
where empno in (select regexp_substr(:P7_EMPLOYEE_NUMBER, '[^:]+', 1, level)
                from dual
                connect by level <= regexp_count(:P7_EMPLOYEE_NUMBER, ':') + 1
               );

Answer 3

The APEX engine will always submit multi-values items as a single colon-delimited string, for example: 1:2:3:4

You need to split the string into multiple values so that you can process them. There are multiple ways to do this:

1. Using an the APEX_STRING.SPLIT or the APEX_STRING.SPLIT_NUMBERS API in a subquery

delete from employees 
 where empno in (select column_value
                   from apex_string.split_numbers(:P7_EMPLOYEE_NUMBER, ':'));

2. Using the APEX_STRING API with the MEMBER OF function

delete from employees 
 where empno member of apex_string.split_numbers(:P7_EMPLOYEE_NUMBER, ':');

Note that the member of needs to have the same type. In this case empno is a number so you must use the split_numbers API.

3. Using a regular expression to split the values

delete from employees 
 where empno in (select regexp_substr(:P7_EMPLOYEE_NUMBER, '[^:]+', 1, level)
                   from dual
                connect by level <= regexp_count(:P7_EMPLOYEE_NUMBER, ':') + 1
                );

I prefer using option 2 as it's less code and easier to read.