There is far too much to write about this idea, so I will address it in parts due to the 4K character limit.

Preferred Solution (Part 1)
There are many types of data access requests in APEX:

• Local (within the same database or via database links) and HTTP-based (in various forms),
• DQL (select) versus DML (insert/update/delete),
• GET (page rendering) versus POST (AJAX) requests,
• And, of course, a wide variety of components- each with its own characteristics (some execute SELECT statements, others may use static values, etc.).

Attempting to deliver full coverage for every possible scenario as a comprehensive, unified solution at once would be difficult. Therefore, I would like to propose a vertical approach: implement a complete, robust solution for a well-defined subset of use cases, while ensuring the design remains extensible for future enhancements.

Specifically, I suggest in one first step focusing on local SELECT statements issued by the following native components when they retrieve "our" business data from "our" database.

Component Types suggestion for initial implementation:

• Select List (SL),
• List of Values (LOV),
• Classic Reports (CR),
• Interactive Reports (IR),
• Interactive Grids (IG) and
• TreeView (TREE).

Data Source Types suggestion for initial implementation:

• Table,
• SQL Query and
• Function Returning SQL Query.

Data Access Types suggestion for initial implementation:

• FETCH_DATA: transfers data to the browser for the main visible part of the component
• FETCH_DATA_WITH_PK: like FETCH_DATA but with primary keys
• FETCH_COLUMN: when data is fetched for one/multiple columns, e.g. fetch_counts of Faceted Search/Smart Filters, sort_widget and narrow of IR, getFilterValues of IG
• DOWNLOAD_PREPARE_FILE: preparation of a file for subsequent download of IR/IG
• DOWNLOAD_GET_FILE: download of a file - previously prepared with DOWNLOAD_PREPARE_FILE but not necessarily, it could also be some kind of (future) direct download.

Preferred Solution (Part 2)

The solution should meet the following guiding principles:

1. Tamper-proof & Synchronous: Logging must occur entirely server-side, within the same request context where the SELECT executes. It must be synchronous: if an exception occurs during logging (e.g., due to a tablespace full error where the logging table resides), no data should be sent to the browser. Instead, the APEX engine should halt and return the standard unhandled exception message - ensuring data is never exposed without a corresponding log entry.

2. Centralized Integration: Developers should enable logging at a single, well-defined point in the application - if you extend the standard activity log then declaratively or if developers may log in custom tables than a new dedicated execution hook or, ideally, the existing "Database Session Cleanup" process. This is a strong candidate because, as required in point 1, any exception thrown here already terminates request processing.

3. Request-Bound, Not Session-Bound: The solution should correctly handle concurrent requests (e.g. initiating a large file download, then a small one with more restricted filters in the same user session). Since the smaller request may complete first, logging must be tied to the individual request context, not the user session, to avoid race conditions. Please pay attention that while developers are already able to define the storage of the session state of Items "Per Request (Memory Only)", there is currently no such method for the filters of IR/IG which are persistently stored "Per User" or "Per Session" in their own tables, but not "Per Request". Therefore, the solution should also provide the IR/IG filters used in query construction - even though they are stored in the database and one might assume developers could retrieve them on their own.

4. Support for Request Types & Multiple Components: Should work reliably for both GET (page render) and POST (ajax) requests, including cases where multiple components trigger data access in a single request (e.g. two Classic Reports on one page without lazy load -> two distinct log records).

5. Sufficient Contextual Detail: Each log entry should include:

   1. Component identifier (e.g. static ID),
   2. Number of rows fetched (fetchedRowCount),
   3. Total available rows (totalRowCount, if applicable and configured - no additional query if not, than empty),
   4. Filters/search conditions: search text (for LOVs), active facets (for CR with faceted search), active filters of IR/IG

6. Performance Neutral: The solution must not degrade APEX performance (e.g. must not execute source queries more than once)

One Simple Implementation Proposal (Part 1)

In case that the APEX activity log would not be extended, but developers are simply enabled to build their own custom log - without changes in the AppBuilder and without changes in the current APEX-datamodel (no additional APEX DB-tables nor changes to existing ones):

1. Introduce a global variable g_component_log to hold request-scoped component logging data (hidden in the body of a new or existing package e.g. WWV_FLOW_LOG, type declaration in the Package Spec).

