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