Skip Headers
Oracle® Application Express API Reference
Release 4.2

E35127-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to current chapter
Up
Go to next page
Next
PDF · Mobi · ePub

11 APEX_IR

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.

Topics:


ADD_FILTER Procedure Signature 1

This procedure creates a filter on an interactive report using a report ID.

Syntax

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);

Parameters

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

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_column

Name of the report SQL column, or column alias, to be filtered.

p_filter_value

The filter value. This value is not used for N and NN.

p_operator_abbr

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

p_report_id

The saved report ID within the current application page. If p_report_id is null, it adds the filter to the last viewed report settings.


Example

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;

ADD_FILTER Procedure Signature 2

This procedure creates a filter on an interactive report using a report alias.

Syntax

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);

Parameters

Table 11-2 describes the available parameters for the ADD_FILTER Procedure Signature 2.

Table 11-2 ADD_FILTER Procedure Signature 2

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_column

Name of the report SQL column, or column alias, to be filtered.

p_filter_value

This is the filter value. This value is not used for N and NN.

p_operator_abbr

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

p_report_alias

The saved report alias within the current application page. If p_report_alias is null, it adds filter to the last viewed report settings.


Example

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;

CHANGE_SUBSCRIPTION_LANG Procedure

This procedure changes the interactive report subscription language.

Syntax

APEX_IR.CHANGE_SUBSCRIPTION_LANG(
    p_subscription_id IN NUMBER,
    p_language        IN VARCHAR2);

Parameters

Table 11-3 describes the available parameters for the

Table 11-3 CHANGE_SUBSCRIPTION_LANG Procedure Parameters

Parameter Description

p_subscription_id

Subscription ID to change the language within the current workspace.

p_language

This is an IANA language code. Some examples include: en, de, de-at, zh-cn, and pt-br.


Example

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;

CLEAR_REPORT Procedure Signature 1

This procedure clears report settings using the report ID.

Syntax

APEX_IR.CLEAR_REPORT(
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER,
    p_report_id IN NUMBER DEFAULT NULL);

Parameters

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

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 clears the last viewed report settings.


Example

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;

CLEAR_REPORT Procedure Signature 2

This procedure clears report settings using report alias.

Syntax

APEX_IR.CLEAR_REPORT(
    p_page_id      IN NUMBER,
    p_region_id    IN NUMBER,
    p_report_alias IN VARCHAR2 DEFAULT NULL);

Parameters

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

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_alias

The saved report alias within the current application page. If p_report_alias is null, it clears the last viewed report settings.


Example

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;

DELETE_REPORT Procedure

This procedure deletes saved interactive reports. It deletes all saved reports except the Primary Default report.

Syntax

APEX_IR.DELETE_REPORT(
    p_report_id IN NUMBER);

Parameters

Table 11-6 describes the available parameters for the DELETE_REPORT procedure.

Table 11-6 DELETE_REPORT Procedure Parameters

Parameter Description

p_report_id

Report ID to delete within the current Application Express application.


Example

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;

DELETE_SUBSCRIPTION Procedure

This procedure deletes interactive report subscriptions.

Syntax

APEX_IR.DELETE_SUBSCRIPTION(
    p_subscription_id IN NUMBER);

Parameters

Table 11-7 describes the available parameters for the DELETE_SUBSCRIPTION procedure.

Table 11-7 DELETE_SUBSCRIPTION Procedure Parameters

Parameter Description

p_subscription_id

Subscription ID to delete within the current workspace.


Example

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;

GET_LAST_VIEWED_REPORT_ID Function

This function returns the last viewed base report ID of the specified page and region.

Syntax

APEX_IR.GET_LAST_VIEWED_REPORT_ID(
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER);

Parameters

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

p_page_id

Page of the current Application Express application that contains an interactive report.

p_region_id

The interactive report region ID.


Example

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;

GET_REPORT Function

This function returns an interactive report runtime query.

Syntax

APEX_IR.GET_REPOR(
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER,
    p_report_id IN NUMBER DEFAULT NULL);

Parameters

Table 11-9 describes the available parameters for the GET_REPORT function.

Table 11-9 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.


Example

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;
    for i in 1..l_report.binds.count
    loop
        dbms_output.put_line(i||'. '||
                             l_report.binds(i).name||
                             '='||l_report.binds(i).value);
    end loop;
END; 

RESET_REPORT Procedure Signature 1

This procedure resets report settings to the developer defined default settings using the report ID.

Syntax

APEX_IR.RESET_REPORT(
    p_page_id   IN NUMBER,
    p_region_id IN NUMBER,
    p_report_id IN NUMBER DEFAULT NULL); 

Parameters

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

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 resets the last viewed report settings.


Example

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;

RESET_REPORT Procedure Signature 2

This procedure resets report settings using the report alias.

Syntax

APEX_IR.RESET_REPORT(
    p_page_id      IN NUMBER,
    p_region_id    IN NUMBER,
    p_report_alias IN VARCHAR2 DEFAULT NULL);

Parameters

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

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_alias

The saved report alias within the current application page. If p_report_alias is null, it resets the last viewed report settings.


Example

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;