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
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
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.
So in conclusion: you have a working example of how to…
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.