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 replace
FUNCTION f_set_reportvars(pin_rownum IN NUMBER
,pin_total_rows IN NUMBER
,pin_rpp IN NUMBER
) RETURN NUMBER
IS
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%’*/;

–define number of row ranges
ln_sets := CEIL(pin_total_rows/pin_rpp);
–when more then 1 row range, create select list
IF ln_sets > 1 THEN
lv_select := ‘&ltselect id=”X01_’||ln_region_id
||’” onchange=”doSubmit(”f?p=’||ln_app_id||’:’||ln_page_id||’:’||ln_session
||’:pg_R_’||ln_region_id||’:NO&pg_min_row=”+this.options[selectedIndex].value+”&pg_rows_fetched=_’||pin_rpp||”’);” size=”1″ name=”X01″>’;
–make an option in the select list for every row range
FOR i IN 1..ln_sets LOOP
ln_from := ((i-1)*pin_rpp)+1;
ln_to := LEAST((i*pin_rpp),pin_total_rows);
–make difference between current row range and others
IF pin_rownum BETWEEN ln_from AND ln_to THEN
lv_select :=lv_select||’&ltoption selected=”selected” value=”current”>Row(s) ‘||ln_from||’-’||ln_to||’ of ‘||pin_total_rows||’&lt/option>’;
ELSE
lv_select :=lv_select||’&ltoption value=”‘||ln_from||’&pg_max_rows=’||pin_rpp||’”>’||ln_from||’-’||ln_to||’ of ‘||pin_total_rows||’&lt/option>’;
END IF;
END LOOP;
–close select-tag
lv_select := lv_select||’&lt/select>’;
END IF;
–set application item with select list
apex_util.set_session_state(p_name => ‘API_SELECT’
,p_value => lv_select);
–return dummy
RETURN 0;
END;

Force page submits when paginating through a report

How do you force a page submit when paginating through a report?

Standard behaviour from apex is to redirect when navigating through the recordset of a report. While this is advisable in most cases, we sometimes need processes to run when the user clicks the next or previous button.

I have often seen the question, but never an answer. Although this can be achieved fairly easy, with following steps.

1) Adapt the report template. Best is to create a new report template, based on an existing template, so we don’t affect all pages.
I copy “Standard Report” and name it “Standard Report with Submit”.

2) In this new template we are interesting in the “Pagination Subtemplate”. We change “Next Page Template”, the original value will be a redirect:
&lta href=”#LINK#”>next&lt/a>
We can easily change this to a submit:
&lta href=”javascript:doSubmit(‘#LINK#’);”>next&lt/a>

And we change “Previous Page Template” analogous from :
&lta href=”#LINK#”>previous&lt/a>
to:
&lta href=”javascript:doSubmit(‘#LINK#’);”>previous&lt/a>

3) When we use this report template on our page, the next and previous button will submit, and the request will be the url it wants to redirect too. This way we can use our ‘after submit’-processes.

4) The last step we need is to foresee a branch.
We create a branch to URL, and our url will be our request:
&REQUEST.
We make the branche conditional, based on Pl/Sql:
:REQUEST LIKE ‘f?p=%’

Steps 3 & 4 need to be repeated for every page needing this functionality. Our processes can be made conditional in the same manner as our branch.

We used this for saving our data temporarily to a collection when paginating through an updatable report, and we didn’t notice any drawbacks using this method.

Apex Course – a Bit Different

I’m one of the iAdvise Apex-trainers, a small group of Oracle Consultants that besides their daily job, also give an apex course from time to time.

I’ll get this question every few months, but last time was rather unusual. Would you mind giving this course in Suriname? This country in South America is located over 7000 km from iAdvise. Suriname used to be a colony of The Netherlands, and one of the few places with Dutch as the mother of tongue… which is why they preferred to work with us. It was starting to get colder here, so I didn’t mind a little trip to a tropical country. A few weeks later the deal was made and I was ready to go.

This wasn’t going to be our average 3-day basic course. Going through all this trouble of getting me there, they wanted to know everything about it, so we added 2 days filled with advanced topics.

It was a challenge, because of the diverse group. Some had experience with Apex, others had years of experience with Forms but most of them where access developers. Before I left I had send some training material for them to study so we could move though the basics a bit faster.
For the 2 days of advanced topics most time was spend on my 2 favourite subjects: security and performance. While it’s not that difficult to create an application, it’s very easy to create one full of security flaws. You shouldn’t call yourself an Apex developer if you don’t know all about SSP, sql injection and all of those other treats.
The time that was left, we used to set up their first projects. Designing your application is probably the most important step.

I’m sure they’ll be excellent Apex developers in no time.