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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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:

"DEPENDENT-LIST OCCURS BETWEEN 0 AND 40 TIMES DEPENDING ON DEPENDENT-COUNT."

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:

SELECT
   htmldb_item.checkbox(11,rownum) " ",
   x.addr_type,
   x.address,
   x.town,
   x.postal,
   x.country
from
   (
   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
   from
      person_address g,
      master_address a
   where
      g.gen_adr_id = a.gen_adr_id
   and
      g.gen_person_id = :P2_PERSON_ID
   and
      g.expiry_date = active()
   ) x
order by 2
 
SEE CODE DEPOT FOR FULL SCRIPTS

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
LOOP
   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));
END LOOP;

SEE CODE DEPOT FOR FULL SCRIPTS

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.

FOR i in 1..HTMLDB_APPLICATION.G_F11.COUNT
LOOP
   delete gen_per_address
   where
      gen_adr_id = htmldb_application.g_f13(htmldb_application.g_f11(i));
END LOOP;
 
SEE CODE DEPOT FOR FULL SCRIPTS
 

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 performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.