Forum Stats

  • 3,852,905 Users
  • 2,264,149 Discussions
  • 7,905,159 Comments

Discussions

Exception with developer toolbar and dbms_hs_passthrough

unficyp
unficyp Member Posts: 197 Bronze Badge
edited Aug 1, 2022 8:55AM in APEX Discussions

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 :)

Best Answer

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,358 Employee
    Answer ✓

    Hi,

    based on the error stack the issue happens when the APEX_JSON package does an ALTER SESSION to change NLS settings (which it does for internal JSON parsing). As the ALTER SESSION is DDL, it does an implicit commit - and this appears to impact the distributed transaction with your MySQL database. I think this is one of the limits of the Generic Connectivity feature of the Oracle Database.

    The issue also should only occur when your builder session runs in a non-english language, when the comma is used as a decimal separator (german, french etc). So one workaround would be to switch to using the application builder in english language.

    Another option would be to close the database link earlier. The session cleanup code runs after the page completely rendered (including the developer toolbar). You might change your table function to close the database link after it's done with processing; as you probably don't need it for the rest of your page anyway ...

    Does that help?

    regards

    -Carsten

    unficyp

Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,358 Employee
    Answer ✓

    Hi,

    based on the error stack the issue happens when the APEX_JSON package does an ALTER SESSION to change NLS settings (which it does for internal JSON parsing). As the ALTER SESSION is DDL, it does an implicit commit - and this appears to impact the distributed transaction with your MySQL database. I think this is one of the limits of the Generic Connectivity feature of the Oracle Database.

    The issue also should only occur when your builder session runs in a non-english language, when the comma is used as a decimal separator (german, french etc). So one workaround would be to switch to using the application builder in english language.

    Another option would be to close the database link earlier. The session cleanup code runs after the page completely rendered (including the developer toolbar). You might change your table function to close the database link after it's done with processing; as you probably don't need it for the rest of your page anyway ...

    Does that help?

    regards

    -Carsten

    unficyp
  • unficyp
    unficyp Member Posts: 197 Bronze Badge

    Thanks for your analysis - switching the application to english (or "No NLS (Application not translated)") does indeed help.

    Regarding the other option "Another option would be to close the database link earlier", i'm already doing that:

    (ok, more or less trial and error....)

    • application init code
    • in the TF before AND after calling dbms_hs_passthrough and filling the collection (sequence: close dblink, fill collection, close dblink, commit, pipe row
    • application cleanup code

    The only step that seems to help is setting the app to english/no nls


    thx&lg,

    Gerald

  • tdobe
    tdobe Member Posts: 193 Blue Ribbon
    edited Sep 17, 2022 8:00AM

    What do you mean just one step? but I can't switch the application to English! Because I have to support other languages, what then?

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,358 Employee

    Hi,

    first, this is tracked as bug 34460675; we plan to fix that for the upcoming APEX release.

    Another workaround would be to navigate to Shared Components, Application Attributes and then switch the Application Availability from Available with Developer Toolbar to just Available. Then the developer toolbar will not render - so you'll not see that error. As a developer, you'd need to have the Application Builder open in a separate window or tab.

    I hope this helps

    -Carsten

    unficyp
  • tdobe
    tdobe Member Posts: 193 Blue Ribbon

    The best news ze is that this is as a bug, not simply a non-compliance with customer expectations ;) This solution in a separate window with the developer is OK, for a while;)