Forum Stats

  • 3,875,222 Users
  • 2,266,892 Discussions
  • 7,912,116 Comments

Discussions

APEX 22.1 - Application Export fails

Niall
Niall Member Posts: 33 Blue Ribbon
edited Aug 16, 2022 10:47AM in APEX Discussions

Hello,

Since our move to APEX 22.1 on ADB, we've been experiencing an error with our nightly exports of our applications using apex_export.get_application.

It appears to be one (very large) application that is provoking this error. Other applications export correctly. This behaviour started about 10 days ago.

Has anyone else experienced a similar issue? Is it a bug? Is there a workaround?

This is 100% reproducible on our DB.

Code:

v_files    apex_t_export_files;

...

v_files := apex_export.get_application (p_application_id => rec_apps.application_id);


Error message stack:

ORA-08177: can't serialize access for this transaction

ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_API", line 143

ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 10218

ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 7395

ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 7395

ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 10035

ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_INT", line 1234

ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_API", line 81

Best Answer

  • Niall
    Niall Member Posts: 33 Blue Ribbon

    Hi Patrick,

    Many thanks for chiming in here.

    The "apex export nnn" command in SQLcl appears to work when I set NLS_LANG as an environment variable (i.e. export NLS_LANG='AMERICAN_AMERICA'). I will try in PL/SQL also.

    Some details here:

    =================================================================

    Query output showing Swiss nls numeric characters 99'999.99 and NLS_LANG environment variable not set

    =================================================================

    SQL> select '*' || parameter || '*', value from sys.nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';

        '*'||PARAMETER||'*'  VALUE 

    ___________________________ ________ 

    *NLS_NUMERIC_CHARACTERS*  .'    


    ===============================================

    NLS Parameters with environment variable NLS_LANG set to 'AMERICAN_AMERICA'

    ===============================================

    nls_calendar                string                                                     

    nls_comp                  string   BINARY                                               

    nls_currency                string                                                     

    nls_date_format               string                                                     

    nls_date_language              string                                                     

    nls_dual_currency              string                                                     

    nls_iso_currency              string                                                     

    nls_language                string   AMERICAN                                              

    nls_length_semantics            string   BYTE                                                

    nls_nchar_conv_excp             string   FALSE                                                

    nls_numeric_characters           string                                                     

    nls_sort                  string                                                     

    nls_territory                string   AMERICA                                               

    nls_time_format               string                                                     

    nls_time_tz_format             string                                                     

    nls_timestamp_format            string                                                     

    nls_timestamp_tz_format           string                                                     



    ===============================================

    NLS Parameters with environment variable NLS_LANG not set:

    ===============================================

    nls_calendar                string                                                     

    nls_comp                  string   BINARY                                               

    nls_currency                string                                                     

    nls_date_format               string                                                     

    nls_date_language              string                                                     

    nls_dual_currency              string                                                     

    nls_iso_currency              string                                                     

    nls_language                string   AMERICAN                                              

    nls_length_semantics            string   BYTE                                                

    nls_nchar_conv_excp             string   FALSE                                                

    nls_numeric_characters           string                                                     

    nls_sort                  string                                                     

    nls_territory                string   SWITZERLAND                                             

    nls_time_format               string                                                     

    nls_time_tz_format             string                                                     

    nls_timestamp_format            string                                                     

    nls_timestamp_tz_format           string                                                     

«13

