Next/Previous buttons from Interactive Report results

What could be simpler than a set of "Next" and "Previous" buttons?

What could be simpler than a set of “Next” and “Previous” buttons?

I love Interactive Reports, they make it easy to deliver a lot of power to users for very little development effort. However, with that power comes some complexity for building certain features not available in the base Apex toolset.

I had an IR with a fairly costly and complex query behind it, linked to another screen to view the details for a record. The users wanted to be able to view each record from the search result without having to click each record, go back to the Search, and click the next record, etc. Instead, they wanted “Next” and “Previous” buttons on the Single Record screen which would allow them to navigate the search results.

There are a few ideas on the web around how to implement this, and I considered two of them:

1. On the Single Record screen, run the query to determine what the next and previous records are in relation to the currently viewed record.

2. On the Search screen, instead of querying the tables/views directly, call a database procedure to store the query results in a collection; then, both the Search and the Single Record screens can query the collection.

Some problems with solution #1 include (a) the query might be quite expensive, so the Single Record screen may be too slow; and (b) if the data changes, the user might get unexpected results (“that record didn’t appear in my search results?”).

Another problem that both of these solutions share is that if the user has used any of the IR features such as custom filters, sort orders, etc. we won’t necessarily pick these up when determining what the Next/Previous records are. Certainly, the collection approach won’t pick these up at all.

Instead, I’ve gone for a different approach. Firstly, I’ve simplified the problem by dictating that the Next/Previous buttons will only allow the user to navigate the list of records they most recently saw on the search screen; therefore, if the results are paginated, we’ll only navigate through that page of results. If the user wants to keep going, they’ll go back to the Search screen and bring up the next page of results.

The solution is quite simple in concept but was a bit tricky to implement. The basic idea is that I encode the record IDs in the HTML generated by the report, and use some Dynamic Actions to grab the resulting list of record IDs, store them as a CSV in a page item, which can then be parsed by the View/Edit screen.

Here it is in detail, ready for you to try, critique or improve (in this example, my record’s id column is called ft_id, my Search screen is p23, and the Single Record screen is p26):

Part A: Save the IDs shown on the Interactive Report

1. In the Link Column on the interactive report region, add class="report-ft-id" to the Link Attributes.

2. In the Link Icon, add data-ft-id=#FT_ID# into the img tag.link_column
I added this to the img bit because the Link Column field doesn’t do the #FT_ID# substitution, unfortunately.
3. Add the following javascript function to the page’s Function and Global Variable Declaration:

function ft_id_list() {
  return $(".report-ft-id >img")
         .map(function(){return $(this).attr("data-ft-id");})
         .get()
         .join(",");
}

This function searches for any records shown on the page by searching for the report-ft-id class, extracts from each one the img node’s data-ft-id attribute, maps these into an array, and then squashes that array down to a comma-separated list.

4. Create a hidden item P23_FT_ID_LIST which will store the resulting list.

5. Create an application item FT_ID_LIST which will be read by the Single Record page.

6. Create a Dynamic Action triggered by the event Page Load, which saves the list of IDs into session state by performing two actions:
(a) Set Value based on a JavaScript Expression, ft_id_list();, assigned to the item P23_FT_ID_LIST
(b) Execute PL/SQL Code which runs the code :FT_ID_LIST := :P23_FT_ID_LIST;. Make sure to set Page Items to Submit to P23_FT_ID_LIST and set Page Items to Return to FT_ID_LIST.
This dynamic action will only fire when the page is initially loaded.

7. Copy the Dynamic Action, but this time set the event to Custom and the Custom Event to apexafterrefresh. This way, whenever the user changes the rows shown in the report (e.g. by paginating, or changing filters or sort order, etc.), the list will be refreshed as well.

Part B: Add the Next/Previous buttons

8. Create some procedures on the database (e.g. in a database package) which take a list of IDs and a “current” ID, and return the next or previous ID in the list:

FUNCTION next_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  ) RETURN VARCHAR2 IS
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
BEGIN
  pos := INSTR(buf, search);
  IF pos > 0 THEN
    -- strip out the found ID and all previous
    buf := SUBSTR(buf, pos+LENGTH(search));
    -- chop off the first ID now in the list
    IF INSTR(buf,',') > 0 THEN
      new_id := SUBSTR(buf, 1, INSTR(buf,',')-1);
    END IF;
  END IF;
  RETURN new_id;
END next_id;

