23.12 GET_REPORT Function

This function returns an interactive report runtime query.

Syntax

APEX_IR.GET_REPORT(
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER,
    p_report_id IN NUMBER DEFAULT NULL,
    p_view      IN VARCHAR2 DEFAULT C_VIEW_REPORT );

Parameters

Table 23-12 GET_REPORT Function Parameters

Parameter	Description
p_page_id	Page of the current Application Express application that contains an interactive report.
p_region_id	The interactive report region ID.
p_report_id	The saved report ID within the current application page. If p_report_id is null, it gets last viewed report query.
p_view	The view type available for the report. The values can be APEX_IR.C_VIEW_REPORT, APEX_IR.C_VIEW_GROUPBY or APEX_IR.C_VIEW_PIVOT. If p_view is null, it gets the view currently used by the report. If p_view passed which doesn’t exist for the current report, an error is raised.

Example 1

The following example shows how to use the GET_REPORT function to retrieve the runtime report query with bind variable information with report ID of 880629800374638220 in page 1, region 2505704029884282 of the current application.

DECLARE
   l_report apex_ir.t_report;
   l_query varchar2(32767);
BEGIN 
    l_report := APEX_IR.GET_REPORT (
                    p_page_id => 1,
                    p_region_id => 2505704029884282,
                    p_report_id => 880629800374638220);
    l_query := l_report.sql_query;
    sys.htp.p('Statement = '||l_report.sql_query);
    for i in 1..l_report.binds.count
    loop
        sys.htp.p(i||'. '||l_report.binds(i).name||' = '||l_report.binds(i).value);
    end loop;
END;

Example 2

The following example shows how to use the GET_REPORT function to retrieve Group By view query defined in the current report page with region 2505704029884282.

DECLARE
   l_report APEX_IR.T_REPORT;
BEGIN
   l_report := APEX_IR.GET_REPORT (
                   p_page_id        => :APP_PAGE_ID,
                   p_region_id      => 2505704029884282,
                   p_view           => APEX_IR.C_VIEW_GROUPBY );
   sys.htp.p( 'Statement = '||l_report.sql_query );
END;