Answers

  • Niall
    Niall Member Posts: 33 Blue Ribbon
    edited Aug 15, 2022 10:07AM

    Update - some additional info:

    • We have one ADB that is still on 21.2 and the application export still works there. So this problem appears to be related to the 22.1 update.
    • Interactive export via APEX Application Builder appears to work.
    • I have found other applications that also fail to export.
    • I can reproduce this in sqlcl, see following:

    SQL> apex export 300

    Exporting Application 300

    APEX: Unable to process request. Verify specified options:

    ORA-06502: PL/SQL: numeric or value error

    ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_API", line 143

    ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 10218

    ORA-06512: at "SYS.DBMS_ASSERT", line 493

    ORA-06512: at "SYS.DBMS_ASSERT", line 583

    ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 10194

    ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_INT", line 1234

    ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_API", line 81

    ORA-06512: at line 2

    SQL> 

  • Niall
    Niall Member Posts: 33 Blue Ribbon

    If anyone from the APEX team is reading this, I've opened an SR:

    SR 3-30377000141 : APEX Application Export fails since move to 22.1

  • RobertMe
    RobertMe Member Posts: 1 Red Ribbon

    Not sure if it helps in your case but for me it helped to use a

    commit;

    right before calling apex_export.get_application.

    Niall
  • Niall
    Niall Member Posts: 33 Blue Ribbon


    Wow! Many Thanks for this tip, @RobertMe.

    This worked for PL/SQL, I added a commit immediately before the apex_export.get_application and now it works! :)


    Exporting with sqlcl still fails, though :(

    % sql /nolog

    SQLcl: Release 22.1 Production on Tue Aug 16 08:18:31 2022

    Copyright (c) 1982, 2022, Oracle. All rights reserved.


    SQL> set cloudconfig /Users/niall/xxxxxxxxx

    SQL> connect xxx/yyy

    Connected.

    SQL> commit;

    Commit complete.

    SQL> apex export 300

    Exporting Application 300

    APEX: Unable to process request. Verify specified options:

    ORA-06502: PL/SQL: numeric or value error

    ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_API", line 143

    ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 10218

    ORA-06512: at "SYS.DBMS_ASSERT", line 493

    ORA-06512: at "SYS.DBMS_ASSERT", line 583

    ORA-06512: at "APEX_220100.WWV_FLOW_GEN_API2", line 10194

    ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_INT", line 1234

    ORA-06512: at "APEX_220100.WWV_FLOW_EXPORT_API", line 81

    ORA-06512: at line 2

    SQL> 

  • Jande87
    Jande87 Member Posts: 7 Red Ribbon

    Hello Nail,

    Please show me your code that it works for you. I have added commit before get_application but still got an error.

    Tnx

    Niall
  • Niall
    Niall Member Posts: 33 Blue Ribbon

    It seems to have a dependency on the session/transaction state...

    This fails if I run it once, but works if I run it a second time.

    My earlier declaration of success was probably because I had failed runs just beforehand :(



  • Niall
    Niall Member Posts: 33 Blue Ribbon

    For SQLCL, it seems to need to fail twice before it works.

    Note that the failure messages are different for the 1st and 2nd times


  • Jande87
    Jande87 Member Posts: 7 Red Ribbon

    No matter how many times it is called, it always reports an error.

  • Niall
    Niall Member Posts: 33 Blue Ribbon

    I summarised the current situation toiday in the SR that I opened with Oracle (SR 3-30377000141).

    I'll copy part of it here, as it may be a useful synopsis:

    === start of summary ===

    Oracle APEX has three methods for application export:

    1) via the APEX builder UI

    2) via the supplied PL/SQL API apex_export.get_application

    3) via SQLcl and the "apex export nnn" command.

    Of these 3 only 1) via APEX builder UI works.

    For us, 1) is not an acceptable workaround as we have automated processes that use methods 2) and 3) to manage our CI/CD pipeline and to backup our application environments.

    For method 2), our tests have shown (see attached screenshots and code examples), that inserting a commit (rollback works too) and re-trying a second time immediately after failure works most of the time.

    For method 3), our tests have shown when inserting a commit or rollback shown the export works after 2 failures. It is interesting to note that the error messages for the two failures are different (screenshot attached).

    Judging from the behaviour observed, the bug appears to be at the transaction level of the export. One of the APEX team members confirmed that this had changed with 22.1 to accommodate new serializable features.

    We have 3 ADBs: DEV, TEST and PROD.

    DEV and TEST are already running APEX 22.1, PROD is scheduled to be upgraded to 22.1 on September 4th at the latest. The bug is only occurring on DEV and TEST. When examining the logs of our nightly jobs, we can see that the bug started for each database the day that it was upgraded from APEX 21.2 to APEX 22.1

    === end of summary ===

  • Jande87
    Jande87 Member Posts: 7 Red Ribbon
    edited Aug 17, 2022 1:54PM

    In my case, the first doesn't work either. Looks like I have to wait for the new version.


    This has already been resolved with the new patch.

    As far as I know, it is related, the same procedures are used for UI export and automatic backup..

    Niall