Updateable Interactive Report for APEX

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

SELECT e.empno,
    apex_item.text (2,
                    ac.c002,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="2"'
                   ) ename,
    apex_item.text (3,
                    ac.c003,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="3"'
                   ) job,
    apex_item.select_list_from_query
       (4,
        ac.c004,
           'select E.ENAME d, E.EMPNO r from emp e where E.EMPNO != '
        || e.empno,
        'class="updateableIR" seqid="' || ac.seq_id || '" attrNum="4"',
        'YES',
        NULL,
        '- Manager -'
       ) mgr,
    apex_item.text (5,
                    ac.c005,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="5"'
                   ) hiredate,
    apex_item.text (6,
                    ac.c006,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="6"'
                   ) comm,
    apex_item.select_list_from_query
                       (7,
                        ac.c007,
                        'SELECT d.dname d, d.deptno r FROM dept d',
                           'class="updateableIR" seqid="'
                        || ac.seq_id
                        || '" attrNum="7"',
                        'YES',
                        NULL,
                        '- Department -'
                       ) deptno
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

Item: P2300_COLLECTION_NAME
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

-- This creates the collection if it isn't created yet.
DECLARE
v_collection_name             apex_collections.collection_name%TYPE
                                                 := :p2300_collection_name;
v_reset_flag                  VARCHAR2 (1) := 'N';
BEGIN
-- Create collection if it does not exist or reset collection required
IF    apex_collection.collection_exists
                                   (p_collection_name            => v_collection_name) =
                                                                     FALSE
  OR v_reset_flag = 'Y'
THEN
 apex_collection.create_collection_from_query
   (p_collection_name            => :p2300_collection_name,
    p_query                      => q'! SELECT empno,
    ename,
    job,
    mgr,
    TO_DATE (hiredate, 'DD-MON-YYYY'),
    comm,
    deptno
FROM emp !',
    p_generate_md5               => 'YES'
   );        -- Generated md5 is important to help identify changed columns
END IF;
END;

- 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:
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand

-- AP_UPDATE_COLLECTION_CELL
BEGIN
apex_collection.update_member_attribute
                             (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
                             );
END;

- 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,
    CASE
      WHEN apex_collection.get_member_md5
                                       (:p2300_collection_name,
                                        ac.seq_id) =
                                                ac.md5_original
        THEN 'NO'
      ELSE 'YES'
    END row_change
FROM apex_collections ac
WHERE collection_name = :p2300_collection_name