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

2 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.

We reviewed this idea carefully, and while it was interesting, we concluded that it is unlikely to make its way into APEX in the foreseeable future.

Comments

Comments

  • carsten.czarski APEX Team OP 10 days ago

    In general, SQL Macros which return a changing set of result columns should be used with care in Oracle APEX. All components (except of Classic Reports with the “Generic Columns” switch enabled) require a stable set of columns.

    Do you have such SQL Macros, or do your SQL Macros always return a stable set of result columns?

    Besides that, I see that similar to how the Invoke API process type works. Just not sure on how common that really is.

  • rode.mb OP 10 days ago

    Hi Carsten,

    yes, I fully agree. SQL Macros with a changing result structure should be used very carefully in APEX.

    My use case is explicitly about Table SQL Macros with a stable set of result columns. I would not expect Interactive Reports, Interactive Grids, Cards, etc. to support dynamically changing columns. The same rule as for normal SQL queries should apply: the region source must have a stable column structure.

    Of course, this is not fundamentally different from existing region sources. A select * from some_view can also change its result columns if the view definition changes. APEX can detect the column structure at design/parse time, but it cannot permanently guarantee that an underlying database object will never change. In that sense, Table SQL Macros would have the same responsibility model as views or regular SQL queries.

    The main value for me is not dynamic columns, but reusable and parameterized table expressions. Typical examples are SQL Macros that encapsulate common joins, tenant filtering, security predicates, language handling, or other reusable query logic, while intentionally returning the same columns.

    Implementation-wise, I also see this as similar to the Invoke API process type: select the SQL Macro declaratively and map its parameters to page items, application items, static values, or expressions. APEX could then generate a normal query internally, for example:

    select *
from my_table_macro(
    p_customer_id => :P1_CUSTOMER_ID,
    p_status      => :P1_STATUS
)

    One additional use case is that the SQL generated by the macro may reference database-side session context, such as application context values or package state initialized earlier in the request. This is useful when common filtering or security logic should not be repeated or passed explicitly into every region.

    So the result shape would stay stable and predictable for APEX, while the query logic could be centralized and reused more cleanly.

    Thanks,

    Roland

  • carsten.czarski APEX Team OP 6 days ago

    Hi Roland,

    thank you. I understand the idea, and it's a good one. However, I will have to put into a “Not Now" status, as the implementation will come at quite a cost, and SQL Macros being not that common yet. So, it's not very likely that we can prioritize this idea in the near future.

    -Carsten