Working with Multi-Select Items on Oracle APEX

The following post was motivated by a recent thread in the Oracle Application Express Community about how to convert single select to multi select field.

The Problem

When working with items that allow multiple selections in Oracle APEX such as checkbox, list manager, radio group, or shuttle, these values are stored in a single colon-delimited string.

Storing the value in this format into the database does not comply with the database normalization forms and is considered a bad practice.

Below I explain my approach to solve this problem. I created a demo application where you can try the solution and/or copy the script to generate the required database objects in your own environment to follow along with this example. Login using the credentials Demo/Demo.

The Solution

In this example, let’s assume we have the following data model:

Since a movie can have one or more categories, and a category can be assigned to zero or more movies, we will use an associate table (MOVIES_CATEGORIES) to link the values between these two tables (MOVIES and CATEGORIES). Instead of storing CATEGORIES as a column on the MOVIES table.

The first step is to create a "Form on a Table with Report" based on the MOVIES table. Click on "Create Page", select "Form" and "Form on a Table with Report". Modify the following values and leave the rest as default:

Under Report Page

Implementation: Classic
Page Name: Movies Report

Under Data Source

Table / View Name: MOVIES

Under Form Page

Page Mode: Modal Dialog
Page Name: Movies Form
Primary Key Type: Select Primary Key Column(s)
Primary Key Column 1: ID (Number)
Select Columns: NAME (Varchar2)

You will end up with two pages, e.g.:

• Page 2 – Movies Report
• Page 3 – Movies Form

On the Movies Report, change the SQL query as follow:

	select
	m.id,
	m.name,
	listagg(c.category, ', ') within group (order by c.category) as categories
	from
	movies m
	left outer join movies_categories mc on m.id = mc.movie_id
	left outer join categories c on mc.category_id = c.id
	group by
	m.id,
	m.name
	order by
	m.name asc

view raw page_2_movies_report.sql hosted with ❤ by GitHub

On the Movies Form, go to Processes and select "Process Row of MOVIES". Under Settings, set the "Return Key Into Item" to your page item holding the Primary Key ID, e.g. P3_ID.

Create a new Page Item "P3_CATEGORIES". Make it a Shuttle. Under the List of Values properties, specify the following:
Type: SQL Query
SQL Query: select category d, id r from categories order by category

Create a new process "Process Categories", right after "Process Row of MOVIES" and before "reset page" with the following PL/SQL Code:

	declare
	l_vc_arr2 apex_application_global.vc_arr2;
	begin
	l_vc_arr2 := apex_util.string_to_table(:P3_CATEGORIES);
	delete from movies_categories
	where movie_id = :P3_ID;
	forall i in l_vc_arr2.first..l_vc_arr2.last
	insert into movies_categories (movie_id, category_id)
	values (:P3_ID, l_vc_arr2(i));
	end;

view raw page_3_process_categories.sql hosted with ❤ by GitHub

Finally, create a fetch process "Fetch Categories" on the Pre-Rendering section, right after "Fetch Row from MOVIES" to retrieve the category information for existing records. This process should have a server-side condition of "Item is NOT NULL" = P3_ID and the following PL/SQL Code:

	declare
	l_vc_arr2 apex_application_global.vc_arr2;
	begin
	select category_id
	bulk collect
	into l_vc_arr2
	from movies_categories
	where movie_id = :P3_ID
	order by 1;
	:P3_CATEGORIES := apex_util.table_to_string (
	p_table => l_vc_arr2,
	p_string => ':'
	);
	end;

view raw page_3_fetch_categories.sql hosted with ❤ by GitHub

That's it!

Final Words

This demo was designed with Oracle Database 11g and APEX 5.0 in mind. In Oracle Database 12c, you can take advantage of Identity Columns when creating new tables. Also, the latest APEX 5.1 comes with a new package APEX_STRING that includes two functions (SPLIT and JOIN) that can be used instead of the package APEX_UTIL and the functions STRING_TO_TABLE and TABLE_TO_STRING.

Regardless of the method you use, it’s important to always keep your database normalized to reduce data redundancy and improve data integrity.