Force page submits when paginating via selectlist

This is an extension for my previous post, how to not only submit via the next and previous links, but also via select.

This might not be the best solutions, and improvements are possible, but it’s a good startpoint. We will replace the #TEXT#-variable of Apex with our own generated html-code.

You need to add 3 extra lines in your sql-query to get the information you need, these columns don’t need to be displayed: ,ROWNUM API_ROWNUM ,COUNT(1) OVER() API_TOTALROWS ,NVL(:P1_ROWS,15) API_RPP

You will need to create a function that create our html-code, and an application-item to store the result in. My app-item will be API_SELECT and my function will be f_get_report_vars. The code will be shown below.

Next we have 2 modification in the report template.

We need to retrieve our total number of rows, our current displayed rows and our rows per page. I changed the column template 1 condition to PLSQL and added f_set_reportvars(‘#API_ROWNUM#’,’#API_TOTALROWS#’,’#API_RPP#’) = 0.

0 is a dummy value so my evalution is always TRUE. It might not be the correct place to do this, because it wasn’t designed to be used like this and the function is execute for every displayed row, instead of just once. (Suggestions are welcome)

Next step is changing the pagination template, which is default empty or &ltspan class=”instructiontext”>#TEXT#&lt/span>and use our app-item &ltspan class=”instructiontext”>&API_SELECT.&lt/span>

And the function making this magic happen would be:create or replaceFUNCTION f_set_reportvars(pin_rownum IN NUMBER ,pin_total_rows IN NUMBER ,pin_rpp IN NUMBER ) RETURN NUMBERIS lv_select VARCHAR2(1000); ln_from NUMBER; ln_to NUMBER; ln_sets NUMBER; ln_page_id NUMBER := apex_util.get_session_state(‘APP_PAGE_ID’); ln_app_id NUMBER := apex_util.get_session_state(‘APP_ID’); ln_session NUMBER := apex_util.get_session_state(‘SESSION’); ln_region_id NUMBER;BEGIN –optional, set variables for other usage –apex_util.set_session_state(‘API_ROWNUM’,piv_rownum); –apex_util.set_session_state(‘API_TOTALROWS’,piv_totalrows); –apex_util.set_session_state(‘API_PPR’,piv_ppr);

–get region id, when more then one report per page you need a tag in your static_id SELECT region_id INTO ln_region_id FROM apex_application_page_regions WHERE application_id = ln_app_id AND page_id = ln_page_id AND source_type = ‘Report’ /*AND static_id LIKE ‘MYTAG%’*/;