The more I use APEX collections the more I love them. A couple of things that I find them really useful for are the following….

• Using them in MERGE statements e.g.

  MERGE INTO cmgmt_comments c
  USING (
     SELECT : P16_COMMENT_ID  comment_id
     ,      : P16_CUST_ID     cust_id
     ,      col.clob001       comments
     ,      : P16_GROUP_ID    group_id
     ,      : P16_CONTRACT_ID contract_id
     FROM   DUAL d
     ,      apex_collections col
     WHERE  col.collection_name = 'CLOB_CONTENT'
     AND    col.c001            = 'P16_COMMENTS' ) c1
  ON ( c.comment_id = c1.comment_id )
  WHEN MATCHED THEN
    UPDATE
    SET c.comments    = c1.comments
    ,   c.group_id    = c1.group_id
    ,   c.contract_id = c1.contract_id
  WHEN NOT MATCHED THEN
     INSERT ( c.cust_id, c.comments, c.group_id, c.contract_id )
     VALUES ( c1.cust_id, c1.comments, c1.group_id, c1.contract_id );
  

• Using them in tabular forms, rather than an underlying table and using an “INSTEAD OF” trigger to manage the saving, updating, deleting of data. This works exceptionally well for data which you then process into multiple tables and gets around updateable view restrictions which can be an obstacle in some cases. See the following OTN forum post for more details to get yourself started….

The sweet part for us is that we have an Ext grid template that we use for tabular forms which allows us to add multiple rows at once without submitting the page. It also comes with declarative tabular form cascading LOV’s. This means that when we use a collection we can mimic a spreadsheet type design without having to rely on an updateable view behind the scenes. It adds a lot of flexibility to the solutions we can design for customers and since it’s a write once template based approach, development productivity is maintained