ORACLE apex - looping through checkbox items using PL/SQL

Question

I have a checkbox on my page P3_Checkbox1 that is populated from the database. How can I loop through all the checked values of my checkbox in PL/SQL code?

I assume that APEX_APPLICATION.G_F01 is used only by sql generated checkboxes and I cannot use it for regular checbox as it would not populate G_Fxx array.

Answer 1

I think I understand now what you're saying.

For example, suppose that the P3_CHECKBOX1 checkbox item allows 3 values (display/return):

• Absent: -1
• Unknown: 0
• Here: 1

I created a button (which will just SUBMIT the page) and two text items which will display checked values: P3_CHECKED_VALUES and P3_CHECKED_VALUES_2.

Then I created a process which fires when the button is pressed. The process looks like this (read the comments as well, please):

begin
  -- This is trivial; checked (selected) values are separated by a colon sign, 
  -- just like in a Shuttle item
  :P3_CHECKED_VALUES := :P3_CHECKBOX1;
  
  -- This is what you might be looking for; it uses the APEX_STRING.SPLIT function
  -- which splits selected values (the result is ROWS, not a column), and these
  -- values can then be used in a join or anywhere else, as if it was result of a
  -- subquery. The TABLE function is used as well.
  -- LISTAGG is used just to return a single value so that I wouldn't have to worry
  -- about TOO-MANY-ROWS error.
  with 
  description (code, descr) as
    (select -1, 'Absent'  from dual union all
     select 0 , 'Unknown' from dual union all
     select 1 , 'Here'    from dual),
  desc_join as
    (select d.descr
     from description d join (select * from table(apex_string.split(:P3_CHECKED_VALUES, ':'))) s
     on d.code = s.column_value
    )
  select listagg(j.descr, ' / ') within group (order by null) 
    into :P3_CHECKED_VALUES_2
  from desc_join j;
end;

Suppose that the Absent and Unknown values have been checked. The result of that PL/SQL process is:

P3_CHECKED_VALUES   = -1:0
P3_CHECKED_VALUES_2 = Absent / Unknown

You can rewrite it as you want; this is just one example.

Keywords:

• APEX_STRING.SPLIT
• TABLE function

Edit: loop through selected values

Looping through those values isn't difficult; you'd do it as follows (see the cursor FOR loop):

declare
  l_dummy number;
begin
  for cur_r in (select * From table(apex_string.split(:P3_CHECKED_VALUES, ':'))) 
  loop
    select max(1)
      into l_dummy
      from some_table where some_column = cur_r.column_value;
      
    if l_dummy is null then
       -- checked value does not exist
       insert into some_table (some_column, ...) valued (cur_r.column_value, ...);
    else
       -- checked value exists
       delete from some_table where ...
    end if;
  end loop;
end;

However, I'm not sure what you meant by saying that a "particular combination is in the database". Does it mean that you are storing colon-separated values into a column in that table? If so, the above code won't work either because you'd compare, for example

• -1 to 0:-1 and then
• 0 to 0:-1

which won't be true (except in the simplest cases, when checked and stored values have only one value).

Although Apex' "multiple choices" look nice, they can become a nightmare when you have to actually do something with them (like in your case).

Maybe you should first sort checkbox values, sort database values, and then compare those two strings.

It means that LISTAGG might once again become handy, such as

listagg(j.descr, ' / ') within group (order by j.descr) 

Database values could be sorted this way:

SQL> with test (col) as
  2    (select '1:0' from dual union all
  3     select '1:-1:0' from dual
  4    ),
  5  inter as
  6    (select col,
  7            regexp_substr(col, '[^:]+', 1, column_value) token
  8     from test,
  9          table(cast(multiset(select level from dual
 10                              connect by level <= regexp_count(col, ':') + 1
 11                             ) as sys.odcinumberlist))
 12    )
 13  select
 14    col source_value,
 15    listagg(token, ':') within group (order by token) sorted_value
 16  from inter
 17  group by col;

SOURCE SORTED_VALUE
------ --------------------
1:-1:0 -1:0:1
1:0    0:1

SQL>

Once you have them both sorted, you can compare them and either INSERT or DELETE a row.