g_component_log
   |_[..]_[..]              [vertical slice: future operations that are not local - not part of the initial implementation]
   |_local_dml              [vertical slice: future local DML - not part of the initial implementation (e.g. saving report configuration as ALTERNATIVE/PRIVATE report)]
   |_local_queries          [array of records – active scope for initial implementation]
       |_component_type
       |_component_static_id
       |_component_region_id
       |_component_report_id
       |_input_action
       |_input_data_access
       |_input_first_row
       |_input_max_rows
       |_input_primary_keys
       |_input_column_id
       |_input_file_id
       |_input_filter_list_of_values
       |_input_filter_faceted_search
       |_input_filter_interactive_report
       |_input_filter_interactive_grid
       |_input_filter_<other> [vertical slice: placeholder for future filter of other component type e.g. Calendar - not part of the initial implementation]
       |_output_row_count
       |_output_total_row_count
       |_output_has_more_rows
       |_output_file_id
       |_output_file_name

2. Populate this variable during request processing (for both SHOW and AJAX calls), as each relevant component executes its query.

3. Expose a new API function, e.g., APEX_UTIL.GET_COMPONENT_LOG, that simply returns this global variable.

4. Developers can then call this function within the "Database Session Cleanup" section, format the data as needed, and persist it to their custom logging table.

One Simple Implementation Proposal (Part 2)

Thoughts on Individual Fields

