apex_collection.update_member results in no data found found
177437Sep 20 2009 — edited Sep 20 2009I'm trying to build a simple scheduling grid using a collection to show who is available on what day of the week. This is the table:
CREATE TABLE WORK (
USER_ID NUMBER(9) NOT NULL,
MON VARCHAR2(1),
TUE VARCHAR2(1),
WED VARCHAR2(1),
THU VARCHAR2(1),
FRI VARCHAR2(1),
CONSTRAINT WORK_FROM_HOME_PK PRIMARY KEY (USER_ID));
My create collection script is:
if apex_collection.collection_exists(p_collection_name=>'WFH') = TRUE then
apex_collection.delete_collection(p_collection_name=>'WFH');
end if;
apex_collection.create_collection_from_query(
p_collection_name=>'WFH',
p_query=>'SELECT user_id,
mon,
tue,
wed,
thu,
fri
FROM work');
As I try to sort this out, I have two reports on the page. One is for the schedule/collection processing, & the other simply shows the collection so I can verify changes:
SELECT seq_id, c001 emp_id, c002 mon, c003 tue, c004 wed, c005 thu, c006 fri -- my working query
FROM apex_collections
WHERE collection_name = 'WFH'
select * from apex_collections -- this shows the entire collection
where collection_name = 'WFH'
I have 2 buttons & processes to add new rows & undo changes, & a button for updates. (I'm not trying to update the underlying table yet, just update the collection.) The add & undo seem to work fine. When I try to update the collection, I get a "no data found" error. The update collection process is:
BEGIN
FOR i IN 1..apex_application.g_f01.count LOOP
apex_collection.update_member(
p_collection_name => 'WFH',
p_seq => apex_application.g_f01(i),
p_c001 => apex_application.g_f02(i),
p_c002 => apex_application.g_f03(i),
p_c003 => apex_application.g_f04(i),
p_c004 => apex_application.g_f05(i),
p_c005 => apex_application.g_f06(i),
p_c006 => apex_application.g_f07(i));
END LOOP;
END;
I suspect there is a problem with the way I'm trying to reference the sequence, but have yet to figure it out.
Suggestions?