Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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) " ",
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
  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
  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:

 insert into country_codes (ctry_code,ctry_name)
 /*  Clear the values */
 :P1_COUNTRY_CODE := null;
 :P1_COUNTRY_NAME := null;

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:

Easy Oracle HTML-DB Application Express
Create Dynamic HTML with Oracle

Includes online APEX code depot

Buy it now for 30% off - Only $27.95




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational