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

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?

CodePudding user response：

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
               );

CodePudding user response：

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,':'))
      );