Thursday, August 30, 2012

Record navigation: refinement

First off: Dan McGhan, Thanks ;-) I wouldn’t have figured this one out by myself!

Second: my previous posts included the package APEX_IR. It has now been replaced by APEX_IR_PKG because APEX_IR is a new API in apex 4.2!

Finally, the code has been cracked :-) : it IS possible to retrieve the currently active interactive report through SQL! This is actually stored in a preference, residing in wwv_flow_preferences$, but also retrievable through apex_util.get_preference.
Code below is how the report id is retrieved:

IF p_report_id IS NULL THEN
      SELECT interactive_report_id
        INTO v_report_id
        FROM apex_application_page_ir
       WHERE application_id = p_app_id
         AND page_id = p_page_id;

      apex_debug_message.log_message('interactive report_id: '||v_report_id);

      lv_pref := apex_util.get_preference(p_preference => 'FSP_IR_'||p_app_id||'_P'||p_page_id||'_W'||v_report_id, p_user => p_app_user);
      lv_pref := substr(lv_pref, 1, instr(lv_pref, '_')-1);
      apex_debug_message.log_message(': '||lv_pref);

      SELECT report_id
        INTO v_report_id
        FROM apex_application_page_ir_rpt
       WHERE application_id = p_app_id
         AND page_id = p_page_id
         AND base_report_id = lv_pref
         AND session_id = p_session_id;
      WHEN no_data_found THEN
         apex_debug_message.log_message('no IR id could be found. Check input parameters. -> end');
   v_report_id := p_report_id;

The returned preference value could look like ‘3522014783654717____X’. We’re only concerned with the first value as this is the id of the currently active report. Note that the preference name uses a report id: this is the id of the interactive report itself and not of a saved or session instanced version. The id we get from the preference is the id of the SAVED version of the report that is instanced for the session of the user. If you take a look at how the ids and saved reports work below you’ll understand.

There is a lot of writing I have done already on these report structures, and there is more in the comments in the package, but here is another illustration:

Metadata for Interactive Reports
Interactive Report Id:
Region Id:

Metadata for saved reports and session instances of those
For example (from my sample app on

•    Reports with no session_id are saved reports.
•    All reports are based on the same interactive report
•    All reports have a unique id: REPORT_ID
•    Default (Primary) and alternative have an own application user. Named reports use the name of the creator evidently. Note that in my example app the report "Only Space Two" is a PRIVATE report! You'll see this of course when you log in with the test user.
•    My own instances of the reports are in the view aswell. You can identify them because they have a session_id, base_report_id and their report_type.
•    The base_report_id is the report_id of the report it is based on. "Report it is based on": the report_id of a saved version of the interactive report

Debugging from sql command line is still possible. There are two changes in the parameter list: p_app_user and p_report_id have been added. App_user is straightforward, but p_report_id is not. Leaving p_report_id blank from a command line to resolve the report_id is not possible. The preference fetch will only work in an apex session. You’ll have to retrieve the id yourself.
To do this, basically take the code block above, but query wwv_flow_preferences$  instead of using apex_util.
SELECT attribute_value
FROM apex_040100.wwv_flow_preferences$
AND preference_name like 'FSP_IR_130_P11%'

Use preference_name like 'FSP_IR_130_P11_W5555555555' if you have retrieved the base interactive report id (that’d be from apex_application_page_ir). And of course, substitute the application and page id!

   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_report_id => 55555555555555,
      p_app_user => ‘YOUR_APEX_USERNAME’, --APP_USER in apex session
      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 );
Download: HERE


  1. Hi Tom,

    Thanks for your efforts here! I'll be sure to put them to use soon...


  2. Tom,
    Since Oracle's implementation has some issues (returning the linking column from an interactive report {Always a null column} and taking aggregate rows and building them as additional columns to your query, I have started using your package instead..

    So is it possible to get a statement from you that it is acceptable to use this package, that there are no licensing issues and such? Just need to be sure since the client iI am working for is a Gov't agency....

    Thanks again!!

    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

    1. Absolutely fine to use Tony. I know there are no license thingies up anywhere but it's good to use. +1 for community! I'll make some time to put up my plugin alongside the code on my github repo here: