This example shows how to manage values in a junction table based on a multiple list of values item.

Here are the base tables used in this example. Note that triggers and foreign key relationships are not shown.

CREATE TABLE  PUB_J_PUBL_SUBJECT 
   (PUB_PUBLICATIONS_ID NUMBER(*,0), 
    PUB_SUBJECTS_ID NUMBER(*,0), 
    CREATED_BY VARCHAR2(30) NOT NULL ENABLE, 
    CREATED_DATE DATE NOT NULL ENABLE, 
    MODIFIED_BY VARCHAR2(30), 
    MODIFIED_DATE DATE, 
     CONSTRAINT PUB_J_PUBL_SUBJECT_PK 
         PRIMARY KEY (PUB_PUBLICATIONS_ID, PUB_SUBJECTS_ID);
 
CREATE TABLE  PUB_SUBJECTS 
   (ID NUMBER(*,0), 
    SUBJECT_NAME VARCHAR2(60) NOT NULL ENABLE, 
    CREATED_BY VARCHAR2(30) NOT NULL ENABLE, 
    CREATED_DATE DATE NOT NULL ENABLE, 
    MODIFIED_BY VARCHAR2(30), 
    MODIFIED_DATE DATE,

Create a page item called P9_SUBJECTS. In this case I am using Select2 APEX Plugin with “Multi-Value Select Listâ€, however it could also be Checkboxes or Shuttle, as long as the result is a colon separated list of values.

Add list of values to P9_SUBJECTS as

select Initcap(a.subject_name) d, a.id r
from pub_subjects a
order by 1

Add source to P9_SUBJECTS (SQL Query returns single value) which creates a list of currently assigned subjects as colon separated list.

SELECT LISTAGG(pub_subjects_id, ':') WITHIN GROUP (ORDER BY pub_subjects_id) ids
FROM pub_j_publ_subject
where pub_publications_id = nvl(:P9_ID,0)

Add an after submit page process to insert or delete the appropriate rows in the junction table (pub_j_publ_subject) based on the list of values

DECLARE
 
l_str varchar2(100);
l_str_delete varchar2(100); 
 
BEGIN
 
l_str := :P9_SUBJECTS;
l_str_delete := l_str || ':0:';
 
-- Delete rows 
delete from pub_j_publ_subject 
where pub_publications_id = :P9_ID
and pub_subjects_id not in (select regexp_substr(l_str_delete,'[^:]+', 1, level) from dual
  connect by regexp_substr(l_str_delete, '[^:]+', 1, level) is not null);
   
-- Insert rows<span id="_mce_caret" data-mce-bogus="true"></span>
insert into pub_j_publ_subject (pub_publications_id, pub_subjects_id) 
select :P9_ID, id 
from pub_subjects where id in
(select regexp_substr(l_str,'[^:]+', 1, level) from dual
  connect by regexp_substr(l_str, '[^:]+', 1, level) is not null)
and id not in
(select pub_subjects_id from pub_j_publ_subject where pub_publications_id = :P9_ID);
 
END;

For more information on how regex_substr is being used here see How to split comma separated string and pass to IN clause of select statement.