It Just Won't Tell Me
APEX knows the current pagination of a report. If you go to another page and back, or just press F5, you will see that APEX knows which page of your report you were on. Where can I get that user data? I have looked at the documented API's, and the closest thing I can see, is the apex_application.g_flow_current_min_row. The problem is that this is only available at render time and maps to pg_min_row request value. Otherwise, I only get the value 1 (first row).
It knows, but won't tell me!
The Dirty Approach
If there is a will, there is a way. I looked into the APEX tables, and especially wwv_flow_data. If you look, you will see a record containing the user session instance and report region id. Item value will be a colon separated value starting with the current min row, followed by current max rows (probably). That looks useful, but you need to keep your head about this. The value column is a CLOB data type, and a pretty special format, so if you attempt to use that data, be prepared to do a rewrite next time you patch your APEX installation.
The Dirty Solution
Access to wwv_flow_data directly is not something you want to do in a normal APEX installation. Creating a very single minded function in a privileged schema (granted select on wwv_flow_data) and granting access to the function to my application schema was the best I could come up with.
create or replace function <privileged schema user>.get_current_pagination ( p_region_id in number ) return varchar2 is l_ret varchar2(255); l_session_id number := v('SESSION'); -- setting directly prevents unauthorized use l_value_tab apex_application_global.vc_arr2; cursor c_region ( b_session_id in number , b_region_id in number ) is select item_value from apex_040000.wwv_flow_data where flow_instance = b_session_id and item_id = b_region_id; begin for r in c_region(l_session_id,p_region_id) loop l_value_tab := apex_util.string_to_table(r.item_value,':'); end loop; if l_value_tab.exists(1) then l_ret := l_value_tab(1) || '_' || l_value_tab(2) || '_' || l_value_tab(2); end if; return l_ret; end; / grant execute on <privileged schema user>.get_current_pagination to <application schema user>;That taken care of, the next thing is to get that value and use it in a call to $a_report_Split.
Follow these steps in your APEX builder:
- Create two hidden page items called P1_REPORT_REGION_ID and P1_REPORT_SPLIT_PARAMS
- Create a dynamic action to be called when you would like your report to be refreshed
- Create three true actions:
- First action to set P_REPORT_REGION_ID with the id of the report region:
(If you set this item value as a computation before header in APEX, you can skip this action)
- Second action is to set P1_REPORT_SPLIT_PARAMS with values corresponding to the current report page:
Action: SetValue, Type: PL/SQL Function Body, PL/SQL Function Body: return <privileged schema user>.get_current_pagination(:p1_report_region_id);
That should be it.
Naturally, I have cannot create a demo page on apex.oracle.com for this, as access to wwv_flow_data is somewhat scarce in that environment (and rightfully so).
Why Didn't He Just...
If you know of a better way to do this (and better than refreshing the whole page :-)), then please let me know! And if it is possible through documented API's, that would be grand.
If this is the closest I get, and any of the APEX team is reading this, then please consider this an enhancement request :-P
For future reference: Oracle Application Express 4.0.1.00.03