Skip to Main Content

APEX

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

APEX 19.2: PL/SQL Function Body code causes ERR-1002 / ORA-20987: APEX - JSON.WRITER.NOT_OPEN in Page Designer

fac586Feb 22 2024

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:

  1. Any ideas for 19.2-compatible solutions/workarounds?
  2. In which release was this problem finally fixed?
Comments
Post Details
Added on Feb 22 2024
1 comment
73 views