Tuesday, March 20, 2012

Record Navigation - Plugged In

Update: improved, cleaned up, and bug fixes: follow-up

By encouragement of Dan McGhan, I turned my record navigation into a process plugin :-)

This is the plugin at work:

When you debug the page, you’ll see the following. Handy when it just doesn’t seem to want to work.


Running the code from a sql command is still possible, if you use the syntax below.
Key elements are p_use_session state, p_binds and p_binds_val. By setting p_use_session_state to TRUE, you indicate you want the bind variables in the query to be extracted and replaced with their session state values. This requires a valid apex session, which would render the procedure when called from for example the sql command window. I’m quite fond of being able to run my code through the sql command if something needs debugging, so I provided this option.
Setting p_use_sesstion_state to FALSE stops the automatic replacement. Instead, the provided bind variables in the p_binds array are processed, replacing them with the associated value in p_binds_val. Note that with “associated” i mean that for a value in p_binds, I look for the value in p_binds_val by position: p_binds(1) + p_binds_val(1).

   v_next      VARCHAR2(50);
   v_prev      VARCHAR2(50);
   v_top       VARCHAR2(50);
   v_bot       VARCHAR2(50);
   v_cur_tot   VARCHAR2(50);
   v_debug     VARCHAR2(5000);
   v_binds     DBMS_SQL.VARCHAR2_TABLE;
   v_binds_val DBMS_SQL.VARCHAR2_TABLE;
    v_binds(1) := 'P52_COMPROD_ID';
    v_binds_val(1) := '106672';
      p_app_id      => 190,
      p_session_id  => 4133013019922250,
      p_column_id   => 'ID', --column id for IR!
      p_value       => 153876,
      p_page_id     => 52,
      p_use_session_state => FALSE,      
      p_binds       => v_binds,
      p_binds_val   => v_binds_val,
      p_next        => v_next,
      p_prev        => v_prev,
      p_top         => v_top,
      p_bot         => v_bot,
      p_cur_tot     => v_cur_tot,
      p_debug       => v_debug 
   dbms_output.put_line('v_next: '||v_next);
   dbms_output.put_line('v_prev: '||v_prev);
   dbms_output.put_line('v_top: '||v_top);
   dbms_output.put_line('v_bot: '||v_bot);
   dbms_output.put_line('v_cur_tot: '||v_cur_tot);
   dbms_output.put_line('v_debug : '||v_debug );

All the code which was previously in my package is now wrapped in the plugin of course. However, you can still switch this around, since I still provide the package. Just put it on your database and the plugin will still work fine. All comments are removed from the plugin code too to save on space. Refer to the package code if you want detailed descriptions!

If you have put the package on the database, you can replace the original plugin code by calling apex_record_navigation.get_navigation_values, instead of calling get_navigation_values locally. An adjusted plugin is provided, use process_type_plugin_plugins_process_recordnavigation_pkg.sql. This has all the local procedures removed and calls the package on the db.

Note: there is currently a bug whereby this whole process will fail when using an IR with multiple saved versions of it. As soon as a user selects another saved version and goes to a detail, the chance is that the wrong base query is used. This is due to me not knowing which version is currently selected by the user on the database. The application express views do not contain a column which holds any such info, nor a timestamp of sort, even though there are last_updated and created_on column. Unfortunaly, these do not change when a report is selected :( The only way to solve this might be to create a dynamic action on the IR page which sets/holds the current selected IR ID in a page item, but i would consider this some sort of workaround.
So my advice for this: do not use an IR with multiple saved versions and this navigation process - for now. If you have any insight on this, feel free to contact me :)

I'll put this up on apex-plugin sometime, but for now the file is here: recordnavigation.zip

There's also this thread on OTN


  1. Hello Tom,
    Thanks so much for doing all this and posting it. This is actually the first plug-in I've tried to utilize. I imported the plug-in, created my items and buttons (created as redirect instead of sumbit/branching although tried branching too) but when I click on my next or prev buttons, the page does a little flash and nothing changes... on the same record I was before. I've been pooring through the debug but nothing is jumping out at me. I'm really not sure what my next steps are for figuring out what I have setup incorrectly. Any direction you can lend would be most welcome.


    1. Hello Kristina,

      The debugging can be a bit annoying when having to go through the ir-query building steps, certainly. I take it you have debugged your page, and you have output there from the plugin. If you look in there, do you get the complete query? If yes, have you tried running it in a sql-command window, providing the necessary variables?
      Something i've also noticed, but haven't had the time to change yet, is that there is some error catching going on in the package and i'd rather not wanted that there: i find a good meaningfull error a lot more clear.
      Furthermore, going through debug: do you see any no-data-found type of errors, like with report ids? Just guessing out loud here.
      Feel free to follow this up with me through my email, this type of conversation gets messy quick in these comments! :)