FUNCTION prev_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  ) RETURN VARCHAR2 IS
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
BEGIN
  pos := INSTR(buf, search);
  IF pos > 0 THEN
    -- strip out the found ID and all following
    buf := SUBSTR(buf, 1, pos-1);
    -- chop off all but the last ID in the remaining list
    IF INSTR(buf,',',-1) > 0 THEN
      new_id := SUBSTR(buf, INSTR(buf,',',-1)+1);
    END IF;
  END IF;
  RETURN new_id;
END prev_id;

9. Add two hidden items to the Single Record screen: P26_FT_ID_NEXT and P26_FT_ID_PREV.

10. On P26_FT_ID_NEXT, set Source Type to PL/SQL Expression, and set Source value or expression to next_id(:FT_ID_LIST,:P26_FT_ID), and similarly for P26_FT_ID_PREV to prev_id(:FT_ID_LIST,:P26_FT_ID).

11. Add buttons Next and Previous, with Action set to Redirect to Page in this Application, pointing back to the same page, but setting the P26_FT_ID to &P26_FT_ID_NEXT. and &P26_FT_ID_PREV., respectively.

This method means that it doesn’t matter if the query behind the report changes, or if the user adds filters or uses different saved reports; the Single Record screen doesn’t need to know – it just needs to know what the list of IDs the user most recently saw on the Search screen were.

Some downsides to this approach include:

  • Server load – the dynamic actions on the report refresh, which causes it to do an ajax call to the database on every refresh of the IR. But at least it saves the View/Edit screen re-executing the query on every page load.
  • Rows Per Page limitation – since we save the list of IDs as a CSV in a single string variable, we may have issues if the user sets Rows Per Page to “All” with a large result set – so we need to limit the Maximum Rows Per Page to about 3,000 (this assumes that all the IDs will be less than 10 digits long) to fit in the 32,767 char limit. YMMV.
  • Duplicate records – this method assumes that the IDs shown in the report will always be distinct. If this is not true, the next/previous functions will not allow the user to navigate through the whole list.

Code can be scary when you simplify it

Disclaimer: I’m not posting to make me look better, we’ve all written code that we’re later ashamed of, and I’m no different!

This is some code I discovered buried in a system some time ago. I’ve kept a copy of it because it illustrates a number of things NOT to do:

FUNCTION password_is_valid
  (in_password IN VARCHAR2)
-- do NOT copy this code!!! ...
  RETURN VARCHAR2 IS
  l_valid VARCHAR2(1);
  l_sql VARCHAR2(32000);
  CURSOR cur_rules IS
    SELECT REPLACE(sql_expression
                  ,'#PASSWORD#'
                  ,'''' || in_password || ''''
                  ) AS sql_expression
    FROM password_rules;
BEGIN
  FOR l_rec IN cur_rules LOOP
    l_valid := 'N';
    -- SQL injection, here we come...
    l_sql := 'SELECT ''Y'' FROM DUAL ' || l_rec.sql_expression;
    BEGIN
      -- why not flood the shared pool with SQLs containing
      -- user passwords in cleartext?
      EXECUTE IMMEDIATE l_sql INTO l_valid;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
    IF l_valid = 'N' THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN l_valid;
END password_is_valid;

I am pretty sure this code was no longer used, but I couldn’t be sure as I didn’t have access to all the instances that could run it.


Submit from jQuery modal causing session state protection violation

Don’t you hate those nagging issues where you attempt a few fixes in vain, waste hours of your life, and then suddenly the issue just resolves itself? What’s worse than an issue that won’t go away is one that just resolves itself and you don’t know why. You don’t know if it is just hiding, waiting to reappear at some inconvenient time later (e.g. when the app goes live in Production).

I added the first modal popup on a page in my application running on APEX 4.2.4 and immediately hit the problem described here: Jquery modal causing page protection violation error.

