Skip to Main Content
Feature Request FR-4920
Product Area Application Builder
Status OPEN

1 Voters

Declarative support for “SQL Macro (Table)” as Region Source Type

rode.mb Public · Jun 11 2026

Idea Summary
Add declarative support for “SQL Macro (Table)” as a region source type in Oracle APEX.

Oracle Database supports table SQL macros, which are a powerful way to encapsulate reusable, parameterized SQL logic in the database. In APEX, regions can currently be based on SQL queries, PL/SQL functions returning SQL queries, REST sources, and other source types. However, there is no dedicated declarative source type for table SQL macros.

The idea is to allow developers to select SQL Macro (Table) directly as a region source type. After selecting this source type, the developer should be able to specify a table SQL macro function and provide values for its parameters, including references to page items and application items.

This would allow APEX regions such as Interactive Reports, Interactive Grids, Classic Reports, Cards, and similar data-driven components to use table SQL macros in a more declarative and structured way.

Use Case
Table SQL macros are useful for centralizing reusable SQL logic in the database. This idea would be especially helpful in applications where the same complex query logic is used across multiple APEX pages or regions.

Typical use cases include:

  • reusable filter logic
  • tenant or workspace-specific data access logic
  • security and authorization-related query logic
  • reusable joins across multiple tables
  • complex reporting queries
  • parameterized data sets used by multiple regions
  • reducing duplicated SQL code in APEX pages

For example, a table SQL macro could encapsulate customer-specific filtering, authorization checks, and recurring joins. Multiple APEX regions could then use the same macro with different page item values as parameters, instead of duplicating the full SQL query in each region.

This would improve maintainability, reduce duplication, and make it easier to use modern Oracle Database SQL features in APEX applications.

Preferred Solution
Add SQL Macro (Table) as an additional option under Region → Source → Type.

When this source type is selected, APEX could allow the developer to either enter a table SQL macro expression manually or select a table SQL macro function declaratively.

APEX should also provide a declarative way to map macro parameters to values, similar to how parameters are mapped for REST Sources, List of Values, or component attributes.

For example, after selecting the table SQL macro, APEX could display the available parameters and allow the developer to assign values such as:

p_customer_id  →  &P1_CUSTOMER_ID.
p_status       →  &P1_STATUS.
p_date_from    →  &P1_DATE_FROM.
p_date_to      →  &P1_DATE_TO.

or bind-style mappings such as:

p_customer_id  →  :P1_CUSTOMER_ID
p_status       →  :P1_STATUS
p_date_from    →  :P1_DATE_FROM
p_date_to      →  :P1_DATE_TO

APEX could then internally generate the corresponding table expression, for example:

select *
from my_table_macro(
    p_customer_id => :P1_CUSTOMER_ID,
    p_status      => :P1_STATUS,
    p_date_from   => :P1_DATE_FROM,
    p_date_to     => :P1_DATE_TO
)

APEX should treat the result of the table SQL macro like a regular table expression and derive the available columns for the region in the same way it does for a normal SQL query.

This would make table SQL macros easier to use in APEX while keeping the region definition declarative, maintainable, and consistent with other APEX source types.

This idea is open.