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.
This procedure creates a filter on an interactive report using a report ID.
Note:
The use of this procedure in a page rendering process causes report download issues (CSV, HTML, Email, and so on). When a user downloads the report, the interactive report reloads the page with download format in the REQUEST value. Any interactive report settings changes (such as add filter or reset report) are done in partial page refresh. Thus, the download data may not match the report data user is seeing. For this reason, Oracle recommends only using this procedure in a page submit process.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 12-1 describes the available parameters for the ADD_FILTER procedure signature 1.
Table 12-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:
The use of this procedure in a page rendering process causes report download issues (CSV, HTML, Email, and so on). When a user downloads the report, the interactive report reloads the page with download format in the REQUEST value. Any interactive report settings changes (such as add filter or reset report) are done in partial page refresh. Thus, the download data may not match the report data user is seeing. For this reason, Oracle recommends only using this procedure in a page submit process.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 12-2 describes the available parameters for the ADD_FILTER Procedure Signature 2.
Table 12-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 interactive report subscriptions email address. When an email is sent out, the subscription sends message to the defined email address.
APEX_IR.CHANGE_SUBSCRIPTION_EMAIL ( p_subscription_id IN NUMBER, p_email_address IN VARCHAR2);
Table 12-3 describes the parameters available in CHANGE_SUBSCRIPTION_EMAIL
procedure.
Table 12-3 CHANGE_SUBSCRIPTION_EMAIL Parameters
Parameter | Description |
---|---|
|
Subscription ID to change the email address within the current workspace. |
|
The new email address to change to. The email address needs to be a valid email syntax and cannot be set to null. |
The following example shows how to use CHANGE_SUBSCRIPTION_EMAIL
procedure to change the email address to some.user@somecompany.com
for the interactive report subscription 956136850459718525.
BEGIN APEX_IR.CHANGE_SUBSCRIPTION_EMAIL ( p_subscription_id => 956136850459718525, p_email_address => 'some.user@somecompany.com'); END;
This procedure changes the owner of a saved interactive report using a report ID. This procedure cannot change the owner of default interactive reports.
APEX_IR.CHANGE_REPORT_OWNER ( p_report_id in number, p_old_owner in varchar2, p_new_owner in varchar2);
Table 12-4 describes the available parameters for the CHANGE_REPORT_OWNER procedure.
Table 12-4 CHANGE_REPORT_OWNER Procedure
Parameters | Description |
---|---|
|
The saved report ID within the current application page. |
|
The previous owner name to change from (case sensitive). The owner needs to a valid login user accessing the report. |
|
The new owner name to change to (case sensitive). The owner must be a valid login user accessing the report. |
This example shows how to use CHANGE_REPORT_OWNER
procedure to change the old owner name of JOHN to the new owner name of JOHN.DOE for a saved report. The saved report has a report ID of 1235704029884282.
BEGIN APEX_IR.CHANGE_REPORT_OWNER ( p_report_id => 1235704029884282, p_old_owner => 'JOHN', p_new_owner => 'JOHN.DOE'); END;
This procedure changes interactive report subscriptions email address. When an email is sent out, the subscription sends message to the defined email address.
APEX_IR.CHANGE_SUBSCRIPTION_EMAIL ( p_subscription_id IN NUMBER, p_email_address IN VARCHAR2);
Table 12-5 describes the parameters available in CHANGE_SUBSCRIPTION_EMAIL
procedure.
Table 12-5 CHANGE_SUBSCRIPTION_EMAIL Parameters
Parameter | Description |
---|---|
|
Subscription ID to change the email address within the current workspace. |
|
The new email address to change to. The email address needs to be a valid email syntax and cannot be set to null. |
The following example shows how to use CHANGE_SUBSCRIPTION_EMAIL
procedure to change the email address to some.user@somecompany.com
for the interactive report subscription 956136850459718525.
BEGIN APEX_IR.CHANGE_SUBSCRIPTION_EMAIL ( p_subscription_id => 956136850459718525, p_email_address => 'some.user@somecompany.com'); END;
This procedure changes the interactive report subscription language.
APEX_IR.CHANGE_SUBSCRIPTION_LANG( p_subscription_id IN NUMBER, p_language IN VARCHAR2);
Table 12-6 describes the available parameters for the
Table 12-6 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:
The use of this procedure in a page rendering process causes report download issues (CSV, HTML, Email, and so on). When a user downloads the report, the interactive report reloads the page with download format in the REQUEST value. Any interactive report settings changes (such as add filter or reset report) are done in partial page refresh. Thus, the download data may not match the report data user is seeing. For this reason, Oracle recommends only using this procedure in a page submit process.APEX_IR.CLEAR_REPORT( p_page_id IN NUMBER, p_region_id IN NUMBER, p_report_id IN NUMBER DEFAULT NULL);
Table 12-7 describes the available parameters for the CLEAR_REPORT procedure signature 1.
Table 12-7 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:
The use of this procedure in a page rendering process causes report download issues (CSV, HTML, Email, and so on). When a user downloads the report, the interactive report reloads the page with download format in the REQUEST value. Any interactive report settings changes (such as add filter or reset report) are done in partial page refresh. Thus, the download data may not match the report data user is seeing. For this reason, Oracle recommends only using this procedure in a page submit process.APEX_IR.CLEAR_REPORT( p_page_id IN NUMBER, p_region_id IN NUMBER, p_report_alias IN VARCHAR2 DEFAULT NULL);
Table 12-8 describes the available parameters for the CLEAR_REPORT
procedure signature 2.
Table 12-8 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 a specific saved report in the current logged in workspace and application.
APEX_IR.DELETE_REPORT( p_report_id IN NUMBER);
Table 12-9 describes the available parameters for the DELETE_REPORT
procedure.
Table 12-9 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 12-10 describes the available parameters for the DELETE_SUBSCRIPTION
procedure.
Table 12-10 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 12-11 describes the available parameters for the GET_LAST_VIEWED_REPORT_ID function.
Table 12-11 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_REPORT( p_page_id IN NUMBER, p_region_id IN NUMBER, p_report_id IN NUMBER DEFAULT NULL);
Table 12-12 describes the available parameters for the GET_REPORT function.
Table 12-12 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:
The use of this procedure in a page rendering process causes report download issues (CSV, HTML, Email, and so on). When a user downloads the report, the interactive report reloads the page with download format in the REQUEST value. Any interactive report settings changes (such as add filter or reset report) are done in partial page refresh. Thus, the download data may not match the report data user is seeing. For this reason, Oracle recommends only using this procedure in a page submit process.APEX_IR.RESET_REPORT( p_page_id IN NUMBER, p_region_id IN NUMBER, p_report_id IN NUMBER DEFAULT NULL);
Table 12-13 describes the available parameters for the RESET_REPORT
procedure signature 1.
Table 12-13 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:
The use of this procedure in a page rendering process causes report download issues (CSV, HTML, Email, and so on). When a user downloads the report, the interactive report reloads the page with download format in the REQUEST value. Any interactive report settings changes (such as add filter or reset report) are done in partial page refresh. Thus, the download data may not match the report data user is seeing. For this reason, Oracle recommends only using this procedure in a page submit process.APEX_IR.RESET_REPORT( p_page_id IN NUMBER, p_region_id IN NUMBER, p_report_alias IN VARCHAR2 DEFAULT NULL);
Table 12-14 describes the available parameters for the RESET_REPORT procedure signature 2.
Table 12-14 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;