Update cell of APEX_COLLECTION in interactive grid

Question

I have a interactive grid with a full join on the wwv_flow_qb_saved_query table and the apex_collection, like this:

Select
qbsq.ID,
qbsq.TITLE, 
qbsq.QB_SQL, 
qbsq.DESCRIPTION,
ac.collection_name,
ac.seq_id,
ac.C001 as new_TITLE, 
ac.CLOB001 as new_QB_SQL, 
ac.C002 as new_DESCRIPTION
FROM APEX_050100.WWV_FLOW_QB_SAVED_QUERY qbsq
full join apex_collections ac
on qbsq.TITLE = ac.C001

The result look like this:

Now I need to make the possibility for users to change the title of the apex_collection, so the title in the "imported queries" column group. If the title is updated and different from the title of the existing queries, there would be a new grid entry.

I tried to do it in the "save interactive grid data" process => settings => target type => pl/sql code with

declare
collection_name varchar2(255);
seq_id number;
new_title varchar2(4000);

begin
    collection_name := :COLLECTION_NAME;
    seq_id := :SEQ_ID;
    new_title := :NEW_TITLE;

    case v('APEX$ROW_STATUS')
    when 'U' then
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => collection_name,
        p_seq => seq_id,
        p_attr_number => 1,
        p_attr_value => new_title);
   end case;
end;

But something just doesn't work and I can't change the title. Am I missing something? Please could someone help me find the problem. I couldn't find any other helpful posts..

Thanks

It looks like your code should work (I would use :APEX$ROW_STATUS rather than the v() function but that's irrelevant). Could it be that when the page reloads you have a process that re-populates the collection and overwrites the change? — Tony Andrews
On the top of the page in the first region I'm uploading a file to the collection. After a submit the new collection shows up in the second region, which is the interactive grid, like shown above. When I double click on this column TITLE in the column group "imported Queries", there is no possibility to change anything, I can't type anything. The color of the interactive grid gets grey, like the page background. — Sara
So your problem is that the user is unable to make any change at all, not that they can make a change but it is not saved? If that's the case, what are the Title column's property settings for Type, Query Only, Read Only condition? — Tony Andrews
Exactly, Type = Text Area, Query Only = No, Read Only Condition = No Condition. Thanks — Sara
And the grid has edit allowed, update allowed? The code you posted is irrelevant to the problem by the way if the user can't even change the values on the screen. — Tony Andrews

Sara Sara · Accepted Answer · 2017-08-10T11:58:20

Thanks to Tony Andrews, for the solution I just had to change in the regions attributes the "Allowed Row Operations Column" to "null".

Update cell of APEX_COLLECTION in interactive grid

2 Answers