The APEX_IR package provides utilities you can use when programming in the Oracle Application Express environment related to interactive reports. You can use the APEX_IR package to get an interactive report runtime query, add filters, reset or clear report settings, delete saved reports and manage subscriptions.
APEX_IR.ADD_FILTER(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_column IN VARCHAR2,
p_filter_value IN VARCHAR2,
p_operator_abbr IN VARCHAR2 DEFAULT NULL,
p_report_id IN NUMBER DEFAULT NULL);
Table 11-1 describes the available parameters for the ADD_FILTER procedure signature 1.
Table 11-1 ADD_FILTER Procedure Signature 1 Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region (ID). |
|
|
Name of the report SQL column, or column alias, to be filtered. |
|
|
The filter value. This value is not used for N and NN. |
|
|
Filter type. Valid values are as follows: EQ = Equals NEQ = Not Equals LT = Less than LTE = Less then or equal to GT = Greater Than GTE = Greater than or equal to LIKE = SQL Like operator NLIKE = Not Like N = Null NN = Not Null C = Contains NC = Not Contains IN = SQL In Operator NIN = SQL Not In Operator |
|
|
The saved report ID within the current application page. If |
The following example shows how to use the ADD_FILTER procedure to filter the interactive report with report ID of 880629800374638220 in page 1, region 2505704029884282 of the current application with DEPTNO equals 30.
BEGIN
APEX_IR.ADD_FILTER(
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_column => 'DEPTNO',
p_filter_value => '30',
p_operator_abbr => 'EQ',
p_report_id => 880629800374638220);
END;
This procedure creates a filter on an interactive report using a report alias.
Note:
This procedure should only be used in page submit processes.
APEX_IR.ADD_FILTER(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_column IN VARCHAR2,
p_filter_value IN VARCHAR2,
p_operator_abbr IN VARCHAR2 DEFAULT NULL,
p_report_alias IN VARCHAR2 DEFAULT NULL);
Table 11-2 describes the available parameters for the ADD_FILTER Procedure Signature 2.
Table 11-2 ADD_FILTER Procedure Signature 2
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region (ID). |
|
|
Name of the report SQL column, or column alias, to be filtered. |
|
|
This is the filter value. This value is not used for N and NN. |
|
|
Filter type. Valid values are as follows: EQ = Equals NEQ = Not Equals LT = Less than LTE = Less then or equal to GT = Greater Than GTE = Greater than or equal to LIKE = SQL Like operator NLIKE = Not Like N = Null NN = Not Null C = Contains NC = Not Contains IN = SQL In Operator NIN = SQL Not In Operator |
|
|
The saved report alias within the current application page. If |
The following example shows how to use the ADD_FILTER procedure to filter an interactive report with a report alias of CATEGORY_REPORT in page 1, region 2505704029884282 of the current application with DEPTNO equals 30.
BEGIN
APEX_IR.ADD_FILTER(
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_column => 'DEPTNO',
p_filter_value => '30',
p_operator_abbr => 'EQ',
p_report_alias => 'CATEGORY_REPORT');
END;
This procedure changes the interactive report subscription language.
APEX_IR.CHANGE_SUBSCRIPTION_LANG(
p_subscription_id IN NUMBER,
p_language IN VARCHAR2);
Table 11-3 describes the available parameters for the
Table 11-3 CHANGE_SUBSCRIPTION_LANG Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Subscription ID to change the language within the current workspace. |
|
|
This is an IANA language code. Some examples include: |
The following example shows how to use the CHANGE_SUBSCRIPTION_LANG procedure to change the subscription with the ID of 567890123 to German in the current workspace.
BEGIN
APEX_IR.CHANGE_SUBSCRIPTION_LANG(
p_subscription_id => 567890123,
p_language => 'de');
END;
This procedure clears report settings using the report ID.
Note:
This procedure should only be used in page submit processes.
APEX_IR.CLEAR_REPORT(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_id IN NUMBER DEFAULT NULL);
Table 11-4 describes the available parameters for the CLEAR_REPORT procedure signature 1.
Table 11-4 CLEAR_REPORT Procedure Signature 1 Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region (ID). |
|
|
The saved report ID within the current application page. If |
The following example shows how to use the CLEAR_REPORT procedure to clear interactive report settings with a report ID of 880629800374638220 in page 1, region 2505704029884282 of the current application.
BEGIN
APEX_IR.CLEAR_REPORT(
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_id => 880629800374638220);
END;
This procedure clears report settings using report alias.
Note:
This procedure should only be used in page submit processes.
APEX_IR.CLEAR_REPORT(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_alias IN VARCHAR2 DEFAULT NULL);
Table 11-5 describes the available parameters for the CLEAR_REPORT procedure signature 2.
Table 11-5 CLEAR_REPORT Procedure Signature 2 Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region (ID). |
|
|
The saved report alias within the current application page. If |
The following example shows how to use the CLEAR_REPORT procedure to clear interactive report settings with report alias of CATEGORY_REPORT in page 1, region 2505704029884282 of the current application.
BEGIN
APEX_IR.CLEAR_REPORT(
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_alias => 'CATEGORY_REPORT');
END;
This procedure deletes saved interactive reports. It deletes all saved reports except the Primary Default report.
APEX_IR.DELETE_REPORT(
p_report_id IN NUMBER);
Table 11-6 describes the available parameters for the DELETE_REPORT procedure.
Table 11-6 DELETE_REPORT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Report ID to delete within the current Application Express application. |
The following example shows how to use the DELETE_REPORT procedure to delete the saved interactive report with ID of 880629800374638220 in the current application.
BEGIN
APEX_IR.DELETE_REPORT (
p_report_id => 880629800374638220);
END;
This procedure deletes interactive report subscriptions.
APEX_IR.DELETE_SUBSCRIPTION(
p_subscription_id IN NUMBER);
Table 11-7 describes the available parameters for the DELETE_SUBSCRIPTION procedure.
Table 11-7 DELETE_SUBSCRIPTION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Subscription ID to delete within the current workspace. |
The following example shows how to use the DELETE_SUBSCRIPTION procedure to delete the subscription with ID of 567890123 in the current workspace.
BEGIN
APEX_IR.DELETE_SUBSCRIPTION(
p_subscription_id => 567890123);
END;
This function returns the last viewed base report ID of the specified page and region.
APEX_IR.GET_LAST_VIEWED_REPORT_ID(
p_page_id IN NUMBER,
p_region_id IN NUMBER);
Table 11-8 describes the available parameters for the GET_LAST_VIEWDED_REPORT_ID function.
Table 11-8 GET_LAST_VIEWED_REPORT_ID Function Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region ID. |
The following example shows how to use the GET_LAST_VIEWED_REPORT_ID function to retrieve the last viewed report ID in page 1, region 2505704029884282 of the current application.
DECLARE
l_report_id number;
BEGIN
l_report_id := APEX_IR.GET_LAST_VIEWED_REPORT_ID (
p_page_id => 1,
p_region_id => 2505704029884282);
END;
This function returns an interactive report runtime query.
APEX_IR.GET_REPOR(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_id IN NUMBER DEFAULT NULL);
Table 11-9 describes the available parameters for the GET_REPORT function.
Table 11-9 GET_REPORT Function Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region ID. |
|
|
The saved report ID within the current application page. If |
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;
This procedure resets report settings to the developer defined default settings using the report ID.
Note:
This procedure should only be used in page submit processes.
APEX_IR.RESET_REPORT(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_id IN NUMBER DEFAULT NULL);
Table 11-10 describes the available parameters for the RESET_REPORT procedure signature 1.
Table 11-10 RESET_REPORT Procedure Signature 1 Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region ID. |
|
|
The saved report ID within the current application page. If |
The following example shows how to use the RESET_REPORT procedure signature 1 to reset interactive report settings with report ID of 880629800374638220 in page 1, region 2505704029884282 of the current application.
BEGIN
APEX_IR.RESET_REPORT(
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_id => 880629800374638220);
END;
This procedure resets report settings using the report alias.
Note:
This procedure should only be used in page submit processes.
APEX_IR.RESET_REPORT(
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_report_alias IN VARCHAR2 DEFAULT NULL);
Table 11-11 describes the available parameters for the RESET_REPORT procedure signature 2.
Table 11-11 RESET_REPORT Procedure Signature 2 Parameters
| Parameter | Description |
|---|---|
|
|
Page of the current Application Express application that contains an interactive report. |
|
|
The interactive report region ID. |
|
|
The saved report alias within the current application page. If |
The following example shows how to use the RESET_REPORT procedure to reset interactive report settings with a report alias of CATEGORY_REPORT in page 1, region 2505704029884282 of the current application.
BEGIN
APEX_IR.RESET_REPORT(
p_page_id => 1,
p_region_id => 2505704029884282,
p_report_alias => 'CATEGORY_REPORT');
END;