![](/uploads/1/2/7/6/127638871/358162581.jpg)
Search BC Oracle Sites!APEX checkboxes in reports regionsOracle Tips by Burleson ConsultingUsing Updateable Reports in APEXThe method described here allows the developerto create a Report within APEX which can be used to updateinformation. To start, I’ll explain what a Report is inAPEX.
A Report is a region which uses a SQL Selectstatement in order to return multiple data records. The datarecords will be displayed in a table fashion. Figure 1 shows anexample of a report on a Country code table.Figure 1The SQL Statement used to create this reportis:select ctrycode, ctrynamefrom countrycodesorder by 1We want to be able to not only report on thedata in the CountryCode table but we also would like to be able tomodify the information. In order to accomplish this, we will usesome of the API’s delivered with APEX.
First we want to beable to specify which row we would like to modify. So for eachrow, we are going to add a checkbox in the first column.
Application Express:: Unable To Delete Multi Row Update Process? I have created a simple page with a tabular form built on a view. The view is on top of a. Instead, what ended up working (while retaining all the benefits of a standard APEX tabular form) was to create a row-level process instead. Here’s some example code that I put in this APEX process that interfaces with my APEX API.
Toaccomplish this, we need to utilize a APEX API called htmldbitem.This API allows us to include different item types (checkboxes,select lists, text items) to reports.We need to modify the SQL statement to includethe API call. We also will modify the Country Name column to be atext item.
This will allow the user to change the name of theCountry. Here is the modified SQL statement:selecthtmldbitem.checkbox(1,rownum) ' ',x.code,x.country from ( selecthtmldbitem.hidden(2,ctrycode) code,htmldbitem.text(3,ctryname) country from countrycodes) x orderby 2The new report is shown in Figure 2.Figure 2.The new Select statement has added the checkboxin the first column. It has also has added a hidden column whichcontains our primary key value for the record. Lastly, it haschanged the Country column into a text field where the user canchange the value.The calls to htmldbitem also include anumber.
When APEX processes the select statement, the values inthe report are kept in an array. The array is referenced by thesyntax GFxx where xx is the number.
So in this example, 3 arraysare created, one for the checkbox (to signify if it has been checkedor not), one for the hidden value and one for the Country names.The 3 arrays are GF01, GF02 and GF03. APEX supports 50arrays, GF01 through GF50, so we can have up to 50 columns in areport. These are referred to through another API, htmldbapplication.Now we need to add a buttons and a processes toperform the actual ‘update’ or ‘delete’ that we wish to perform onthe row.
For the ‘Update’ process, we will add the code: for iin 1.htmldbapplication.gf01.count loopupdate countrycodesset ctryname =htmldbapplication.gf03(htmldbapplication.gf01(i))where ctrycode =htmldbapplication.gf02(htmldbapplication.gf01(i)); endloop;As you can see from this code, thehtmldbapplication API also gives you a count. This count willequal the number of records which are checked. So if rows 1, 4 and10 are checked, this count will be equal to 3. But the referenceto the values in the array GF01 will be the number of the arrayindex, so htmldbapplication.GF01(1) will equal 1,htmldbapplication.GF01(2) will equal 4 andhtmldbapplication.GF01(3) will equal 10.Our ‘Delete’ process will be coded in much thesame fashion. Here is the code: for iin 1.htmldbapplication.gf01.count loopdelete from countrycodeswhere ctrycode =htmldbapplication.gf02(htmldbapplication.gf01(i)); endloop;With the new buttons added, our new screen willappear as in Figure 3.Figure 3Now we need to add one last process to thescreen, the Insert.
For the insert we will have to add some textentry fields, a button and a process. For the entry fields, 2have been created, Code and Country Name. Figure 4 shows thescreen with the new fields and button.Figure 4.The process to insert the new record will looklike this: begininsert into countrycodes (ctrycode,ctryname)values (:P1COUNTRYCODE,:P1COUNTRYNAME); /.Clear the values./:P1COUNTRYCODE:= null;:P1COUNTRYNAME:= null; end;By using the APEX APIs, htmldbitem andhtmldbapplication you can build powerful updating and deletionroutines.
A colleague had a requirement where he had over 10,000 rows of data which had to be updateable. Using Interactive Reports (IR) was the preferred approach as it would allow users to filter the data to modify the rows they wanted to before submitting the page. Tabular forms wouldn’t work since the page would be to large. This is the solution that I proposed to make “Updateable IRs”.
The following solution will work with IRs and standard reports with pagination. If the users applies filters or paginates to another set of data, the changes they make will remain in the collection. This is using a similar technique that I wrote about for APEX Report with checkboxes (advanced). To summarize this process:
- Store the current query data into a collection as well as the md5 checksum.
- Build an IR against the collection and use APEX_ITEMs to display input fields.* When a user changes a field, we submit that change to the collection.
- Once the user is done with their changes you’ll need to process the collection as required. In the last step in this example I have a query that will help identify changed rows.
You can do a lot with this approach but if you don’t have an urgent need I’d suggest holding off until APEX 4.0. They’re some security issues that would need to be addressed before launching this code in a public application. I didn’t include the security updates in this example since I did not want to lose scope of the base functionality. Updating the code to make it secure shouldn’t be too difficult.
Here’s the link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2300
- Create IR Report Region
Note: You can use this for regular reports with pagination as well
Note: You can use this for regular reports with pagination as well
- Create Region Items
- Hidden & Protected: P2300_COLLECTION_NAME
- Create Page Computation
Note: This is just to set the collection name. You can call it whatever you want
Note: This is just to set the collection name. You can call it whatever you want
Item: P2300_COLLECTION_NAME
Computation Point: Before Header
Computation Type: Static Assignment
Computation: P2300_IR_COLLECTION
Computation Point: Before Header
Computation Type: Static Assignment
Computation: P2300_IR_COLLECTION
- Create a Page Process (PL/SQL)
Name: Load Collection
Process Point: On Lead - Before Header
Process Point: On Lead - Before Header
- Create HTML Region to store JS code:
Note: This uses jQuery and Tyler Muth’sjApex plugin. You’ll have to upload the .js files as static files in Shared Components.
Note: This uses jQuery and Tyler Muth’sjApex plugin. You’ll have to upload the .js files as static files in Shared Components.
- Create Application Process:
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand
![Multi Multi](http://www.thinkoholic.com/wordpress/wp-content/uploads/2019/03/190311-oracle-apex-update-table-column-based-on-list-of-ids-from-input-field.jpg)
![Apex multi row update processor Apex multi row update processor](http://2.bp.blogspot.com/_tffIKLFlRlY/TI4B31cuUgI/AAAAAAAAF18/O4_vG7eATgI/s1600/Screen+shot+2010-09-13+at+12.36.52.png)
- Query to see which rows were changed
![](/uploads/1/2/7/6/127638871/358162581.jpg)