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.
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
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…
- use APEX_IR.GET_REPORT to fetch the underlying query and the associated binds.
- 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
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.