Working With apex_collection in Oracle Apex

 

1. Create apex_collection :
(Collection Name Must be in Capital letter)
apex_collection.create_collection('NAME');

2. Existence Check:

apex_collection.collection_exists ('NAME');

3. Add Member:

  apex_collection.add_member (p_collection_name   => 'NAME',
                                  p_c001              => :P2_ITEM1,
                                  p_c002              => :P2_ITEM2,
                                  p_c003              => :P2_ITEM3);
                               
4. Collection to Database table data transcfer:

BEGIN
   FOR i IN (  SELECT c001 AS a, c002 AS b, c003 AS c
                 FROM apex_collections
                WHERE collection_name = 'NAME'
             ORDER BY 1)
   LOOP
      INSERT INTO DATABASE_TABLE (COLUMN1, COLUMN2, COLUMN3)
           VALUES (i.a, i.b, i.c);
   END LOOP;

   COMMIT;
END; 

5. Truncate Collection:

apex_collection.truncate_collection(p_collection_name => 'NAME');   

6. Delete Collection Member :

apex_collection.delete_member(
    p_collection_name => collection name,
    p_seq             => member sequence number);    
 
7. Delete All Collecion Members:

apex_collection.delete_members(
    p_collection_name => collection name,
    p_attr_number     => number of attribute used to match for the                           specified
                         attribute value for deletion, 
    p_attr_value      => attribute value of the member attribute                             used to match for deletion); 
8. Delete Collection :

apex_collection.delete_collection (
    p_collection_name =>'NAME');