This discussion is archived
1 Reply Latest reply: Jun 3, 2013 9:24 AM by ediazjorge RSS

Collections - Update member attributes

VANJ Journeyer
Currently Being Moderated
APEX 4.2.2

Suppose I have a APEX collection used to store supplemental session-specific data for some items. N001 stores the primary key of the database record. The Cxxx attributes store the session-specific data.

How can I go about updating specific values in the collection based on the primary key? All the update APIs in the APEX_COLLECTION package work with the p_seq not with our own data. Basically, I want something equivalent to
update collection set c001=<value>, c002=<value> where n001=<pk>; if (sql%rowcount=0) then create the n001,c001,c002,... collection member
Any ideas? Thanks
  • 1. Re: Collections - Update member attributes
    ediazjorge Explorer
    Currently Being Moderated
    Hello VANJ,

    I guess you can write something like this:
    declare
      l_seq_id number := null;
    
    begin
      select seq_id
      into l_seq_id
      from apex_collections
      where collection_name = 'YOUR_COLLECTION_NAME'
      and n001 = <pk>;
    
      if l_seq_id is null then -- new
        apex_collection.add_member(
          p_collection_name => 'YOUR_COLLECTION_NAME',
          p_c001            => 
          ...
        );
    
      else -- update
        apex_collection.update_member(
          p_collection_name => 'YOUR_COLLECTION_NAME',
          p_seq             => l_seq_id,
          p_c001            =>
          ...
        );
    end;
    Thank you,

    Erick

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points