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
|| '" attrNum="2"'
|| '" attrNum="3"'
'select E.ENAME d, E.EMPNO r from emp e where E.EMPNO != '
'class="updateableIR" seqid="' || ac.seq_id || '" attrNum="4"',
'- Manager -'
|| '" attrNum="5"'
|| '" attrNum="6"'
'SELECT d.dname d, d.deptno r FROM dept d',
|| '" attrNum="7"',
'- Department -'
FROM apex_collections ac, emp e
WHERE ac.collection_name = :p2300_collection_name AND ac.c001 = e.empno
- 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
Computation Point: Before Header
Computation Type: Static Assignment
- Create a Page Process (PL/SQL)
Name: Load Collection
Process Point: On Lead - Before Header
-- This creates the collection if it isn't created yet.
v_reset_flag VARCHAR2 (1) := 'N';
-- Create collection if it does not exist or reset collection required
(p_collection_name => v_collection_name) =
OR v_reset_flag = 'Y'
(p_collection_name => :p2300_collection_name,
p_query => q'! SELECT empno,
TO_DATE (hiredate, 'DD-MON-YYYY'),
FROM emp !',
p_generate_md5 => 'YES'
); -- Generated md5 is important to help identify changed columns
- Create HTML Region to store JS code:
Note: This uses jQuery and Tyler Muth's jApex plugin. You'll have to upload the .js files as static files in Shared Components.
- Create Application Process:
Process Point: On Demand
(p_collection_name => apex_application.g_x01,
p_seq => apex_application.g_x02,
p_attr_number => apex_application.g_x03,
p_attr_value => apex_application.g_x04
- Query to see which rows were changed
SELECT ac.collection_name, ac.seq_id, ac.c001, ac.c002, ac.c003, ac.c004,
ac.c005, ac.c006, ac.c007, ac.md5_original,
FROM apex_collections ac
WHERE collection_name = :p2300_collection_name