Hi,
this post is a reply to https://twitter.com/cczarski/status/1553833714391580672
APEX 22.1.3 is throwing an "ORA-02047: cannot join the distributed transaction in progress" exception displaying an IR selecting from a pipelined table function which is using dbms_hs_passthrough to select from a mysql database. The PTF works without any problems in sqlplus/toad/sqldeveloper/etc, only apex throws an exception.
Debug from APEX:
Error processing request.
Contact your application administrator. Details about this incident are available via debug id "105402".
Technical Info (only visible for developers)
is_internal_error: true
apex_error_code: APEX.UNHANDLED_ERROR
ora_sqlcode: -2047
ora_sqlerrm: ORA-02047: Teilnahme an begonnener verteilter Transaktion nicht möglich ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1278 ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1318 ORA-02047: Teilnahme an begonnener verteilter Transaktion nicht möglich ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1278 ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1285 ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1337 ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1350 ORA-06512: in "APEX_220100.WWV_FLOW_DEVELOPER_TOOLBAR", Zeile 321 ORA-06512: in "APEX_220100.WWV_FLOW_PAGE", Zeile 1876470384 ORA-06512: in "APEX_220100.WWV_FLOW_PAGE", Zeile 3310
component.type: APEX_APPLICATION_PAGES
component.id: 200000000952
component.name: OTRS
error_backtrace:
ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1278
ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1318
ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1278
ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1285
ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1337
ORA-06512: in "APEX_220100.WWV_FLOW_JSON", Zeile 1350
ORA-06512: in "APEX_220100.WWV_FLOW_DEVELOPER_TOOLBAR", Zeile 321
ORA-06512: in "APEX_220100.WWV_FLOW_PAGE", Zeile 1876470384
ORA-06512: in "APEX_220100.WWV_FLOW_PAGE", Zeile 3310
ORA-06512: in "APEX_220100.WWV_FLOW", Zeile 3308
Page was run with Full Trace, i can provide the trace if needed.
I have 2 versions of the PTF: one running in an autonomous transaction and one without.
The non-AT version loops over DBMS_HS_PASSTHROUGH.FETCH_ROW and outputs the data using PIPE ROW.
The AT version selects all data in a collection, commits, loops over collection and uses PIPE ROW .
Code to close the database links in the init and cleanup PLSQL code section is present.
My observation:
running the page without dev toolbar works. (does not mean that it always works, but i never saw the exception in my tests)
running the page without dev toolbar - i.e. not in a builder session works. (like above, i don't say that it will work everytime - i just didn't see any exception)
thanks for reading :)