 |
|
APEX check
boxes in reports regions
Oracle Tips by Burleson Consulting |
Using Updateable Reports in APEX
The method described here allows the developer
to create a Report within APEX which can be used to update
information. To start, I’ll explain what a Report is in
APEX. A Report is a region which uses a SQL Select
statement in order to return multiple data records. The data
records will be displayed in a table fashion. Figure 1 shows an
example of a report on a Country code table.
Figure 1
The SQL Statement used to create this report
is:
select ctry_code, ctry_name
from country_codes
order by 1
We want to be able to not only report on the
data in the Country_Code table but we also would like to be able to
modify the information. In order to accomplish this, we will use
some of the API’s delivered with APEX. First we want to be
able to specify which row we would like to modify. So for each
row, we are going to add a checkbox in the first column. To
accomplish this, we need to utilize a APEX API called htmldb_item.
This API allows us to include different item types (checkboxes,
select lists, text items) to reports.
We need to modify the SQL statement to include
the API call. We also will modify the Country Name column to be a
text item. This will allow the user to change the name of the
Country. Here is the modified SQL statement:
select
htmldb_item.checkbox(1,rownum) " ",
x.code,
x.country
from (
select
htmldb_item.hidden(2,ctry_code) code,
htmldb_item.text(3,ctry_name) country
from country_codes
) x
order
by 2
The new report is shown in Figure 2.
Figure 2.
The new Select statement has added the checkbox
in the first column. It has also has added a hidden column which
contains our primary key value for the record. Lastly, it has
changed the Country column into a text field where the user can
change the value.
The calls to htmldb_item also include a
number. When APEX processes the select statement, the values in
the report are kept in an array. The array is referenced by the
syntax G_Fxx where xx is the number. So in this example, 3 arrays
are created, one for the checkbox (to signify if it has been checked
or not), one for the hidden value and one for the Country names.
The 3 arrays are G_F01, G_F02 and G_F03. APEX supports 50
arrays, G_F01 through G_F50, so we can have up to 50 columns in a
report. These are referred to through another API, htmldb_application.
Now we need to add a buttons and a processes to
perform the actual ‘update’ or ‘delete’ that we wish to perform on
the row. For the ‘Update’ process, we will add the code:
for i
in 1..htmldb_application.g_f01.count
loop
update country_codes
set ctry_name =
htmldb_application.g_f03(htmldb_application.g_f01(i))
where ctry_code =
htmldb_application.g_f02(htmldb_application.g_f01(i));
end
loop;
As you can see from this code, the
htmldb_application API also gives you a count. This count will
equal the number of records which are checked. So if rows 1, 4 and
10 are checked, this count will be equal to 3. But the reference
to the values in the array G_F01 will be the number of the array
index, so htmldb_application.G_F01(1) will equal 1,
htmldb_application.G_F01(2) will equal 4 and
htmldb_application.G_F01(3) will equal 10.
Our ‘Delete’ process will be coded in much the
same fashion. Here is the code:
for i
in 1..htmldb_application.g_f01.count
loop
delete from country_codes
where ctry_code =
htmldb_application.g_f02(htmldb_application.g_f01(i));
end
loop;
With the new buttons added, our new screen will
appear as in Figure 3.
Figure 3
Now we need to add one last process to the
screen, the Insert. For the insert we will have to add some text
entry fields, a button and a process. For the entry fields, 2
have been created, Code and Country Name. Figure 4 shows the
screen with the new fields and button.
Figure 4.
The process to insert the new record will look
like this:
begin
insert into country_codes (ctry_code,ctry_name)
values (:P1_COUNTRY_CODE, :P1_COUNTRY_NAME);
/*
Clear the values */
:P1_COUNTRY_CODE := null;
:P1_COUNTRY_NAME := null;
end;
By using the APEX APIs, htmldb_item and
htmldb_application you can build powerful updating and deletion
routines. Additionally the concepts described here also can be
adapted to a web-store type application where the customer will
check each item they wish to purchase.
APEX support:
 |
For APEX development support just call to gat an
Oracle Certified professional for all APEX development
projects. |
APEX book and code samples:
|
and include the URL for the page.