APEX Collections with Interactive GRID

Imaginations are the key of special requirements, specially when are you working with dynamic customers who are always open for broad mind suggestions.

Working with apex collection gives you opportunity to store values in session without save in database until last hit on SAVE or CREATE button of record. Fetch data or create records and store in apex_collections, do manipulations with data as per steps user go through. When user is happy with the record set then only hit Oracle table and save data. For more information on apex_collection click here.

Step 1: Create master page as normal and child page as popup.
Master page creates on Table1 DML process. Child page will create on Interactive Grid based on apex collection.

Step 2: Initialize collection and add members in collection

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
        p_collection_name => 'SAMPLE_NAME');
        
for i in (select  ID1, ID2, NUMBER1, DATE1, CHARACTER1 FROM TABLE2 where ID2 = :PX_ITEM) loop

    APEX_COLLECTION.ADD_MEMBER(
        p_collection_name => 'SAMPLE_NAME',
        p_n001            => i.ID1,
        p_n002            => i.ID2,
        p_n003            => i.NUMBER1,
        p_d001            => i.DATE1,
        p_c001            => i.CHARACTER1);
        
end loop;

ID1 is primary key of column TABLE2 table,
ID2 is foreign key of TABLE2,
NUMBER1 is number column in TABLE2 table,
DATE1 is date column in TABLE2 table,
CHARACTER1 is varchar column in TABLE2.

Create collection with name "SAMPLE_NAME", add member by using APEX_COLLECTION.ADD_MEMBER procedure within for loop. So, if data already present in TABLE2 then load data in apex collection.

Step 3: Create Interactive Grid on Child page (popup)
By using following query, create interactive grid.

select seq_id, n001, n002, n003, d001, c001 from apex_collections where collection_name = 'SAMPLE_NAME'

Make sure Enable Yes option in Edit section of Attributes of interactive grid

Also enable Primary Key as "Yes" for "SEQ_ID" column


Step 4: Customize Interactive Grid - Automatic Row process (After Submit)
Choose "Target Type" as "PL/SQL Code".

In PL/SQL code, all three operation are required (insert / update / delete)
Note: If primary key columns get populated by a trigger or in the PL/SQL code, it is a requirement that the new values are assigned to the bind variables of the primary key columns, because Application Express uses those values to fetch the row after an insert or update.

Use below code to populate, update and delete records of apex_collection

begin
    case :APEX$ROW_STATUS
    when 'C' then
        :SEQ_ID := APEX_COLLECTION.ADD_MEMBER(
        p_collection_name => 'SAMPLE_NAME',
        p_n001            => :N001,
        p_n002            => :N002,
        p_n003            => :N003,
        p_d001            => :D001,
        p_c001            => :C001);
        
    when 'U' then
        APEX_COLLECTION.UPDATE_MEMBER (
        p_collection_name => 'SAMPLE_NAME',
        p_seq             => :SEQ_ID,
        p_n001            => :N001,
        p_n002            => :N002,
        p_n003            => :N003,
        p_d001            => :D001,
        p_c001            => :C001);
    when 'D' then
        APEX_COLLECTION.DELETE_MEMBER (
        p_collection_name => 'SAMPLE_NAME',
        p_seq             => :SEQ_ID);
    end case;
end;

Here, "I" represents insert, "U" represents update and "D" represent delete DML operations.

Set "Lock Row" to "No" in Settings



Step 5: At master page add PL/SQL process (After Submit => After DML operation of TABLE1 process)

BEGIN
--Delete records which are not present in collection
    delete TABLE2 where ID2 = :PX_ID1 --Primary Item of TABLE1
    AND ID1 not in 
        (select     n001 
         from       apex_collections 
         where      collection_name = 'SAMPLE_NAME'
         AND        n001 is not null);
         
--Insert records which are new in collection
    insert into TABLE2 (ID2, DATE1, NUMBER1, CHARACTER1)
    (select :PX_ID1, trunc(d001), n005, c001 from apex_collections where
     collection_name = 'SAMPLE_NAME' and
     n001 is null);
     
--Update records
    for i in (  select fe.ID1, coll.d001, coll.n005, coll.c001 from 
                        (select * from TABLE2 where ID2 = :PX_ID1 ) fe,
                        (select * from apex_collections where collection_name = 'SAMPLE_NAME' and n002 = :PX_ID1 ) coll
                where   fe.ID1 = coll.n001
                and     fe.ID2 = coll.n002
                and     (trunc(fe.DATE1) <> trunc(coll.d001)
                or      hours_detail <> coll.n005
                or      who <> coll.c001)) loop
                
        update  TABLE2 
        set     DATE1 = trunc(i.d001), 
                NUMBER1 = i.n005,
                CHARACTER1 = i.c001
        where   ID1 = i.ID1;
        
    end loop;
END;

Add above code for save data when user hit "Create" or "Save" button.

Author: Lovneesh Kumar

APEX Collections with Interactive GRID

Imaginations are the key of special requirements, specially when are you working with dynamic customers who are always open for broad mind...