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, CONSTRAINT PUB_SUBJECTS_PK PRIMARY KEY (ID);
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.