Understanding Oracle Apex_Application.G_Fnn and how to use it

Question

I am quite confused with how Oracle apex_application.gfnn works and whether it only works for standard SQL reports in Oracle ApEx or only with SQL (Updateable reports, i.e. tabular forms).

Basically I am trying to achieve the following using this sample SQL, which is just a Standard SQL Report but I'm unsure if what I am trying to achieve is possible with this type or report, i.e.:

select id,
       name,
       telephone,
       apex_item.checkbox2(10,id) as "Tick when Contacted",
       apex_item.text(20,my_date) as "Date Contacted",
       apex_item.textarea(30,my_comment,5,80) as "Comment"
from   my_table

Based on the above SQL, assume this SQL query returns 10 rows. Now using checkbox as my driving id, I tick the checkbox of all odd records/rows, i.e. rows 1,3,5,7,9 and for each of these rows, I also enter a date value (f20) together with a comment (f30)

Keeping this in mind, I then want to created a page process that is called when the user presses the "Save" button that will iterate through these checked rows and store for each record, my date and my comment but only for the rows that I have selected.

So based on the above, I would expect to have 5 news rows in my table with the following columns:

ID      MY_DATE      MY_COMMENT
1       26/08/2012   Comment A
3       27/08/2012   Comment B
5       28/08/2012   Comment C
7       29/08/2012   Comment D
9       30/08/2012   Comment E

Unfortunately I am unsure how to achieve this using apex_application.G_F10.COUNT.

I want to be able to access the content of each of these array elements (f20) and f(f30) for each row that I have ticked the checkbox with.

Is this possible or have I misunderstood how apex_application.G_Fnn works? If this is not possible, how I can achieve this? Do I need a tabular report?

Answer 1

You're very close.

select apex_item.checkbox2(10, empno) select_me,
apex_item.text(20, empno) empno,
apex_item.text(30, ename)||apex_item.hidden(50, empno) ename
from emp

I'm concatenating the hidden item since i don't want it in its own column. Messes with the layout.
Also, the hidden item is there because of how checkboxes work. Checkboxes only submit their values for checked items. This would mean that array 10 has 3 values. The other arrays would still contain the values for all rows.
This is why i added the hidden empno again: so we can match the checked values to the other rows.

On submit process:

DECLARE
   v_empno emp.empno%TYPE; 
   v_ename emp.ename%TYPE;
BEGIN
   --f10: checkbox
   --f20: empno
   --f30: ename
   --f50: empno again
   for i in 1..apex_application.g_f10.count
   loop
      for j in 1..apex_application.g_f50.count loop
         if apex_application.g_f10(i) = apex_application.g_f50(j) 
         then         
            -- access values for the selected rows in the other arrays
            v_empno := apex_application.g_f20(j);
            v_ename := apex_application.g_f30(j);

            apex_debug_message.log_message('Employee: '||v_empno||' - '||v_ename);
         end if;
      end loop;
   end loop;
END;

Run page, enable debug, select records 2, 4 and 6, submit.

Debug output:

All you now need to do is put your processing in that loop.