5 Replies Latest reply on Jul 17, 2017 7:34 PM by Martin1

    Error in Login after Migration APEX from 4.2 to 5.1.2

    Martin1

      Hi,

      i migrated APEX from 4.2 to 5.1.2 and now i get the error ORA-28817 at Login. The application has a custom authentication scheme. The other two applications in the workspace has also a custom authentication scheme but there no error occurs at login.

       

      Here the debug log in detail:

      "Elapsed","Execution","Message","Level"

      "0.00000","0.01700","Reset NLS settings","4"

      "0.01700","0.00000","alter session set  NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN' NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'","4"

      "0.01700","0.00000","...NLS: Set Decimal separator="".""","4"

      "0.01700","0.00000","...NLS: Set NLS Group separator="",""","4"

      "0.01700","0.00000","...NLS: Set g_nls_date_format=""DD-MON-RR""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_format=""DD-MON-RR HH.MI.SSXFF AM""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_tz_format=""DD-MON-RR HH.MI.SSXFF AM TZR""","4"

      "0.01700","0.00000","...Setting session time_zone to +02:00","4"

      "0.01700","0.00000","R E Q U E S T accept LOGIN","4"

      "0.01700","0.00000","Metadata: Fetch application definition and shortcuts","4"

      "0.01700","0.00000","NLS: wwv_flow.g_flow_language_derived_from=FLOW_PRIMARY_LANGUAGE: wwv_flow.g_browser_language=en-us","4"

      "0.01700","0.00000","alter session set nls_language='AMERICAN' nls_territory='AMERICA'","4"

      "0.01700","0.00000","NLS: CSV charset=WE8MSWIN1252","4"

      "0.01700","0.00000","...NLS: Set Decimal separator="".""","4"

      "0.01700","0.00000","...NLS: Set NLS Group separator="",""","4"

      "0.01700","0.00000","...NLS: Set g_nls_date_format=""DD-MON-RR""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_format=""DD-MON-RR HH.MI.SSXFF AM""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_tz_format=""DD-MON-RR HH.MI.SSXFF AM TZR""","4"

      "0.01700","0.00000","Setting NLS: NLS_DATE_FORMAT=""DD.MM.RRRR""","4"

      "0.01700","0.00000","...NLS: Set g_nls_date_format=""DD.MM.RRRR""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_format=""DD-MON-RR HH.MI.SSXFF AM""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_tz_format=""DD-MON-RR HH.MI.SSXFF AM TZR""","4"

      "0.01700","0.00000","...metadata, fetch translated app info","4"

      "0.01700","0.00000","Authentication check: Table Based Authentication (NATIVE_CUSTOM)","4"

      "0.01700","0.00000","... sentry+verification success","4"

      "0.01700","0.00000","...Session ID 6585613103527 can be used","4"

      "0.01700","0.00000","Session State: fetch from database (exact)","4"

      "0.01700","0.00000","...Setting session time_zone to +02:00","4"

      "0.01700","0.00000","Setting NLS: NLS_DATE_FORMAT=""DD.MM.RRRR""","4"

      "0.01700","0.00000","...NLS: Set g_nls_date_format=""DD.MM.RRRR""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_format=""DD-MON-RR HH.MI.SSXFF AM""","4"

      "0.01700","0.00000","...NLS: Set g_nls_timestamp_tz_format=""DD-MON-RR HH.MI.SSXFF AM TZR""","4"

      "0.01700","0.00000","...Check for session expiration:","4"

      "0.01700","0.00000","...Metadata: Fetch Page, Computation, Process, and Branch","4"

      "0.01700","0.00000","...Parse JSON","4"

      "0.01700","0.00000","......Validate item page affinity","4"

      "0.01700","0.00000","Exception in ""final_exception_handler"":

      Sqlerrm: ORA-28817: PL/SQL function returned an error.

      Backtrace: ORA-06512: at ""SYS.DBMS_CRYPTO_FFI"", line 67

      ORA-06512: at ""SYS.DBMS_CRYPTO"", line 44

      ORA-06512: at ""APEX_050100.WWV_FLOW_CRYPTO"", line 198

      ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 2539

      ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 3052

      ORA-06512: at ""APEX_050100.WWV_FLOW"", line 3028

      ORA-06512: at ""APEX_050100.WWV_FLOW"", line 5538

      ","2"

      "0.01700","0.00000","Add error onto error stack","4"

      "0.01700","0.00000","CGI: PATH_INFO = /wwv_flow.accept","9"

      "0.01700","0.00000","POST http://192.168.1.117/pls/dev1

      REMOTE_ADDR=192.168.1.100

      REMOTE_USER=APEX_PUBLIC_USER

      REFERER=http://192.168.1.117/pls/dev1/f?p=105:101:6585613103527::YES

      ","9"

      "0.01700","0.00000","SID=39

      USER=APEX_PUBLIC_USER

      INSTANCE=1

      ","9"

      "0.01700","0.00000","Error: Error processing request.","1"

      "0.01700","0.00000","- additional_info: ORA-28817: PL/SQL function returned an error.","4"

      "0.01700","0.00000","- display_location: ON_ERROR_PAGE","4"

      "0.01700","0.00000","- is_internal_error: true","4"

      "0.01700","0.00000","- apex_error_code: APEX.UNHANDLED_ERROR","4"

      "0.01700","0.00000","- ora_sqlcode: -28817","4"

      "0.01700","0.00000","- ora_sqlerrm: ORA-28817: PL/SQL function returned an error.","4"

      "0.01700","0.00000","- error_backtrace: ORA-06512: at ""SYS.DBMS_CRYPTO_FFI"", line 67

      ORA-06512: at ""SYS.DBMS_CRYPTO"", line 44

      ORA-06512: at ""APEX_050100.WWV_FLOW_CRYPTO"", line 198

      ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 2539

      ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 3052

      ORA-06512: at ""APEX_050100.WWV_FLOW"", line 3028

      ORA-06512: at ""APEX_050100.WWV_FLOW"", line 5538

      ","4"

      "0.01700","0.00000","- component: APEX_APPLICATION_PAGES Login (105000000101)","4"

      "0.01700","0.00000","...Show Error on Error Page","4"

      "0.01700","0.00000","......Performing rollback","4"

      "0.01700","0.00000","Show page template header","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/jquery/1.12.3/jquery-1.12.3.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/jquery/2.2.3/jquery-2.2.3.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Set RequireJS define jquery=apex.jQuery","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/core.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/debug.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/util.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/locale.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/lang.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/message.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=wwv_flow.js_messages?p_app_id=105&p_lang=en-us&p_version=5.1.2.00.09-8190031&p_builder=Y","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/storage.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/navigation.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/event.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/server.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/page.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/region.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/item.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/apex_5_0.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.util.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/dynamic_actions_core.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/dynamic_actions.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/jquery-ui/1.10.4/ui/jquery-ui-1.10.4.custom.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/actions.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.checkboxAndRadio.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.popupLov.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.selectList.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.textarea.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.yesNo.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.menu.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.toggleCore.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/widget.collapsible.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/tooltipManager.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/theme.js?v=5.1.2.00.09","4"

      "0.01700","0.00000","Load JavaScript file=/i/libraries/apex/legacy.js?v=5.1.2.00.09","4"

      "0.01700","0.01600","Load JavaScript file=/i/libraries/jquery-migrate/1.4.1/jquery-migrate-1.4.1.js?v=5.1.2.00.09","4"

      "0.03300","0.00000","Rendering form open tag and internal values","4"

      "0.03300","0.00000","Processes - point: AFTER_ERROR_HEADER","4"

      "0.03300","0.00000","Processes - point: BEFORE_ERROR_FOOTER","4"

      "0.03300","0.00000","Show page footer","4"

      "0.03300","0.00000","Show page tempate footer","4"

      "0.03300","0.00000","Rendering form close tag and page checksum","4"

      "0.03300","0.00000","Load JavaScript file=/i/libraries/apex/widget.menu.js?v=5.1.2.00.09","4"

      "0.03300","-","Load JavaScript file=/i/apex_ui/js/devToolbar.js?v=5.1.2.00.09","4"

       

      Has somebody an idea what's wrong?

      Many thanks in advance.

       

      Regards,

      Martin

        • 1. Re: Error in Login after Migration APEX from 4.2 to 5.1.2
          Kiran Pawar

          Hi Martin,

          Martin1 wrote:

           

          i migrated APEX from 4.2 to 5.1.2 and now i get the error ORA-28817 at Login. The application has a custom authentication scheme. The other two applications in the workspace has also a custom authentication scheme but there no error occurs at login.

           

          Here the debug log in detail:

          "Elapsed","Execution","Message","Level"

          "0.00000","0.01700","Reset NLS settings","4"

          ...

          "0.01700","0.00000","......Validate item page affinity","4"

          "0.01700","0.00000","Exception in ""final_exception_handler"":

          Sqlerrm: ORA-28817: PL/SQL function returned an error.

          Backtrace: ORA-06512: at ""SYS.DBMS_CRYPTO_FFI"", line 67

          ORA-06512: at ""SYS.DBMS_CRYPTO"", line 44

          ORA-06512: at ""APEX_050100.WWV_FLOW_CRYPTO"", line 198

          ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 2539

          ...

          "0.01700","0.00000","Error: Error processing request.","1"

          "0.01700","0.00000","- additional_info: ORA-28817: PL/SQL function returned an error.","4"

          "0.01700","0.00000","- display_location: ON_ERROR_PAGE","4"

          "0.01700","0.00000","- is_internal_error: true","4"

          "0.01700","0.00000","- apex_error_code: APEX.UNHANDLED_ERROR","4"

          "0.01700","0.00000","- ora_sqlcode: -28817","4"

          "0.01700","0.00000","- ora_sqlerrm: ORA-28817: PL/SQL function returned an error.","4"

          "0.01700","0.00000","- error_backtrace: ORA-06512: at ""SYS.DBMS_CRYPTO_FFI"", line 67

          ORA-06512: at ""SYS.DBMS_CRYPTO"", line 44

          ORA-06512: at ""APEX_050100.WWV_FLOW_CRYPTO"", line 198

          ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 2539

          ORA-06512: at ""APEX_050100.WWV_FLOW_SECURITY"", line 3052

          ORA-06512: at ""APEX_050100.WWV_FLOW"", line 3028

          ORA-06512: at ""APEX_050100.WWV_FLOW"", line 5538

          ","4"

          "0.01700","0.00000","- component: APEX_APPLICATION_PAGES Login (105000000101)","4"

          "0.01700","0.00000","...Show Error on Error Page","4"

          "0.01700","0.00000","......Performing rollback","4"

          ...

           

          Has somebody an idea what's wrong?

          The above debug logs marked in bold seem to be the cause of the issue.

          joelkallman-Oracle, Patrick Wolf-Oracle: What is "Validate item page affinity" after login? Does it have "final_exception_handler" which is invoked?

           

          Martin: As you said there other 2 applications in the workspace using the custom authentication scheme. So,

          • Can you post the details of the custom authentication scheme?
          • All the 3 applications (including the failing one) have the same custom authentication scheme? If not, what is the difference between the ones that are working and the one that is failing?

           

          Regards,

          Kiran

          • 2. Re: Error in Login after Migration APEX from 4.2 to 5.1.2
            Martin1

            Hi Kiran,

             

            many thanks for your reply.

            I created an authentication scheme with <Scheme Type>Custom. In the property 'Authentication Function Name' i inserted my PL/SQL authentication function 'ppm_auth.authenticate' which resides in the same database schema as all the other database objects of the application.

             

            At the Login page there are three processes.

            1. Set Username Cookie

               begin

                owa_util.mime_header('text/html', FALSE);

                owa_cookie.send(

                  name => 'LOGIN_USERNAME_COOKIE',

                  value => lower(:P101_USERNAME));

               exception when others then null;

               end;

            2. Login

               wwv_flow_custom_auth_std.login(

                 P_UNAME => :P101_USERNAME,

                 P_PASSWORD => :P101_PASSWORD,

                 P_SESSION_ID  => v('APP_SESSION'),

                 P_FLOW_PAGE   => :APP_ID||':1');

            3. Clear Page(s) Cache

             

            By the way i detected that my authentication function 'ppm_auth.authenticate' is never called during the login.

            And yes the other two application have principally the same authentication function but in another database scheme.

             

            Regards,

            Martin

            • 3. Re: Error in Login after Migration APEX from 4.2 to 5.1.2
              Martin1

              Hi Kiran,

               

              in the attachment there is the export of the application.

               

              Regards,

              Martin

              • 4. Re: Error in Login after Migration APEX from 4.2 to 5.1.2
                Patrick Wolf-Oracle

                Hi Martin,

                 

                the error occurs because your 'Login' page templates does contain two #FORM_OPEN# placeholders. One can be found in the 'Header' attribute and the other in the 'Body' attribute of your 'Login' template. To fix it,

                 

                1. open page 101 in Page Designer
                2. open the 'Page Shared Components' tab
                3. go to Templates > Pages > Login
                4. click 'Edit Component' to edit the page template
                5. Remote #FORM_OPEN# from the 'Header' attribute (it's at the bottom text).

                 

                Because the APEX engine replaces both, it will generate two different crypto keys which then confuses the Engine when the page gets submitted and values are decrypted. You might also want to check your other page templates for duplicate #FORM_OPEN# placeholders.

                 

                Regards

                Patrick

                • 5. Re: Error in Login after Migration APEX from 4.2 to 5.1.2
                  Martin1

                  Hi Patrick,

                   

                  your solution solved my problem - thanks a lot :-)

                   

                  Have a nice day and best regards,

                  Martin