5 Replies Latest reply on Dec 14, 2011 1:07 PM by gabs

    Constant ORA-06502 errors in Application Builder after upgrading to 1.1.3.2

    gabs
      Hello,
      After upgrading to 1.1.3.2, I keep getting random ORA-06502 errors in the application builder. If I reload the page a few times it goes away, but comes back after a few page loads / clicks.
      I've done a lot of debugging and have found that the error goes away if I alter the session parameter from NLS_COMP=LINGUISTIC to NLS_COMP=BINARY only problem is that every time a new session is created the parameter is back to LINGUISTIC. Setting NLS_COMP=BINARY in ENV and the spfile has no effect, the listener seems to override it somewhere.

      I first though it was an APEX 4.1 issue, but since the problem is not present when logging in through the embedded PL/SQL gateway, my finger is now pointing at the Listener.
      I'm sure theres a deeper reason for this issue, the listener should work with all NLS settings - but right now I'm after a quick fix and the fastest one I can think of is to somehow set NLS_COMP = BINARY for all apex sessions. Apart from creating a logon trigger for the APEX_PUBLIC_USER, does anyone have any other suggestions?

      """
      The error is:
      ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters
      Error processing authorization.
      """

      and is only present in the Application Builder, when using the latest Application Express Listener.

      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      Application Express 4.1.0.00.32
      ApexListener Version:1.1.3.243.11.40
      Patch p12920544_112020_Generic
      Patch p13355663_112020_Generic
      Patch p13045147_112020_Generic

      I've seen reports of users getting this on apex.oracle.com ( Problem on apex.oracle.com - ORA-06502 ) so i'm hoping theres a solution at hand.

      cheers,
      gabs
        • 1. Re: Constant ORA-06502 errors in Application Builder after upgrading to 1.1.3.2
          gabs
          Anyone know how I can set NLS parameters for AEL JDBC Thin connections?
          • 2. Re: Constant ORA-06502 errors in Application Builder after upgrading to 1.1.3.2
            Udo
            Hello,

            as far as I know, apex.oracle.com is not hosted by APEX Listener, at least it was not the last time I checked.
            Furthermore, APEX Listener isn't really aware what kind of APEX application you run. If the behaviour of changing the NLS-Parameters exists in the Application Builder (which is an APEX application itself), it should exist in your applications as well if it is set by the APEX Listener.
            This makes me assume this is nothing that is done by APEX Listener. Since this kind of observation hasn't reported before with APEX 4.0, and the thread you referenced also seems to refer to APEX 4.1, my guess would be the issue is related to APEX. Fortunately, you've opened a thread in the APEX-Forum already: {thread:id=2319619}. Please avoid double posting and decide which path you want to continue.
            I'd stay with the APEX path, especially because there are other similar reports already.

            -Udo
            1 person found this helpful
            • 4. Re: Constant ORA-06502 errors in Application Builder after upgrading to 1.1.3.2
              Udo
              Seems to be the same issue that has been solved in {thread:id=2211962} (jQuery Autocompletion with improper changes to NLS parameters; solution posted [url http://tylermuth.wordpress.com/2010/03/16/jquery-autocomplete-for-apex/#comment-2511]in this comment.

              -Udo
              P.S.: Please either mark as correct or please post the actual cause for the problem, so others get a clue on what they should be searching for.

              Edited by: Udo on 13.12.2011 21:16
              • 5. Re: Constant ORA-06502 errors in Application Builder after upgrading to 1.1.3.2
                gabs
                As mentioned in the Apex forum a jQuery autocompletion application process was indeed the problem, the cause on the other hand is documented in Bug 8580542 - ASSOCIATIVE ARRAY SHAPE IS NOT CONSISTENT WITH SESSION PARAMETERS which states that NLS settings should not be changed between populating and accessing the associative array. The application process was interfering with the APEX flow and breaking this rule.

                To solve it I first went with a similar solution to the one you posted in your comment above, but since the alter session statement apparently affects other users in the connection pool (even dough for a very short amount of time). I decided to play it safe and remove any alter session statements in the application processes all together.

                I then solved my linguistic sort issues by implementing the NLSSORT function ( http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch9sql.htm#NLSPG441 ).

                I'm sorry for my late reply, but keep in mind that we are in different time zones ;)

                .g