The new combo box in Oracle APEX 23.2 ushers in exciting new possibilities for creating dynamic and user-friendly applications. They are akin to the classic select list and Popup LOV, but they offer a range of unique features that add versatility and functionality to your Oracle APEX applications. In this blog post, we’ll take an in-depth look at the combo box, highlighting its distinct flavors, unique attributes, and improvements it brings to the table.
Understanding the Combo Box
The combo box in Oracle APEX is a game-changer for developers seeking a flexible and interactive input option. It’s similar to the select list but with some significant differences and advantages. With Combobox, users can also type values not listed in the options, providing a dynamic and user-centric experience. The combo box provides two distinct modes, known as Combo Box Many and Combo Box One. The primary difference lies in the cardinality of the selections: Combo Box Many supports multiple selections, while Combo Box One allows only a single choice.
The Key Differentiators
Dynamic Behaviour
The combo box operates dynamically, visually adapting as users interact with it. It offers various options that users can select, enabling single or multiple selections.
Separators
A significant improvement in combo boxes is the ability to use separators to separate values or tags. These separators can be customized, allowing developers to choose symbols or characters to indicate separations. This enhancement ensures a more intuitive user experience, especially when dealing with tags or lists.
Multi-Select Functionality
In the past, when using a Popup LOV, multi-selecting options required cumbersome steps. With the combo box, users can effortlessly select multiple values without the need to reopen the list, even when filtering.
Extensive Features Support
The combo box supports many features, including icons, grouping, HTML, and template directives. Developers can customize how options are displayed using template directives, allowing greater flexibility and user interface control.
Multi-Column Search
When working with a list of values with one or more columns defined, the combo box allows users to search across all defined columns. This makes it easier to find specific values based on various criteria.
Identifying Manual Entries
One of the most notable features is the ability to identify manual entries. This functionality allows values that are not part of the dataset to be sent to the server for further processing, such as adding them to a lookup table or an external dataset. Users can enter values processed separately from the dataset, enhancing data management capabilities.
Creating a Combobox in Oracle APEX 23.2
Creating Combobox One:
To create a combobox one page item, follow these steps:
- In-Page Designer’s Rendering tab, Right-click on Body, and from the context menu, select Create Page Item.
- To create the combo box, you’ll need two items. Name the first item as Pn_COMBOBOX_ONE and the second one as Pn_MANUAL_ENTRIES_ONE. Note that Pn_MANUAL_ENTRIES_ONE is typically a hidden item
- Now, configure the attributes for Pn_COMBOBOX_ONE to make it a combo box.
- Under Identification, set the Type to Combobox.
- Under Settings, for Manual Entries section, enter (or select) item Pn_MANUAL_ENTRIES_ONE
- Under List of Values, choose SQL Query for the Type. You can then copy and paste the SQL query below. Ensure that you have an TAGS table available in your schema.
SELECT DESCRIPTION, ID FROM TAGS
-
- Click Save and Run Page to see the Combobox One in action! You learn how to handle Manual Entries in the Next Section.
Creating Combobox Many:
To create a combobox Many page item, follow these steps:
- In-Page Designer’s Rendering tab, Right-click on Body, and from the context menu, select Create Page Item.
- To create the combo box, you’ll need two items. Name the first item as Pn_COMBOBOX_MANY and the second one as Pn_MANUAL_ENTRIES_MANY.
- Now, configure the attributes for Pn_COMBOBOX_MANY.
- Under Identification, set the Type to Combobox.
- In the Settings,
- Set Multiple Values to Yes
- Set Multi Selection to Yes
- For the Manual Entries section, enter (or select) item Pn_MANUAL_ENTRIES_ONE.
- Under List of Values, choose SQL Query for the Type. You can then copy and paste the SQL query below. Ensure that you have an TAGS table (with Columns ID and Description) available in your schema.
SELECT DESCRIPTION, ID FROM TAGS
-
You learned how to allow manual entries in a combobox with a colon(: ) as separator. In this Section, we will learn how to process these manual entries using a PL/SQL code on a button click.
-
Create a page process. In the page process, add the following PL/SQL code:
declare
l_new_tag_id number;
begin
— loop over any manually-entered tag descriptions
FOR L_NEW_TAG IN (
SELECT
COLUMN_VALUE
FROM
APEX_STRING.SPLIT(:P2_MANUAL_ENTRIES_MANY, ‘:’)
) LOOP
— append the new tag id to the delimited list
insert into tags(description) values (l_new_tag.column_value) returning id into l_new_tag_id;
:P2_COMBOBOX_MANY := :P2_COMBOBOX_MANY || CASE WHEN :P2_COMBOBOX_MANY IS NOT NULL
THEN
‘:’
END || L_NEW_TAG_ID;
END
LOOP;
— clear the manual entries item
:P2_MANUAL_ENTRIES_MANY := null;
end;
Exploring the New Combo Box Features in Oracle APEX > Screenshot 2023-11-09 at 3.59.10 PM.png” data-unresolved-comment-count=”0″ height=”400″ src=”https://confluence.oraclecorp.com/confluence/download/attachments/8236486251/Screenshot%202023-11-09%20at%203.59.10%20PM.png?version=1&modificationDate=1699527724000&api=v2″ title=”Oracle APEX > Exploring the New Combo Box Features in Oracle APEX > Screenshot 2023-11-09 at 3.59.10 PM.png”>
-
Assign the page process to a button. When the user clicks the button, the PL/SQL code will be executed.
-
The PL/SQL code will do the following:
- Insert the manual entries into the tags table.
- Append the newly inserted tag IDs to the :P2_COMBOBOX_MANY item.
- Clear the :P2_MANUAL_ENTRIES_MANY item.
- Click Save and Run Page to see the Combobox Many in action!
Customize with HTML/Template Directives.
You can further define how the choices are rendered using HTML Expression. This attribute supports HTML and template directives.
Conclusion
The introduction of combo boxes in Oracle APEX brings dynamism and user-friendliness to your applications. With dynamic behaviour, custom separators, multi-select options, and support for various features, the combo box is a valuable addition to your development toolkit. Whether you’re dealing with tags, lists of values, or user inputs, the combo box offers an enhanced and flexible solution.
Ready to try it out? Upgrade to Oracle APEX 23.2 and incorporate Combo Boxes into your applications today!