This discussion is archived
13 Replies Latest reply: Apr 26, 2013 4:38 AM by cibo005 RSS

APEX Collection - how to process

933667 Newbie
Currently Being Moderated
Hello all.

I am fairly new to apex and using apex 4.0. I have a SQL report based on collections, with 2 columns updateable. Following is the query.

SELECT APEX_ITEM.TEXT(1,SEQ_ID) AS SEQ_ID,
APEX_ITEM.TEXT(2,c001) AS R_TYPE,
APEX_ITEM.TEXT(3,c002) AS TA_ID,
APEX_ITEM.TEXT(4,c003) AS ANSWER_ID,
c008 AS R_TITLE,
CASE WHEN c001 = 'QUESTION'
THEN APEX_ITEM.CHECKBOX(40,c009)
ELSE APEX_ITEM.HIDDEN(40,c009) END AS ANSWER_Y_N,
CASE WHEN c001 = 'QUESTION' AND c011 = 'Y'
THEN APEX_ITEM.TEXTAREA(41,c010,3,40)
ELSE APEX_ITEM.HIDDEN(41,c010) END AS ANSWER_TEXT,
APEX_ITEM.TEXT(11,c011) AS TEXT_FLAG
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = 'AUDIT_ANSWER'
AND c002 = :P18_TA_ID
ORDER BY c004, c005, c006, c007

ANSWER_ID is the primary key which is not displaying. R_TITLE is the question which is display only. ANSWER_Y_N is a column having Y or Null answers. ANSWER_TEXT is enterable comment column.

After SAVE button, it fires page process (On Submit - Before computations) having following code -

BEGIN

FOR i IN 1..APEX_APPLICATION.G_F04.COUNT
LOOP

IF APEX_APPLICATION.G_F02(i) = 'QUESTION'
THEN
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE('AUDIT_ANSWER', APEX_APPLICATION.G_F04(i), '9', APEX_APPLICATION.G_F40(i));
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE('AUDIT_ANSWER', APEX_APPLICATION.G_F04(i), '10', APEX_APPLICATION.G_F41(i));
END IF;
END LOOP;

FOR r IN ( SELECT c003, c009, c010
+     FROM APEX_COLLECTIONS+
+     WHERE COLLECTION_NAME = 'AUDIT_ANSWER' )+
LOOP
UPDATE VW_OTA_AUDIT_ANSWERS
SET ANSWER_Y_N  = r.c009,
ANSWER_TEXT = r.c010
WHERE ANSWER_ID = r.c003;
END LOOP;

END;

It is not updating the collection. What could be the problem here.

Edited by: 930664 on Nov 29, 2012 3:53 AM

Legend

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