I’d used the simple popup modal using the builtin jQuery dialog widget as described here: Oracle APEX 4.2 – Creating a modal window [link no longer valid – http://rowantraining.blogspot.com.au/2013/08/oracle-apex-42-creating-modal-window.html] – Helen’s example works but doesn’t include submitting the page. Side note: I found it only worked if I put it in one of the page body regions, not in the After Header region.

The purpose of my popup is to accept additional input from the user, then submit the page. If I removed the page submit, the popup worked fine. On submit, it raises this error for the first item on the page:

“Session state protection violation: This may be caused by manual alteration of protected page item P1_ID. …”

If I turn off session state protection on the item, the same error is then raised on the next item on the page. Plus, I require session state protection to be enabled so disabling it is not an acceptable solution.

I didn’t want to import another plugin to the application because I wanted to keep it dead simple. The default jQuery dialog should just work, dammit! (I don’t mind importing plugins if the plain vanilla features provided by APEX are just not sufficient for the need; but the basic jQuery dialog is perfectly fine.)

In the end on a hunch I tried doing the submit after a timeout, i.e. instead of:

function popupSubmit() {
  $("#myPopup").dialog('close');
  doSubmit('SAVE');
}

I changed it to:

function popupSubmit() {
  $("#myPopup").dialog('close');
  setTimeout( function() { doSubmit('SAVE'); }, 1);
}

This workaround seemed to have done the trick, but I wasn’t happy – it just felt “hacky”.

I tried adding a similar popup to another page that was far simpler and it didn’t experience the problem. So submitting from a popup dialog should work.

I reverted the code to remove the timout and tried disabling all the Dynamic Actions on the original page, and the problem disappeared. So I figured the problem was caused by some interaction with a Dynamic Action. I gradually re-enabled the DAs one by one, retesting the page between each one. Finally I re-enabled the last Dynamic Action – and the problem still didn’t reoccur. So the problem has resolved itself, but apparently not because of any particular thing I’ve fixed, and I can no longer reproduce the problem. Aarrgh.

UPDATE 4/9/2015:

After a number of unrelated changes to the page, I started getting this error again, consistently. As the comments (see below) suggested, this is due to the jQuery dialog.close() method moving its contents outside of the <form> tag, which meant that the items submitted to APEX differed from those in the original page, causing APEX to raise the error.

I could find no way of avoiding the error, so instead I’ve fixed it by a little hack: instead of opening the dialog based on the original region that was generated by APEX, I use jQuery to create a copy of the region, and open and close that copy. That way, the original region stays unmodified (forever hidden) and the page submit works. It’s a bit fiddly but it seems to work so far.

(in this example, P1_REAL_NOTE is the database item; P1_POPUP_NOTE is a non-database textarea that’s rendered in the popup region)

function showPopup() {
  //copy the current note text into the popup window
  $s("P1_POPUP_NOTE",$v("P1_REAL_NOTE"));

  //make a temporary copy of the region
  //(because dialog.close moves it leading to
  //APEX.SESSION_STATE.ITEM_VALUE_PROTECTION)
  var x = $("#myPopup").clone();
  x.attr("id","myPopupCopy");
  x.insertAfter($("#myPopup"));
  //change the ID of the copied popup note item
  $("#myPopupCopy textarea#P1_POPUP_NOTE")
    .attr("id","P1_POPUP_NOTE_COPY");

  //popup the dialog
  $("#myPopupCopy").dialog(
    {modal:true
    ,autoOpen:true
    ,title:'Please enter your Notes'
    ,width:'500px'
    }
  );
}
function popupSubmit() {
  //copy the note text back into the real form item
  $s("P1_REAL_NOTE",$v('P1_POPUP_NOTE_COPY'));

  //close the popup (this moves the contents out of the
  //form tag which is what was leading to the apex error)
  $("#myPopupCopy").dialog('close');

  apex.submit({request:'SAVE_REVIEW',showWait:true});
}

AUSOUG 2014 Perth Conference

It’s that wonderful time of year again – no, not talking about springtime, when I’m stalked by airborne grass pollens causing me to choke and gag all day. The sound of a lawnmower is enough to elicit a sneezing fit – and in Aussie springtime, that’s most weekends. (I’m so glad my wife does the mowing at our place, usually while I’m at work. My son is now old enough to do the mowing – and I’m quite happy to pay him to do it, I can tell you!)

The “wonderful time of year” I referred to earlier is that time when the excitement of OpenWorld has petered a bit and AUSOUG have released the program for Perth’s excellent conference.

I’ll be presenting a talk about Building Maintainable Applications in Apex, which I sincerely hope will be a lot more interesting than the title sounds. (It’s funny how you think after rejecting several competing wordings, the title you finally settle on is perfect when you submit the abstract – but when you see it alongside the other gems on the program you think “surely I could have come up with a more interesting title?”. Oh well.)

I’ll be talking about why you should remove most of the business logic from your Apex application – or at least consider it. I won’t promise it will necessarily be earth-shattering or original. I think there’s some ideas here that are worth spreading around – get people thinking, talking and writing about. Most of the talk will be in reference to a recent greenfields project, but the ideas are ones I’ve picked up over the years at different locations and from other Oracle developers – bits that I find work well or seem worthwhile expanding on.

If that topic doesn’t grab you, you might be interested in hearing Tim Hall talk about virtualization. If I wasn’t speaking at the time I would have attended that one – so if you do, could you make sure to take a few notes for me? Thanks.

I hope to see you there. But – fake flowers only please.

WA_2014_banner_web

WA Conference Program