• component_type: Always populated with one of NATIVE_SELECT_LIST, NATIVE_POPUP_LOV, NATIVE_SQL_REPORT, NATIVE_IR, NATIVE_IG or NATIVE_JSTREE. Future extensions may include NATIVE_CARDS, NATIVE_CSS_CALENDAR, NATIVE_JET_CHART, etc. - but only if explicitly requested by other users here. If a component type is not on this list, no record should be added to the local_queries array for this component.
• component_static_id: Always populated. If the developer has not explicitly assigned a static ID to the component, assume the developer is not interested in logging this component, and thus no record should be created for it.
• component_region_id: Populated for all region-based components (CR, IR, IG, TREE).
• component_report_id: Populated for components that support named or saved reports (IR, IG).
• input_action: The native action performed by the component (CR with Faceted Search/Smart Filters: reset, sort, fetch_counts, …; IR: page, sort, col_filter, filter_delete, quick_filter, set_columns, pull_toolbar, save_break, group_by_remove, filter_toggle, sort_widget, narrow, download, …; IG: fetchData, getFilterValues, download.
• input_data_access: Indicates the nature of the data access, e.g. FETCH_DATA, FETCH_DATA_WITH_PK, FETCH_COLUMN, DOWNLOAD_PREPARE_FILE, DOWNLOAD_GET_FILE
• input_first_row & input_max_rows: Populated only when relevant to the component (e.g. pagination settings). Otherwise left empty.
• input_primary_keys: Populated only when the action includes primary keys (FETCH_DATA_WITH_PK). Contains the exact key values (single or composite) as submitted in the request payload. Empty otherwise.
• input_column_id: Populated only for FETCH_COLUMN actions.
• input_file_id: Populated only for DOWNLOAD_GET_FILE actions.
• input_filter_list_of_values: Contains the search_text entered by the user in a LOV.
• input_filter_faceted_search: An array of faceted search parameters (as found in the itemsToSubmit section of the APEX ajax payload). Empty if there is no faceted search defined with the component, e.g. classic report without faceted search.
• input_filter_interactive_report: An array of records matching the structure of APEX_APPLICATION_PAGE_IR_COND (literally %ROWTYPE of it) and populated with the filter data of the current component_report_id, as captured by APEX and used for query-construction - not via a subsequent SELECT, but exactly the very same filter data. This ensures accuracy, as post-execution queries could reflect changes made during long-running requests.
• input_filter_interactive_grid: Similarly, an array of records of APEX_APPL_PAGE_IG_RPT_FILTERS populated with the filter data of the current component_report_id, captured at the moment the query is built in WWV_FLOW_INTERACTIVE_GRID.
• output_row_count: Always populated (except for DOWNLOAD_GET_FILE), with a value 0 or greater than 0.
• output_total_row_count: Populated only when the component supports, is configured with activated "Total Row Count" option by developers and has computed a total row count. Otherwise empty.
• output_has_more_rows: If apropriate for component and configuration, otherwise empty.
• output_file_id: Populated with the internal file ID when the action is DOWNLOAD_PREPARE_FILE. Empty otherwise.
• output_file_name: Contains the final, resolved filename as it will appear in the browser's "Save As" dialog - after any developer-defined substitutions (e.g. &<item>.) have been applied. Populated only for action DOWNLOAD_GET_FILE.

Alternative Solutions

The solution outlined above is intended purely as an illustration. The APEX team may choose a completely different internal implementation - provided that the end result empowers developers to easily fulfill the described auditing use case. For instance, an entirely alternative approach might involve exposing the full HTTP request and response to developers, allowing them to parse this data at their own risk (bearing in mind that the HTTP structure could change with each APEX release). However, even in such a scenario, developers would still lack access to the exact IR/IG filters as they existed immediately prior to query execution - the very filters that APEX uses internally to construct the query. Another viable direction could be to introduce a declarative mechanism, enabling developers to specify which components they wish to log in detail, along with the ability to define a custom callback directly within the component’s configuration settings. Or, similarly to the "activity log" provide a "component log" stored in an APEX table, analogous to the activity log.

Community Input

If you as APEX developer require this logging capability for components beyond SL, LOV, CR, IR, IG and TREE, I warmly invite you to share your specific needs here: Which components do you use and wish to log in detail? What additional metadata would you need to log (e.g., which columns were displayed or included in a download or perhaps the actual query result with all result set data - I do not need such things)? Your feedback will help the APEX team prioritize the initial implementation and the future extensions. Without concrete use cases, there is no need for the APEX team to support all possible components and scenarios if nobody needs them! Similarly, if you have alternative implementation ideas, please share them.

This feature request perfectly matches our current security and compliance requirement.

In our environment, all APEX applications connect to the APEX database, but the actual business data resides in a separate Oracle database (SALUDSOFT).

All data access is performed through database links (DBLINKs) from the APEX database to the remote one.

Because of this architecture, database-level auditing only shows the DBLINK schema user (e.g., APEX_APP), and we lose visibility of the real APEX application user (APP_USER) and the context (application, page, or component) that originated the query.

For compliance and traceability, we need to capture — in a unified audit trail — both layers of information:

• The APEX context (APP_USER, CLIENT_IDENTIFIER, APPLICATION_ID, PAGE_ID)
• The actual SQL statement executed (SELECT, INSERT, UPDATE, DELETE)
• Optionally, the DBLINK or remote data source name used

Currently, neither Unified Auditing nor APEX Debug (even at level 9) provides this information.

Having a built-in mechanism to extend the APEX activity log — or a new component-level logging API — that exposes SQL statements along with their APEX session context would be extremely valuable for regulated and multi-database environments like ours.

This enhancement would finally allow complete end-to-end traceability of who accessed which data, from which APEX page and component, even when the query runs through a DBLINK to another database.

I fully support this feature and would be happy to provide real use-case details or testing feedback if needed.

— Hernan Fernandez

So, this is about auditing. Did you look into what the Oracle Database provides with regards to Auditing functionality. I'm specifically thinking about Fine Grained Auditing.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-audit-policies.html#GUID-88636FFA-3AE7-4794-8834-FF6803D42F7C

FGA indeed allows you to not only put an Audit policy onto a table, you can also define it to be specific to rows, columns or conditions.  In such conditions, you can access the MODULE and ACTION values from the V$SESSION table, which APEX populates with App and App Page information - so you can perfectly set up an Audit Policy on a given table, which fires for specific APEX apps, users or other conditions which must be met.

We typically see auditing as a functionality of the database, independent from any application. Audit Policies in the database will not only apply to APEX apps, but also to other apps potentially querying the same tables.

Are there specific requirements which cannot be met by applying auditing in the database?

Hello Carsten,

thank you so much for your reply!

Yes, exactly - this is about auditing. And yes, there are specific requirements that simply can’t be met by relying on database-level auditing. These are laid out clearly in the Use Case right at the start of this idea.

For instance, there’s no way to log ROW_COUNT and TOTAL_ROW_COUNT - as described in the Use Case - using FGA or other general-purpose database logging tools. And it’s not just about row counts: the database, by itself, just doesn’t have enough context to capture certain things without the application’s help. After all, the application is the one using the database - not the other way around - so it naturally “knows” more about what’s actually happening.

Let me give you a quick example: the ACTION mentioned in the Use Case is something database auditing can’t distinguish. To the database, it’s all the same - whether a query runs to show a list on-screen or to generate a downloadable file. But for my logging purposes, that difference is crucial: Was a list shown or refreshed, or was a file actually downloaded? And on top of that, I’m accessing the exact same datasource multiple times from different places in my own PL/SQL code - none of which I want showing up in the audit log.

So, to sum it up: database auditing just can’t fulfill these requirements. That’s why I believe the best path forward would be to make APEX strong and powerful enough that developers can easily log exactly what they need - right from inside the application itself.

Best regards, Nikola

Hmmm … these are very specialized logging and auditing requirements. The issue I see in the APEX engine is that the required infrastructure alone will require overhead and introduce a performance penalty to all customers, even those not wanting to use such functionality - I actually don't see such functionality as part of the APEX engine.

I'd indeed rather see it as a combination of code in your very APEX application, and database audit functionality. As I stated before, Fine Grained Auditing in the database can be configured to only audit SQL statements if certain conditons are met - a very often used technique is to look into the ACTION, MODULE and CLIENT_INFO properties of a database session. APEX always sets these, so a database session “knows” which application, page or APEX user it is working for. Audit Policies in the database can thus be configured to fire only for APEX or specific APEX pages. In your APEX app, you can also use DBMS_APPLICATION_INFO to set these session properties yourself, so that you can even more control the database auditing.

The difference between a list just being refreshed or a file downloaded is also something you can get when combining database auditing with custom code in your APEX app. 

As said - I struggle with imagining that as an APEX engine feature - auditing needs are very different across customers - the configuration of such an “APEX extended audit" woule be complex, we would at least partly rebuild what's already in the database - and for many customers it would be non-sufficient as audit requirements are often beyond the APEX app: customers want to audit all database activity.

Thank you very much for your prompt response!

I completely agree that database auditing (like FGA) is extremely powerful - and we can use it to monitor interactions between the APEX application backend and the database. However, the idea I submitted is actually focused on something different: application-level auditing of the traffic between the browser and the APEX backend.

In other words: which UI component (e.g., an Interactive Grid, a List of Values, etc.) sent what data (just row counts would be sufficient - no need for the actual payload) to the browser, and under which user-defined search or filter criteria.  

For example: “Interactive Grid 2 on page 10 with search ‘XYZ’ returned 42 rows to the browser.”
This is purely about the frontend/backend boundary, not the backend/database one.

You wrote that these are “very specialized logging and auditing requirements” - and I wanted to gently point out that this is actually a natural extension of what APEX already does with its built-in Activity Log. The Activity Log already captures browser–backend interactions (page views, ajax calls, etc.), and even includes IR-specific columns like INTERACTIVE_REPORT_ID, IR_SEARCH, and IR_SAVED_REPORT_ID. That shows APEX actually recognizes the value of logging UI-layer activity—not just database activity.

Many developers (myself included) have been hoping for years that this concept would eventually be expanded beyond Interactive Reports to cover many other component types. So this idea isn’t about adding something entirely new - it’s about completing a story that APEX began a long time ago.

Regarding performance: the overhead would be minimal - essentially just accumulating a few KB of in-memory metadata per component (e.g., component type/id, row count, applied filters), with no additional I/O or waits. That shouldn’t pose a performance issue. You could even introduce a new “Component Logging” toggle (analogous to the existing “Logging” switch, placed right next to it), disabled by default. For users who don’t need it, they simply wouldn’t enable the switch - and the additional cost would be not just nearly zero, but truly zero for those users.

That said - if this direction is definitively not on the roadmap, I fully respect that decision.
Still, it was well worth asking - this has been a very productive conversation. After all these years of waiting, this finally brings clarity. At least we know it now! :)

But I’d like to ask: what options do we have to achieve this ourselves?

One possibility we’ve explored is using internal APEX globals like WWV_FLOW.g_request (which is documented) and WWV_FLOW.g_json (which is undocumented and marked in the source code as “Will be removed in 5.2”). While g_request works for simpler or older components, only g_json contains the details needed to identify components like IGs and LOVs - which is essential for our logging.

It’s crystal clear that relying on undocumented internals isn’t ideal. But if there’s no supported alternative, could you at least offer reassurance that G_JSON (or an equivalent mechanism) won’t be removed or hidden without a viable migration path? We’re happy to adapt our code when its structure changes between APEX versions - no problem. What matters is that some way to identify the calling component remains accessible, even if only internally and undocumented.

I know - it’s a bad idea… but what alternatives do we have?

Thank you again for your time and transparency. Knowing your position helps us plan accordingly.