1. Overview
This document will be helpful to find the query in the IR report when the filter condition is applied dynamically. (Source code should be in SQL format ).
2. Technologies and Tools Used
The following technology has been used to get the SQL query with filter condition that is applied to the interactive report.
- Oracle Apex
3. Use Case
When there is a requirement to dynamically frame a sql query with the filter condition that is applied in the interactive report then we can use the below method.
4. Steps with Screenshot
We can achieve this function by using PLSQL package. The step by step process to achieve this is as follows
Step 1: Create a simple interactive report query with EMP table or build your own report query.
Eg: Select * from emp
Step 2: Now set Static ID as “IR_RPT” in the interactive report region.
Step 3: Now Create a text area item as P9_QUERY.
Step 4: Compile the below package.
Note: Oracle apex interactive report’s standard views are used to build the above package.
Step 5: Now create a dynamic action as follows
Event: After Refresh
Selection Type : Region
Region: IR Report(Select interactive report region)
True Action: Set Value
Set type: Sql statement
Sql Statement:
Code:
Select apex_ir_pkg.f_custom_get_ir_sql
(v (‘APP_ID’), — application id (APP_ID)
v (‘APP_SESSION’), — session id (APP_SESSION)
v (‘APP_PAGE_ID’), — Page number of the interactive report
‘USERNAME’, — user (APP_USER)
‘IR_RPT’ — IR Report static ID
) from dual;
Affected Elements:
Selection Type: Item
Item: P9_QUERY
Output:
