A Reference APEX Application for APEX_IR.GET_REPORT

On occasion, I encounter the need to use APEX_IR.GET_REPORT and I thought it would be a good idea to create a reference APEX application that includes handy examples which I can refer back to. There are also plenty of blogs on this subject.

A while ago I created a reference application in which I have now polished and released. This contains several code snippets which you may find useful.

Before I dig in to the detail… let me explain a typical use-case for APEX_IR.GET_REPORT.

A typical requirement may be that the user wishes to view an Interactive Report and then use the Interactive Report filters to narrow down on a subset of records. The user would then then hit a “process records” button which performs some action on the filtered records.

This poses a challenge for the developer as they have to think of a method of obtaining the filtered records. They may consider using JavaScript to scrape the IDs of the records off the page and perform an action with those records. If the Interactive Report is paginated, then this method would fail. Therefore, there are serious flaws with this approach.

The developer may consider somehow fetching and rewriting applied filters to the underlying SQL query – it’s possible this would work, but it’s a mammoth task.

The correct approach is quite simple. By calling APEX_IR.GET_REPORT you’ll receive the rewritten SQL (yes APEX will wrap / rewrite your SQL) which includes the Interactive Report filters together with a table of Bind Names and Values.

Typically, I would then create a collection based on what APEX_IR.GET_REPORT returns and then loop through the members – alternatively you could use DBMS_SQL, describing the columns and then fetching each record. I think the former is simpler.

So let’s look at the reference application and I’ll explain what happening.

Use the link below if you want to follow along with the blog

https://apex.oracle.com/pls/apex/f?p=128464

Interactive Report

In the middle of the screen I have an Interactive Report

I have a P1_SEARCH_FIRST_NAME where I can type a name e.g. Lex

I have a Key-Release Dynamic action so it searches as I type. Basically the true action performs an Interactive Report refresh and the region itself submits P1_SEARCH_FIRE_NAME in to session.

The underlying query to the Interactive Report is

Select * 
  from employees 
 where INSTR(first_name COLLATE BINARY_CI,
             NVL(:P1_SEARCH_FIRST_NAME, first_name)) > 0

The COLLATE BINARY_CI allows me to perform a case insensitive search. If you’ve not understood a word I’ve said so far about APEX_IR.GET_REPORT however you’re inspired to use COLLATE BINARY_CI in your applications, then this blog was definitely worth writing.

Next it’s time to call APEX_IR.GET_REPORT. I do this on the After-Refresh trigger on the Interactive Report.

DECLARE
   l_report                apex_ir.t_report;
   l_emp_region_id         apex_application_page_regions.region_id%TYPE;
   rec_report_name         APEX_APPLICATION_GLOBAL.VC_ARR2;
   rec_report_value        APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN 
    
    SELECT region_id 
      INTO l_emp_region_id
      FROM apex_application_page_regions 
     WHERE application_id = :APP_ID
       AND page_id = :APP_PAGE_ID
       AND static_id = 'emp';

    l_report := APEX_IR.GET_REPORT (
                    p_page_id => :APP_PAGE_ID,
                    p_region_id => l_emp_region_id,
                    p_report_id => NULL);
                    
    :P1_REPORT_SQL := l_report.sql_query;         
 
    FOR i in 1..l_report.binds.count
    LOOP 
     rec_report_name(i):= l_report.binds(i).name;
     rec_report_value(i) := l_report.binds(i).value;
    END LOOP;
  
    apex_collection.create_or_truncate_collection('REPORT_BINDS');
    apex_collection.add_members(
        p_collection_name => 'REPORT_BINDS',
        p_c001 => rec_report_name,
        p_c002 => rec_report_value );
END;

Calling APEX_IR.GET_REPORT gives returns a type of APEX_IR.T_REPORT. This gives me a SQL_QUERY and an array of BINDS (containing both a NAME and a VALUE).

In the above example I put the SQL_QUERY in a page item so I can see what it returns. I create a collection containing the BINDS so I can create a report based on the results and see what I’ve fetched.

As you can see from the results, APEX has rewired my SQL and given me a bind report

select i.*
 from (select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"
from ((select /*+ qb_name(apex$inner) */d."EMPLOYEE_ID",d."FIRST_NAME",d."LAST_NAME",d."EMAIL",d."PHONE_NUMBER",d."HIRE_DATE",d."JOB_ID",d."SALARY",d."COMMISSION_PCT",d."MANAGER_ID",d."DEPARTMENT_ID" from (Select * 
  from employees 
 where INSTR(first_name COLLATE BINARY_CI,
             NVL(:P1_SEARCH_FIRST_NAME, first_name)) > 0
) d
 )) i 
) i where 1=1 
order by "EMPLOYEE_ID" asc nulls last,"JOB_ID" desc nulls first

Let’s try this with some Interactive Report filters

The query is rewritten to

select i.*
 from (select "EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"
from ((select /*+ qb_name(apex$inner) */d."EMPLOYEE_ID",d."FIRST_NAME",d."LAST_NAME",d."EMAIL",d."PHONE_NUMBER",d."HIRE_DATE",d."JOB_ID",d."SALARY",d."COMMISSION_PCT",d."MANAGER_ID",d."DEPARTMENT_ID" from (Select * 
  from employees 
 where INSTR(first_name COLLATE BINARY_CI,
             NVL(:P1_SEARCH_FIRST_NAME, first_name)) > 0
) d
 )) i 
) i where 1=1 

 and "HIRE_DATE">=:apex$f1

 and upper("JOB_ID")like :apex$f2
order by "EMPLOYEE_ID" asc nulls last,"JOB_ID" desc nulls first

Report Binds

And here are my binds

Finally, following our use case… we are now ready to process our records. Let’s simulate this by clicking the [get_report.sql_query] to [Collection] button. This button performs the following PL/SQL

DECLARE
   lv_collection_name           CONSTANT apex_collections.collection_name%TYPE DEFAULT 'COL_EMP';
   rec_names                    apex_application_global.vc_arr2;
   rec_values                   apex_application_global.vc_arr2;
BEGIN 

  SELECT c001,
         c002
         BULK COLLECT 
    INTO rec_names,
         rec_values
    FROM apex_collections
   WHERE collection_name = 'REPORT_BINDS'
   ORDER BY seq_id;
 
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
        p_collection_name => lv_collection_name, 
        p_query => 'WITH data AS ( ' || :P1_REPORT_SQL || ' ) select * FROM data',
        p_names => rec_names,
        p_values => rec_values,
        p_truncate_if_exists => 'YES');
         
  :P1_COLLECTION_COUNT := NVL( APEX_COLLECTION.COLLECTION_MEMBER_COUNT( p_collection_name => lv_collection_name) , 0);
 
END;

In brief, what I’m doing here is bulk fetching the binds in to array types. I’m not really bulk fetching for any performance gain, I’m bulk fetching because its fetching in to the correct data types for APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B.

Following this; I report the success message

I can validate that the collection is correct by examining it in the session information.

Conclusion

So in conclusion: you have a working example of how to…

1. use APEX_IR.GET_REPORT to fetch the underlying query and the associated binds.
2. Use the information returned from APEX_IR.GET_REPORT in a way that’ll allow you to loop through results to process records regardless of pagination.

If you want to use APEX_IR.GET_REPORT for Interactive Grids, then sadly you are out of luck. There is no APEX_IG package as of yet. It’s high up on my wish-list though.

Enjoy the sample application https://apex.oracle.com/pls/apex/f?p=128464

Author: Matt Mulvaney

Job Title: Senior Oracle APEX Development Consultant

Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.