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 







Oracle Application Express (APEX): Repeating updateable screen items

Oracle Tips by Burleson Consulting

All online application must be able to model repeating groups of data and this is especially common in  data entry forms where the end-user must enter variable occurrence screen items.  Just a few examples include:

  • Welfare application - Repeating list of dependents

  • Order Form - Repeating list of items

  • Security clearance application - repeating list of previous addresses

Languages have had this construct since the later 1950's, such as the Cobol syntax to allow repeating groups in online screens:


So, how do we model repeating groups in APEX?  The APEX reports already have a superb built-in pagination functionality, But we need to model repeating sets on a screen that can be:

  • Addressed by subscripts (e.g. year(i), skill(i))

  • Allows for more entries

These is an example of an updateable repeating group in an APEX screen.  Note the button "Create and save more" that scrolls the repeating item list:

This repeating list of update screen items is easily implemented as an APEX report with a few simple buttons:

We start my examining the "before header" process called "create collection".  Here we see the wwv_flow_collection PL/SQL package invocation calling the create_or_truncate_collection stored procedure:

We see the named collection "MULTI_SKILLS" and a FOR loop calling the wwv_flow_collection packages add_member procedure, specifying the p_collection_name, and the three repeating updateable screen item values.

Next, let's look at the APEX report that implements the screen with repeating update items:

In the APEX reports region definition we see the region source using the htmldb_item package and the select_list_from_lov and select_list_from lov_x1 procedures.  The region definition selects these values from the wwv_flow_collections table that we defined in the "before header" processing:

Now lets click the "report:" link from the "page rendering" section of the report region screen.

Here we see the heading definitions for the repeating groups of data items and see their screen reference names (year, month, skill) that will be referenced via subscripts (e.g. skill(i)):

The code for updateable repeating groups

Populating an updateable report is easy and here is a sample of actual code.  Note the in-line view to populate the values and the calls to the htmldb_item.text procedure:

   htmldb_item.checkbox(11,rownum) " ",
   select htmldb_item.hidden(13,a.gen_adr_id)||
   address_name   addr_type,
   htmldb_item.text(14,a.adr_address_line1,25,240) address,
   htmldb_item.text(15,a.adr_town,20,240) town,
   htmldb_item.text(16,a.adr_postal_cd,9,9) postal
      person_address g,
      master_address a
      g.gen_adr_id = a.gen_adr_id
      g.gen_person_id = :P2_PERSON_ID
      g.expiry_date = active()
   ) x
order by 2

What this does is set up 5 arrays of one column each. You canít create an array of more than one column. So each array will hold the values of the information you select from the table. In this case, we have the 5 arrays, designated by the 11, 13, 14, 15 and 16, are referred to in APEX as
htmldb_application.G_F11, htmldb_appliation.G_F13 and so on. The country_name and isle_state_prov_name just display only values.

Now the 2 things of importance in this query is the htmldb_item.checkbox(11,rownum) and the htmldb_item.hidden(13,a.gen_adr_id). The htmldb_item.checkbox(11,rownum) is used to set up the checkboxes, one for each row returned by the query, with the rownum of the record. The htmldb_item.hidden(13,a.gen_adr_id) is used to set up a hidden item which will hold the primary key for the address record.

When APEX does the processing, it recognizes which checkboxes are checked and returns the corresponding values in the array 11, because it is part of the main select. For example, if checkboxes 1, 3 and 5 are checked, array
htmldb_application.G_F11 will have the rownums for the row corresponding to checkboxes 1, 3 and 5.

Updating a APEX repeating screen display

Here is the code to take the values from the updateable screen items and place them into an Oracle table:

FOR i in 1..htmldb_application.G_F11.COUNT
   update gen_address
   set adr_address_line1 = htmldb_application.g_f14(htmldb_application.g_f11(i)),
   adr_town = htmldb_application.g_f15(htmldb_application.g_f11(i)),
   adr_postal_cd = htmldb_application.g_f16(htmldb_application.g_f11(i))
   where gen_adr_id = htmldb_application.g_f13(htmldb_application.g_f11(i));


Here we have a loop which will loop 3 times in our example. Htmldb_application.G_F11.COUNT function returns 3, the count of values in the array. For each iteration, the
htmldb_application.g_f11(i) will return the rownum of the checked record. Now we have the subscript for the arrays with the real values. This is a sample delete process for repeating update screen items.

   delete gen_per_address
      gen_adr_id = htmldb_application.g_f13(htmldb_application.g_f11(i));

We now have a APEX updateable report where our end-user can enter repeating values directly into the APEX screen, and we can reference each item by subscripts to extract the repeating items from the screen for storing into Oracle.  For more details on this concept, see the book Easy Oracle HTML-DB Application Express .

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