In the previous recipe, we saw that we can use APEX_ITEM and APEX_APPLICATION to programmatically display and control items. We can also create and manipulate a checkbox. In this recipe, we will show you how to use the APEX_ITEM.CHECKBOX function and how to avoid problems. We will use the previous recipe and extend it with a checkbox.
A checkbox is a special kind of item on a webpage. It can either be checked or unchecked. But APEX doesn't know the unchecked state and therefore it replaces the unchecked value with null. Since APEX_APPLICATION is an array, null values will not be stored in APEX_APPLICATION. But if you try to look up the value of a certain row, you might get a no data found error.
Make sure you have a working events page from the previous recipe. Add a column to the table APP_EVENTS by entering the following in SQLplus:
Alter table app_events Add oracle_event varchar2(1);
[1346_07_03.txt]
We will make a change to the page from the previous recipe.
- Go to the page you created in the previous recipe.
- In the regions section, click the Report region link.
- Replace the region source with the following:
select apex_item.hidden(1,id)||apex_item.text(2,event) appevent , apex_item.date_popup(3,rownum,event_date,'dd-mm-yyyy') event_date , apex_item.text(4,location) location , apex_item.text(5,event_type) event_type , apex_item.checkbox(6,id,decode(nvl(oracle_event,'N'),'Y','CHECKED',null)) oracle_event from app_events
[1346_07_04.txt]
The difference with the query in the previous recipe is that there is one extra column: APEX_ITEM.CHECKBOX. The third argument is the decode, which sets the checkbox to be checked if the value is 'Y'. If the value of ORACLE_EVENT is 'N' or empty, the checkbox is unchecked. However, in APEX, you cannot speak of an unchecked status. The checkbox is either checked or null. This is an HTML restriction. And this is exactly the point you need to pay attention to. However, besides the checked status, a checkbox can also get a value and in this query the checkbox gets the value of the ID column of the APP_EVENTS table. We will use that later on in the recipe.
- Click the Apply changes button.
- Click on the Report link in the regions section.
- Click on the Edit icon (the pencil) next to the column
ORACLE_EVENT. - In the column attributes section, select Standard report column in the Display as select list.
- Click the Apply changes button.
- Click the Apply changes button again.
- In the processes section in the middle of the screen, click the
SAVE_RESULTSlink. - Replace the code in the process text area with the following:
declare type t_event is table of varchar2(10) index by binary_integer; v_event t_event; l_index number(3); l_oracle_event varchar2(10); begin -- use a pl/sql table to put the 'Y' in the -- right index for a in 1..apex_application.g_f06.count loop if apex_application.g_f06.exists(a) then l_index := to_number(apex_application.g_f06(a)); v_event(l_index) := 'Y'; end if; end loop; -- -- Loop through the records for i in 1..apex_application.g_f01.count loop -- if the pl/sql table contains a value for this -- row, oracle_event has to be set to 'Y'. Otherwise -- leave oracle_event to null if v_event.exists(to_number(apex_application.g_f01(i))) then l_oracle_event := v_event(to_number(apex_application.g_f01(i))); else l_oracle_event := null; checkboxcheckboxreading, programmaticallyend if; -- update app_events set event = apex_application.g_f02(i) , event_date = to_date(apex_application.g_f03(i), 'dd-mm-yyyy') , location = apex_application.g_f04(i) , event_type = apex_application.g_f05(i) , oracle_event = l_oracle_event where id = to_number(apex_application.g_f01(i)); end loop; end;
[1346_07_05.txt]
This code is different from the code in the previous recipe. You can see that the ORACLE_EVENT is added to the update statement. Furthermore, you can see that a PL/SQL table (V_EVENT) is used to store the checked values. APEX_ITEM.CHECKBOX only stores the checked values. This means that when the checkboxes of rows 1, 3 and 5 are checked, the array for the checkbox, apex_application.g_f06, contains only three values. But when you try to read the value of the fifth row, you will get an error message. Contrary to, let's say event (with the apex_application.g_f03 array), you cannot get the value of the checkbox of a certain row.
To make this clear, have a look at the following table. You can see that the arrays for the ID and the EVENT column contain the correct values for each row. But the array for the checkbox shows the checked state for the first three rows where it should be checked for the rows 1, 3, and 5.
|
Index value |
Value of ID column Array |
Value of EVENT column array |
Value of Checkbox array |
|---|---|---|---|
|
1 |
2 |
ODTUG… |
Checked (1) |
|
2 |
3 |
OBUG… |
Checked (3) |
|
3 |
4 |
OOW… |
Checked (5) |
|
4 |
5 |
Dinner… | |
|
5 |
6 |
Knowledge session… |
To avoid this, we use a PL/SQL table (V_EVENT) that only stores a value for the rows that contain a checked checkbox. We already saw that in the query each checkbox gets the value of the ID column. This value is used as index for the PL/SQL table V_EVENT. Later on in the code, where APEX loops through the array for the ID column, APEX checks whether V_EVENT contains a value for that particular index (ID). If so, the column ORACLE_EVENT should be updated with 'Y'.
- Click the Apply changes button.
- The page is ready. Run it, check some checkboxes, and see if it works.
