Oracle Apex: Creating a Shuttle Item

In Oracle Apex, a shuttle item is a kind of list item having a multi-select option. It has two boxes (left and right) containing lists; the left side shows the source of the list and right side the selected values.

The user can select the list options using its controls. Selected values are stored in a single colon-delimited string.

In this tutorial, you will learn how to create a shuttle item in Oracle Apex.

Here I am going to give two examples for this:

1. Storing shuttle item values into a single field of a table.
2. Storing shuttle item values into a separate table as multiple records.

Creating a Shuttle Item in Oracle Apex

In the following example, we are going to store the selected shuttle item values into a field.

Example 1: Storing Shuttle Item Selected Values into a Field

To do this, you can use any of your existing Varchar2 column in the table, but it should have a good length to store the selected values.

Or you can add a new column as Varchar2(4000) into your table.

Now open your page in Oracle Apex, go to the region your table based on and add the new item and change its type to Shuttle.

Select the source as SQL query and specify the SQL query something like below:

select product_name d, product_id r
   from demo_product_info
order by product_name

You can also select the other type of sources such as Static Values and Shared Components LOV, etc.

So now we have the product_name as the display column and the product_id as the return column.

Below is the screenshot for the above setting:

You also need to specify the source table column for the shuttle item.

Now save the changes and run the page to test. The output will be as shown in the below image:

The selected product ids will be stored into a field like 1001:1008:1011:1020.

Example 2: Storing Selected Shuttle Item Values into a Separate Table

To store the selected values into a table, you can create a process just after the default form process and use the following PL/SQL code:

Declare
  Cursor c_values
   is 
  Select
    t.column_value As prodcut_ids
  From
     Table ( apex_string.split(:P9_PRODUCTS, ':') ) t
  Where
     t.column_value Is Not Null;
Begin
  for c in c_values loop
     insert into your_table (product_id)
       values (c.product_ids);
  end loop;
End;

The above PL/SQL code will insert the selected shuttle item values as multiple records.