Method to use the query of an Interactive Report, including all user defined filters, sort orders, etc., in a pl/sql procedure.
Change the settings of the IR and then click the Run Procedure button to see the results of the pl/sql procedure.

A similar (but more complex) method to get the query of a Classic Report can be found on the Get Classic Report query page.

Note 1: make sure the columns you use in your code are always present in the query result. In this example, columns EMPNO and JOB are used in the code and therefore these columns are set "not hide-able" for the user.

Note 2: In recent Apex versions the apex_ir.get_report function has been deprecated - thanks to Yuri for pointing out that one!
The documentation states that you should use apex_region.open_query_context instead, but that is not really a 1-on-1 replacement... This post gives an example of a way of using that: https://srihariravva.blogspot.com/2021/10/interactive-grid-process-filtered-data.html

Page

Identification
	Page ID	1018
	Name	Apex - Reports - Use IR results in pl/sql

Region

Identification
	Sequence	20
	Title	IR
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	select emp.* from emp

Button

Name
	Button Name	P1018_RUN_PROCEDURE
	Text Label / Alt	Run Procedure
Displayed
	Sequence	10
	Display in Region	Results
Behavior
	Action	Submit Page
	Execute Validations	Yes

Process

Name
	Button Name	P1018_THE_PROCEDURE
	Type	PL/SQL anonymous block
Process Point
	Sequence	10
	Process Point	On Submit - After Computations and Validations
	Run Process	Once Per Page Visit (default)
Source
	Process	declare c_lf varchar2(2) := chr(13) || chr(10); l_result varchar2(32767); cur number; res number; number_tab dbms_sql.number_table; varchar2_tab dbms_sql.varchar2_table; description_tab dbms_sql.desc_tab; l_col_count number; l_row_count number; l_colval varchar2(4000); l_region_id apex_application_page_regions.region_id%type; l_report apex_ir.t_report; begin select region_id into l_region_id from apex_application_page_regions where page_id = v('APP_PAGE_ID') and application_id = v('APP_ID') and source_Type = 'Interactive Report' ; l_result := l_result || 'l_region_id=' || l_region_id || c_lf; l_report := apex_ir.get_report ( p_page_id => v('APP_PAGE_ID') , p_region_id => l_region_id ) ; l_result := l_result || c_lf; l_result := l_result || c_lf; l_result := l_result || '### QUERY' || c_lf; l_result := l_result || c_lf; l_result := l_result || 'l_report.sql_query =' || c_lf || l_report.sql_query || c_lf; cur := dbms_sql.open_cursor; dbms_sql.parse(cur, l_report.sql_query, dbms_sql.native); l_result := l_result || c_lf; l_result := l_result || c_lf; l_result := l_result || '### BINDS' || c_lf; l_result := l_result || c_lf; for i in 1 .. l_report.binds.count loop l_result := l_result || 'i=' || i || ' - '; l_result := l_result || 'l_report.binds(i).name=' || l_report.binds(i).name || ' - '; l_result := l_result || 'l_report.binds(i).value=' || l_report.binds(i).value || c_lf; dbms_sql.bind_variable(c => cur, name => l_report.binds(i).name, value => l_report.binds(i).value); end loop; dbms_sql.describe_columns(c => cur, col_cnt => l_col_count, desc_t => description_tab); for i in 1 .. l_col_count loop dbms_sql.define_column( cur, i, l_colval, 4000 ); end loop; l_result := l_result || c_lf; l_result := l_result || c_lf; l_result := l_result || '### COLUMNS' || c_lf; l_result := l_result || c_lf; for i in 1 .. l_col_count loop l_result := l_result || 'i=' || i || ' - '; l_result := l_result || 'description_tab(i).col_name=' || description_tab(i).col_name || ' - '; l_result := l_result || 'description_tab(i).col_type=' || description_tab(i).col_type || c_lf; if description_tab(i).col_name = 'EMPNO' then l_result := l_result || '### COLUMN: EMPNO' || c_lf; res := dbms_sql.execute(cur); l_result := l_result || 'res=' || res || c_lf; -- print rows l_row_count := 0; while ( dbms_sql.fetch_rows( cur ) > 0 ) loop l_row_count := l_row_count + 1; l_result := l_result || 'l_row_count=' || l_row_count || ' - '; dbms_sql.column_value( cur, i, l_colval ); l_result := l_result || 'l_colval=' || l_colval || c_lf; number_tab(l_row_count) := l_colval; end loop; l_result := l_result || '###' || c_lf; end if; if description_tab(i).col_name = 'JOB' then l_result := l_result || '### COLUMN: JOB' || c_lf; res := dbms_sql.execute(cur); l_result := l_result || 'res=' || res || c_lf; -- print rows l_row_count := 0; while ( dbms_sql.fetch_rows( cur ) > 0 ) loop l_row_count := l_row_count + 1; l_result := l_result || 'l_row_count=' || l_row_count || ' - '; dbms_sql.column_value( cur, i, l_colval ); l_result := l_result || 'l_colval=' || l_colval || c_lf; varchar2_tab(l_row_count) := l_colval; end loop; l_result := l_result || '###' || c_lf; end if; end loop; dbms_sql.close_cursor(cur); if l_row_count > 0 then for i in number_tab.first .. number_tab.last loop -- Put your code here, using number_tab(i) and varchar2_tab(i) null; end loop; end if; apex_util.set_session_state ('P1018_RESULTS', l_result); end;
Conditions
	When Button Pressed	P1018_RUN_PROCEDURE