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

Note 3: I haven't found a way to get columns that are hidden from the user to still be included in the resulting query.
In the example I have tried a hidden column, 2 columns that are not shown because of server-side conditions, and a column that is not shown because of an authorization scheme.
None of these are included in the resulting query, excpet for the Hidden column - and that one is excluded in the final select statement, so that doesn't help.
This can be annoying but it also seems logical - if columns are not needed in the final result, there is simply no reason to include them. Unless you want to use them for something else of course, in which case you seem to be out of luck here.
Yuri tried to show hidden columns with the apex_region.open_query_context option mentioned above, but that seems to behave in the same way - hidden columns are not included.
The only thing I can think of next is to fill a collection first (including all the "hidden" columns), and then use that to both show the report AND access the hidden columns too. That's not elegant of course, but it could work.

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	Interactive Report
	Region Source	select emp.* , 'hidden' as hidden , 'ssc_never' as ssc_never , 'ssc_0_is_1' as ssc_0_is_1 , 'never_authorized' as never_authorized from emp

Report Column

Column Definition
	Column Name	HIDDEN
	Display Type	HIDDEN_ESCAPE_SC
	Column Heading	Hidden

Report Column

Column Definition
	Column Name	SSC_NEVER
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Ssc Never
Server-Side Condition
	Type	Never

Report Column

Column Definition
	Column Name	SSC_0_IS_1
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Ssc 0 Is 1
Server-Side Condition
	Type	Expression
	Language	PLSQL
	PL/SQL Expression	0 = 1

Report Column

Column Definition
	Column Name	NEVER_AUTHORIZED
	Display Type	ESCAPE_SC (Escape special characters)
	Column Heading	Never Authorized
Security
	Authorization Scheme	Never Authorized

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

Identification
	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