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).

DECLARE
   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;
BEGIN
    v_binds(1) := 'P52_COMPROD_ID';
    v_binds_val(1) := '106672';
    apex_record_navigation.get_navigation_values
   (
      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 );
END;

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