Use Values from APEX Checkbox with IN Opearator in SQL where clause

In my Oracle Apex application, I am using a checkbox to filter query results. The idea query should look like:

select * from emp e where e.job in :P1_JOB_CHECKBOX;

However when I put this query string in report region of my application, nothing shows up. Upon checking the return value of P1_JOB_CHECKBOX, I noticed the returning string is "MANAGER:CLERK".

Solution

Use PL/SQL process to put jobs into an Apex collection.

1. Define a process "Process Checkboxes" at Page Processing section of  page 1 using "On Submit - Before Computations and Validations" as process point.

2. Put the following into the source section of the process

declare
    JOB_Checkbox_array apex_application_global.vc_arr2;

begin
    JOB_Checkbox_array := apex_util.string_to_table (:P1_JOB_CHECKBOX, ':');
    apex_collection.create_or_truncate_collection ('P1_JOB');
    apex_collection.add_members ('P1_JOB', JOB_Checkbox_array);

end;

3. Modify the query string in active report region

select * from emp e where e.job in (select c001 from apex_collections where collection_name = 'P1_JOB');