A-1680 Always define an ordering for reporting regions.
Reason
Without an ordering, the order of rows from a report is undefined. It can look right in testing and change after a statistics refresh or plan change, and pagination may repeat or skip rows. Define the order where the region type expects it:
- Classic reports and Cards: add an
ORDER BYto the query, or set the region's Order By attribute. - Interactive Reports: add a sort to the primary default report. An
ORDER BYin the query works as well. - Interactive Grids: add a sort to the primary report. An
ORDER BYin the grid query is not allowed.
Example
Non-Compliant Example
page 10 (
// ...
region customers (
name: Customers
type: classicReport
source {
location: localDatabase
type: sqlQuery
sqlQuery:
```sql
select name, city from customers
```
}
// ...
)
region orders (
name: Orders
type: interactiveGrid
// ...
savedReport primary (
visibility: primary
view {
default: grid
}
// no sort
)
// ...
)
region invoices (
name: Invoices
type: interactiveReport
source {
location: localDatabase
type: sqlQuery
sqlQuery: select id, amount from invoices
}
// ...
savedReport primary (
visibility: primaryDefault
// no sort
)
// ...
)
) | Line | Column | Message |
|---|---|---|
| 3 | 12 | Classic Report has no defined order. |
| 17 | 12 | Interactive Grid has no defined order. |
| 31 | 12 | Interactive Report has no defined order. |
Compliant Solution - ★★★★★
page 10 (
// ...
region customers (
name: Customers
type: classicReport
source {
location: localDatabase
type: sqlQuery
sqlQuery:
```sql
select name, city from customers order by name
```
}
// ...
)
region orders (
name: Orders
type: interactiveGrid
// ...
savedReport primary (
visibility: primary
view {
default: grid
}
sort (
column: @ORDER_ID
sort {
nulls: last
sequence: 1
}
)
)
// ...
)
region invoices (
name: Invoices
type: interactiveReport
source {
location: localDatabase
type: sqlQuery
sqlQuery: select id, amount from invoices
}
// ...
savedReport primary (
visibility: primaryDefault
sort (
column: ID
sort {
direction: desc
sequence: 1
}
)
)
// ...
)
) Tests
Test SQL query
select aa.workspace || '.' || aa.alias || '.' || r.page_id || '.' || coalesce(r.static_id, to_char(r.region_id)) as identifier,
case r.source_type_plugin_name
when 'NATIVE_SQL_REPORT' then 'Classic report '
when 'NATIVE_IR' then 'Interactive report '
when 'NATIVE_IG' then 'Interactive grid '
when 'NATIVE_CARDS' then 'Cards region '
end || r.region_name || ' on page ' || r.page_id || ' of app ' || aa.alias || ' in '
|| aa.workspace || ' has no defined order.' as message
from apex_application_page_regions r
join apex_applications aa
on aa.application_id = r.application_id
and aa.workspace = r.workspace
where aa.workspace in (#ApexWorkspaces#)
and r.source_type_plugin_name in ('NATIVE_SQL_REPORT', 'NATIVE_IR', 'NATIVE_IG', 'NATIVE_CARDS')
and r.location_code = 'LOCAL'
and nvl(r.query_type_code, 'x') != 'FUNC_BODY_RETURNING_SQL'
and instr(nvl(r.component_template_options, ' '), 't-BadgeList') = 0
-- classic report: ORDER BY in the query, the region's order by or a default column sort
and not (r.source_type_plugin_name = 'NATIVE_SQL_REPORT'
and (regexp_like(nvl(r.region_source, ' '), '(^|[^[:alnum:]_])order[[:space:]]+by([^[:alnum:]_]|$)', 'i')
or r.order_by_clause is not null
or r.order_by_item is not null
or exists (select 1
from apex_application_page_rpt_cols c
where c.region_id = r.region_id
and c.default_sort_sequence != 0)))
-- cards: ORDER BY in the query or the region's order by
and not (r.source_type_plugin_name = 'NATIVE_CARDS'
and (regexp_like(nvl(r.region_source, ' '), '(^|[^[:alnum:]_])order[[:space:]]+by([^[:alnum:]_]|$)', 'i')
or r.order_by_clause is not null
or r.order_by_item is not null))
-- interactive report: ORDER BY in the query, the Order By Clause of a table source or a sort in the primary default report
and not (r.source_type_plugin_name = 'NATIVE_IR'
and (regexp_like(nvl(r.region_source, ' '), '(^|[^[:alnum:]_])order[[:space:]]+by([^[:alnum:]_]|$)', 'i')
or r.order_by_clause is not null
or exists (select 1
from apex_application_page_ir_rpt ir
where ir.region_id = r.region_id
and ir.report_type = 'PRIMARY_DEFAULT'
and ir.sort_column_1 is not null)))
-- interactive grid: a sort in the primary saved report or the Order By Clause of a table source
-- (an ORDER BY in a SQL query is not allowed in a grid, so the query text is not checked)
and not (r.source_type_plugin_name = 'NATIVE_IG'
and (r.order_by_clause is not null
or exists (select 1
from apex_appl_page_ig_rpts g
join apex_appl_page_ig_rpt_columns gc
on gc.report_id = g.report_id
where g.region_id = r.region_id
and g.type = 'PRIMARY'
and gc.sort_order is not null)))Test results
| Identifier | Message | Migration |
|---|---|---|
| WORKSPACE.CUSTOMERS.10.108087001605795312 | Classic report Customers on page 10 of app CUSTOMERS in WORKSPACE has no defined order. | - |
| WORKSPACE.CUSTOMERS.10.108087001605795313 | Interactive grid Orders on page 10 of app CUSTOMERS in WORKSPACE has no defined order. | - |
| WORKSPACE.CUSTOMERS.10.108087001605795314 | Interactive report Invoices on page 10 of app CUSTOMERS in WORKSPACE has no defined order. | - |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| ApexWorkspaces | Comma-separated List of APEX workspaces owning APEX applications to check. | dblinter, dbl_gui |
References
- same as UC-1680