Passing item value in PLSQL dynamic content in ORACLE APEX for where clause using button?

Question

I want to pass items in PLSQL dynamic content region to be used for where clause. Is this not possible or I just did it wrong?

I tried to set variable with item value and add it to where clause but it seems not working.

I also tried to print the item value to check if it is passing through plsql dynamic content but its not.

THIS IS MY SAMPLE CODE:

declare
 v_year number := :P1_year;
 cursor c_tasks is 
   select task_name, assigned_to
     from eba_ut_chart_tasks
    where year >= :P1_year;
 
begin
  sys.htp.p('<table>');
  sys.htp.p('<tr>');
  for a in c_tasks loop
    sys.htp.p('<td>' || a.task_name || ' (' || a.assigned_to || ')</td>' );
   end loop;
  sys.htp.p('</tr>');
  sys.htp.p('</table>');



  sys.htp.p(v_year);
end;

After pressing search Button to refresh the region, table still doesn`t filter. I also tried putting the value in variable just to see if its passing through the PLSQL content but still not working.

Answer 1

This is a sample table contents:

SQL> select * from eba_ut_chart_tasks;

      YEAR TASK_N ASSIGN
---------- ------ ------
      2015 Task 1 Little
      2022 Task 2 Foot
      2032 Task 3 Scott

SQL>

In Apex, I created a region whose type is "Dynamic content". It contains a select list item for years and submits the page when a new value is chosen:

select distinct year d, year r from eba_ut_chart_tasks order by year;

As Apex requires, its (region's) source is a function (body that returns a CLOB) so - let it return something (NULL will do). Also, <tr> tags should be include into the loop, otherwise you'll get everything in only one row.

declare
  cursor c_tasks is 
    select task_name, assigned_to
      from eba_ut_chart_tasks
      where year >= :P1_YEAR;
 begin
  sys.htp.p('<table>');
  for a in c_tasks loop
    sys.htp.p('<tr><td>' || a.task_name || ' (' || a.assigned_to || ')</td></tr>' );
  end loop;
  sys.htp.p('</table>');

  return null;
end;

When executed: