Multi-select List Item in Oracle APEX | Shuttle Item

Often a time in our project we have a requirement to create a multi-select list item in order to represent a many-to-many relationship. For e.g. you want to implement a functionality where a customer can select multiple products they are interested in. I encountered one such requirement recently in my Telegram Integration application where a user can send a message in multiple telegram groups. For that user needs to select all the telegram groups in which he wants to send a message. 

There are three native multi-select items available in Oracle APEX; Checkbox Group, List Manager, and Shuttle. In this post, I will explain how to create a Shuttle Item in Oracle APEX. 

What is a Shuttle item?

In Oracle APEX, a shuttle item renders as a multi-select list value. It has two boxes left and right. The left box (source list) displays all the values available to select and the right box (destination list) displays all the selected values. A user can select the values from left box to right box using shuttle control buttons. How data is stored in the database?

All the values that the user selected in the right box are stored as a colon-delimited string (val1:val2:val3) in a database column. 

How to create a Shuttle item? 

1. Create a VARCHAR2 column having a large size as it stores a colon-delimited string.
2. Navigate to an APEX page and create a page item in a region that is based on the database table.
3. Change its type to "Shuttle".
4. Go to the "List of Values" section in the property section and select the list of values type. Here I selected "SQL Query" for Type. 
5. Specify the source query in the below format in the SQL Query field. 

   SELECT COLUMN1 D,COLUMN2 R FROM TABLE_NAME;

   Here COLUMN1 is a display column whose value you can see on the left box and COLUMN2 is a return value that is stored in the database in a colon-delimited string.

6. Save the changes and run the page.

How to use that colon-delimited value in a query?

Since the selected values of a Shuttle item are stored as a colon-delimited string in a database, we need to convert this colon-delimited string into rows.  We can use an Oracle APEX API APEX_ITEM.SPLIT to split the input string into rows. For e.g. the below query will return three rows val1, val2, and val3. 

SELECT COLUMN_VALUE FROM TABLE(APEX_STRING.SPLIT('val1:val2:val3',':'));

In PL/SQL block we can use this API as mentioned below to process each value.

DECLARE
     L_str_arr  apex_t_varchar2;
     L_String VARCHAR2(100):= 'val1:val2:val3:val4:val5'; 
BEGIN
     L_str_arr := APEX_STRING.SPLIT(P_str => L_String, P_sep => ':'); -- Replace L_String with database column name
     FOR i IN 1..L_str_arr.COUNT
     LOOP
        DBMS_OUTPUT.PUT_LINE(L_str_arr(i));
     END LOOP;
END;

Below is the output of this code snippet

In the future post, I will show how to use Checkbox Group and List Manager.