Introduction
A Shuttle item in Oracle APEX is a form element that allows users to move one or more values from an available list to a selected list. It’s particularly useful when you want to let users assign multiple options to a record — such as assigning users to roles, students to classes, or employees to projects. This blog will walk you through the steps to create a Shuttle item on a form page using Page Designer, explain how it works behind the scenes, and offer detailed examples and best practices.
How to Create a Shuttle Item – Step-by-Step
Use Case Example:
We have a projects table and a project_employees association table. We want to create a form where users can assign multiple employees to a project using a shuttle.
1. Open Your Page in Page Designer
-
Log in to your APEX workspace.
-
Open your application and go to the form page where you want to add the Shuttle item.
2. Create the Shuttle Item
-
In Page Designer, under the appropriate region, click + to add a new item.
-
Select Shuttle as the item type.
-
Set a name such as
P1_EMPLOYEE_LIST.
3. Define the List of Values (LOV)
Under the Shuttle item’s List of Values section:
-
Set Type to
SQL Query. -
Example query:
SELECT full_name d, employee_id r FROM employees ORDER BY full_name-
dis the display value shown to the user. -
ris the return value stored when submitted.
-
4. Configure Storage
The Shuttle will return a colon-separated list of values (e.g., 101:104:109).
You have two main storage options:
-
A. Store as a delimited string in one column (not recommended for relational data).
-
B. Process the values in a
BEFORE SUBMITPL/SQL process and insert them into a child table (recommended).
5. Process the Shuttle Selection
Create a PL/SQL page process to handle the selected values.
Example:
DECLARE
l_array apex_application_global.vc_arr2;
BEGIN
-- Delete existing assignments for the project
DELETE FROM project_employees WHERE project_id = :P1_PROJECT_ID;
-- Convert shuttle string to an array
l_array := apex_util.string_to_table(:P1_EMPLOYEE_LIST);
-- Insert selected employees
FOR i IN 1..l_array.count LOOP
INSERT INTO project_employees (project_id, employee_id)
VALUES (:P1_PROJECT_ID, l_array(i));
END LOOP;
END;
Make sure the Shuttle item is included in "Items to Submit".
Extensive Example – Assign Courses to a Student
-
Shuttle Item:
P2_COURSE_LIST -
SQL Query:
SELECT course_name, course_id FROM courses ORDER BY course_name -
On Submit Process:
DECLARE l_course_ids apex_application_global.vc_arr2; BEGIN DELETE FROM student_courses WHERE student_id = :P2_STUDENT_ID; l_course_ids := apex_util.string_to_table(:P2_COURSE_LIST); FOR i IN 1..l_course_ids.count LOOP INSERT INTO student_courses (student_id, course_id) VALUES (:P2_STUDENT_ID, l_course_ids(i)); END LOOP; END;
This section demonstrates how to create shuttle item type. In this example, you first create a report and form on the DEPT table that shows which employees are assigned to a given department. Then, you create a shuttle item that lists employees alphabetically to make it easier to assign employees to a department.
To create a shuttle item on a form:
View the page in Page Designer:
On the Workspace home page, click the App Builder icon.
Select an application.
Select a page.
Page Designer appears.
In Page Rendering, right-click region containing the form and select Create Page Item.
In the Property Editor, edit the following attributes:
Identification: Name - Enter the name of this item (for example, PX_EMP_LIST).
Where PX in the item name (for example P2_EMP_LIST) indicates the page on which the item resides.
Identification: Type - Select Shuttle.
In the Property Editor, edit the List of Values attributes:
List of Values: Type - Select SQL Query.
List of Values: SQL Query, enter:
SELECT ename, empno FROM emp ORDER BY 1
In the Property Editor, edit the Source attributes
Source: Type - Select SQL Query (return colon separated value).
Source: Item Source Value - Enter the SQL Query that returns one or more rows to be used as the source for this item value. If the result contains multiple rows then the value from each row is formed into a single colon delimited value. For example:
SELECT empno FROM emp WHERE deptno = :P8_DEPTNO ORDER BY enam
Configuring Page Item Security
To configure page item security:
View the page in Page Designer:
On the Workspace home page, click the App Builder icon.
Select an application.
Select a page.
Page Designer appears.
In either the Rendering tab or the Layout tab, select the item to edit.
The Property Editor displays the item attributes.
Tip:
Attributes are organized in groups. To find a group or attribute, enter keywords in the Filter Properties field, or click Go to Group and select the group.
Find Security. Configure the following Security attributes.
Table 13-2 Property Editor - Page Item, Security
Best Practices
-
Always delete and re-insert values when using many-to-many tables to simplify logic.
-
Use foreign keys and indexes on association tables for better performance.
-
If the list is large, consider using AJAX-based LOVs for better load time.
-
Handle NULL submissions gracefully to avoid losing all assignments unintentionally.
-
Use descriptive labels and instructions to help users understand how the shuttle works.
-
Consider adding a search box or filtering logic in the LOV SQL if the list grows large.
Oracle APEX Documentation
Oracle APEX Shuttle Item Reference
Conclusion
The Shuttle item in Oracle APEX is a powerful and user-friendly control for managing multi-select relationships on forms. Whether you are assigning roles, tagging records, or linking entities, shuttle items provide a clean UI that simplifies the selection process. When combined with PL/SQL processes and well-structured tables, they can handle complex relationships with ease and precision. Mastering the Shuttle control is a great step toward building more interactive and functional APEX applications.
No comments:
Post a Comment