I've encountered a critical issue involving the JSON_TABLE function within Oracle APEX 24.2, specifically when editing an existing Automation via Shared Components → Automation in the internal APEX Application 4000 (page 7021, item P7021_PK_COLUMN_NAME).
When attempting to edit an existing Automation, the page throws the following error:
ORA-03113: database connection closed by peer
The root cause is identified in the SQL used for the List of Values (LOV) of the item P7021_PK_COLUMN_NAME:
SELECT d, r
FROM JSON_TABLE(
wwv_flow_automation_dev.p7021_query_columns_as_clob,
'$[*]'
COLUMNS(
d VARCHAR2(255) PATH '$.d',
r VARCHAR2(255) PATH '$.r'
)
);
This query fails to execute correctly on Oracle Database 19.3, despite a direct query returning results successfully:
SELECT apex_240200.wwv_flow_automation_dev.p7021_query_columns_as_clob FROM dual;
As a workaround, I've implemented the following update:
UPDATE apex_240200.wwv_flow_step_items
SET LOV = '
WITH json_source AS (
SELECT wwv_flow_automation_dev.p7021_query_columns_as_clob AS json_data FROM dual
)
SELECT d, r
FROM json_source,
JSON_TABLE(
json_data,
'$[*]'
COLUMNS(
d VARCHAR2(255) PATH '$.d',
r VARCHAR2(255) PATH '$.r'
)
);
'
WHERE flow_id = 4000 and name = 'P7021_PK_COLUMN_NAME';
Encapsulating the function call in a Common Table Expression (CTE) effectively resolves the issue.
I'd appreciate feedback, particularly if others have encountered similar issues or if an official patch is available from Oracle.
It would also be highly beneficial if Oracle could:
- Resolve this and similar compatibility issues explicitly,
- Or at minimum, clearly document the minimum supported database version (including exact patch level) required for each APEX release, especially when critical features like
JSON_TABLE are involved.
This clarity would help developers avoid unpredictable behavior and streamline deployment planning.