APEX 19.2
Attempting to change page item session state in PL/SQL Function Body returning SQL Query region source code causes the Page Designer to throw a load of errors when the page is saved, with no obvious way to commit the required code.
This is easily reproduced in 19.2 with a simple function body block:
:p4_item := 'foo';
return 'select * from emp';
On clicking Save, errors are displayed:
1 error has occurred
- Error: SyntaxError: Unexpected non-whitespace character after JSON at position 178 (line 5 column 1)
ORA-20987: APEX - JSON.WRITER.NOT_OPEN - Contact your application administrator. Details about this incident are available via debug id "342563".
The debug trace contains:
0.000068 CGI: PATH_INFO = /wwv_flow.ajax
0.000055 POST http://localhost:8080/ords
REMOTE_ADDR=10.0.2.2
REMOTE_USER=APEX_PUBLIC_USER
APEX_LISTENER_VERSION=19.4.0.r3521226
REFERER=http://localhost:8080/ords/f?p=4000:4500:6406512163781::NO:RP::&success_msg=UGFnZSBjb3BpZWQgc3VjY2Vzc2Z1bGx5Lg~~%2FUWcP0kH92rLobVMN5UcD0M4hmz62Rz22E68giPTKweEnC97yeNI6O_EtMl6MZQ088C7KqJJqL1qW7FM_jjgwdg
HTTP_COOKIE=jenkins-timestamper-offset=0; remember-me=YWRtaW46MTcwOTU4NDM4OTk4NDo3Y2I2NDYwZmY0ZDkzZmZlNGQ5MjAzOWEzOWQ5MThhNThiNGQ2ZDcxZTI1MTQ4YWMzZWU5NjE4Zjc2OWU1Y2Ew; JSESSIONID.649e5622=node0iz2phkdhe7wlx68fzekay8530.node0; screenResolution=1920x1080; JSESSIONID.1d35d5c5=node01tm661lg9ngf017bypawfdq3iz0.node0
0.00001 SID=50
USER=APEX_PUBLIC_USER
INSTANCE=1
0.000027 Error: ERR-1002 Unable to find item ID for item "P4_ITEM" in application "4000".
0.000017 - additional_info: Unexpected error, unable to find item name at application or page level.
0.000029 - display_location: ON_ERROR_PAGE
0.000017 - is_internal_error: true
0.000016 - is_common_runtime_error: false
0.000029 - apex_error_code: WWV_FLOW.FIND_ITEM_ID_ERR
0.000021 - error_backtrace: ----- PL/SQL Call Stack -----
object line object
handle number name
8cdc7970 1033 package body APEX_190200.WWV_FLOW_ERROR.INTERNAL_GET_ERROR
8cdc7970 1101 package body APEX_190200.WWV_FLOW_ERROR.INTERNAL_ADD_ERROR
8cdc7970 1493 package body APEX_190200.WWV_FLOW_ERROR.RAISE_INTERNAL_ERROR
80d67e88 3271 package body APEX_190200.WWV_FLOW_META_DATA.FIND_ITEM_BY_NAME
9170d318 1847 package body APEX_190200.WWV_FLOW_SESSION_STATE.SAVE_BY_NAME
9862baa8 2167 package body APEX_190200.WWV_FLOW_DYNAMIC_EXEC.SET_SESSION_STATE_AFTER_EXEC
9862baa8 2553 package body APEX_190200.WWV_FLOW_DYNAMIC_EXEC.RUN_BLOCK5
9862baa8 1355 package body APEX_190200.WWV_FLOW_DYNAMIC_EXEC.GET_PLSQL_FUNC_RESULT_VARCHAR2
8847d538 1481 package body APEX_190200.WWV_FLOW_EXEC.GENERATE_SQL_QUERY
96171ba8 414 package body APEX_190200.WWV_FLOW_EXEC_DEV.CHECK_AND_DESCRIBE
985f74f0 3476 package body APEX_190200.WWV_FLOW_PROPERTY_DEV.EMIT_COMPONENT
9~
0.003049 - component: APEX_APPLICATION_PAGE_PROCESS writePageData (3791708414403)
0.000119 Error: JSON.WRITER.NOT_OPEN
0.000029 - additional_info: Contact your application administrator.
Details about this incident are available via debug id "342561".
0.000019 - display_location: ON_ERROR_PAGE
0.000017 - is_internal_error: true
0.000016 - is_common_runtime_error: false
0.00003 - apex_error_code: JSON.WRITER.NOT_OPEN
0.00002 - error_backtrace: ----- PL/SQL Call Stack -----
object line object
handle number name
8cdc7970 1033 package body APEX_190200.WWV_FLOW_ERROR.INTERNAL_GET_ERROR
8cdc7970 1101 package body APEX_190200.WWV_FLOW_ERROR.INTERNAL_ADD_ERROR
8cdc7970 1493 package body APEX_190200.WWV_FLOW_ERROR.RAISE_INTERNAL_ERROR
8c5e6f18 3536 package body APEX_190200.WWV_FLOW_JSON.WRITE
985f74f0 2407 package body APEX_190200.WWV_FLOW_PROPERTY_DEV.EMIT_T_DESCRIBE_RESULT
985f74f0 3491 package body APEX_190200.WWV_FLOW_PROPERTY_DEV.EMIT_COMPONENT
985f74f0 6173 package body APEX_190200.WWV_FLOW_PROPERTY_DEV.WRITE_PAGE_DATA.PROCESS
985f74f0 6235 package body APEX_190200.WWV_FLOW_PROPERTY_DEV.WRITE_PAGE_DATA
8c6b16f8 2 anonymous block
84f1c128 2120 package body SYS.DBMS_SYS_SQL.EXECUTE
823ea6b8 601 package body SYS.WWV_DBMS_SQL_APEX_190200.EXECUTE
9862baa8 2550 package body APEX_190200.WWV_FLOW_DYNAMIC_EXEC.RUN_BLOCK5
9862baa8 1499 packa~
0.000535 - component: APEX_APPLICATION_PAGE_PROCESS writePageData (3791708414403)
0.000213 Error in error handler: ORA-20876: Stop APEX Engine
ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 1524
ORA-06512: at "APEX_190200.WWV_FLOW", line 2474
ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 1518
Backtrace: ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 1524
ORA-06512: at "APEX_190200.WWV_FLOW", line 2474
ORA-06512: at "APEX_190200.WWV_FLOW_DYNAMIC_EXEC", line 1518
ORA-06512: at "APEX_190200.WWV_FLOW_ERROR", line 858
The root cause is that during validation of the region source, APEX is executing the function block and treating the referenced page item as a component of the internal Application Builder application that is currently running.
This does not occur in APEX 23.2 on apex.oracle.com.
This was reported years ago in earlier versions, but the workaround suggested then is no longer available as the undocumented apex_application.get_current_flow_sgid function was removed in APEX 4.2.
In our actual use case is in an area that requires high performance, and I don't want to incur an unnecessary DA request to set the item before the region is refreshed.
Two questions:
- Any ideas for 19.2-compatible solutions/workarounds?
- In which release was this problem finally fixed?