Oracle APEX: Auditing User-Requested Data

Recently, I was asked how to track the data displayed to users on Reports and Forms in Oracle APEX. The requirement was to prevent displaying any data before search/filter criteria are entered, and to limit the number of rows shown. After some research, I discovered that combining APEX views with the apex_region.open_query_context built-in function can be a powerful solution to address this challenge.

This post will focus specifically on retrieving Report and Form data shown to the user.

Understanding the apex_region.open_query_context Function

The apex_region.open_query_context function returns APEX_EXEC query context returning current region data. Only native region types are supported. More about function here.

Here’s an example of how to use it:

DECLARE
       l_context apex_exec.t_context;
BEGIN
       l_context := apex_region.open_query_context (
              p_page_id => 1,
              p_region_id => 2505704029884282 );

       while apex_exec.next_row(l_context)
       loop

          sys.dbms_output.put_line(
            'ENAME: ' || apex_exec.get_varchar2( p_context => l_context, p_column_name => 'ENAME') ||
            'MGR: ' || apex_exec.get_varchar2( p_context => l_context, p_column_name => 'MGR') ||
            'SAL: ' || apex_exec.get_varchar2( p_context => l_context, p_column_name => 'SAL') 
          );

       end loop;
       apex_exec.close(l_context);
END;

Or an example to how to get it in the JSON format:

DECLARE
       l_context apex_exec.t_context;
BEGIN
       l_context := apex_region.open_query_context (
              p_page_id => 1,
              p_region_id => 2505704029884282);

       apex_json.open_object;
       apex_json.write_context( 'data', l_context );
       apex_json.close_object;
END;

As you can see in the code above, function is called with two parameters:

• p_page_id, which represents the ID of the page where the report is located.

• p_region_id, which is the internal ID of the report.

To avoid hardcoding the region_id, a simple query to the APEX view will provide that information.

Retrieving the region_id from APEX views

Before executing the query, it’s essential to set a Static ID for the report region.

Once you’ve set the Static ID, you can write a query to retrieve the region_id from the apex_application_page_regions APEX view:

select region_id
from apex_application_page_regions
where 1 = 1
  and application_id = :APP_ID
  and page_id = :APP_PAGE_ID
  and static_id = 'EMP_RPT';

Example Output of the apex_region.open_query_context Function

Assume you have a report that displays 3 rows of data.

Below is an example of the JSON output from the apex_region.open_query_context function:

{
   "data":[
      {
         "EMPNO":7698,
         "ENAME":"BLAKE",
         "JOB":"MANAGER",
         "MGR":7839,
         "HIREDATE":"1981-05-01T00:00:00Z",
         "SAL":2850,
         "DEPTNO":30
      },
      {
         "EMPNO":7782,
         "ENAME":"CLARK",
         "JOB":"MANAGER",
         "MGR":7839,
         "HIREDATE":"1981-06-09T00:00:00Z",
         "SAL":2450,
         "DEPTNO":10
      },
      {
         "EMPNO":7839,
         "ENAME":"KING",
         "JOB":"PRESIDENT",
         "HIREDATE":"1981-11-17T00:00:00Z",
         "SAL":5000,
         "DEPTNO":10
      }
   ]
}

Now, you can have a Process or Dynamic Action to to save output to your audit table.

Auditing Data from Form Regions

You can also track data displayed via Form regions. Here’s an example of how to audit the Form region data.

begin
    for set_items in (select item_name
                              from apex_application_page_items
                              where 1 = 1 
                                and application_id = v('APP_ID')
                                and page_id = v('APP_PAGE_ID')
                                and display_as != 'Hidden')
    loop

        insert into your_audit_table (item_name, item_value, ...)
        values (set_items.item_name, v(set_items.item_name), ...);

    end loop;

end;

This PL/SQL block iterates through the form items, inserts each item's name and value into an audit table, and allows you to track the data displayed to the user.

Enjoy auditing your APEX applications! :)