1 2 3 Previous Next 40 Replies Latest reply: Sep 15, 2011 7:05 AM by MortenBraten RSS

    Listener Error - character string buffer too small (ORA-06502)

      I am running into a very strange problem with the APEX listener on seemingly random pages. I can hit every page in my application just fine, but as soon as I hit this specific one, Glassfish throws an HTTP 500 error. If I click Debug, the page seems to load fine with no indications of trouble, but as soon as I turn debug back off, it goes back to the HTTP 500 error. I don't see anything special about the page that makes this happen. It is pretty simple and has 4 regions. I have noticed that if I set any 1 of the 4 regions to "Never display", the page loads fine. It's like having all 4 of them enabled at once are causing some overload, even though it's actually a smaller amount of data than most of my other pages.

      **Update: I just discovered this only happens if I'm logged into the workspace first and then try to run the page! If I log out of APEX and then hit the application as a normal user, page loads error-free. This is still an annoying problem, but at least it seems I have a decent workaround since regular users never see it.

      After a couple days, I noticed the exact same problem on a second APEX application I'm using. Again, it is on some random page whereas all the other pages work fine.

      Both applications and pages in question worked without issue in APEX 3.x. I am trying to get an APEX application up and running on the latest version.

      I have tried both Glassfish server and simply downloading the latest listener (version 1.1.1) and running it in standalone mode. I get the problem both ways, which is why it seems it's a listener issue.

      I have seen a few other threads of people having this problem, but I never did find anyone with a solution, and most of the posts stopped back in December.

      Some details on my environment:
      Database version: Oracle Database 10g Enterprise Edition Release - 64bi
      APEX version: 4.0.2
      Webserver: Glassfish 3.1

      Here is the log entry from Glassfish when the HTTP 500 error displays as I try to load one of the bugged pages.


      init: # headers=46
      declare nm owa.vc_arr := ?;
      vl owa.vc_arr := ?;
      owa.init_cgi_env( ?, nm, vl );
      htp.init; htp.HTBUF_LEN := 63;
      ? := sys_context('USERENV','SID');

      p:100:2:220529248574492::NOPAGE CALL:
      nlns number := 999999;
      l_clob CLOB;
      lines htp.htbuf_arr;
      l_buff varchar2(32767);
      l_clob_init boolean:= false;
      l_file varchar2(5);
      l_doc_info varchar2(1000);
      OWA.GET_PAGE(lines, nlns);
      if (nlns > 1) then
      for i in 1..nlns loop
      if ( length(lines(i)) > 0 ) then
      if ( ( lengthb(l_buff) + lengthb(lines(i))) > 32767) then
      if (NOT l_clob_init) then
      dbms_lob.createtemporary(l_clob, TRUE);
      dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
      end if;
      l_buff := lines(i);
      l_buff := l_buff || lines(i);
      end if;
      end if;
      end loop;
      end if;
      if (l_clob_init) then
      l_buff := '';
      end if;
      ? := l_clob;
      ? := l_buff;
      if (wpg_docload.is_file_download) then l_file:='TRUE'; wpg_docload.get_download_file(l_doc_info); else l_file := 'FALSE'; end if; ? := l_file;
      ? := l_doc_info;
      get_page FAILED:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at line 33

      Edited by: BrianB on May 11, 2011 7:50 AM

      Edited by: BrianB on May 11, 2011 8:01 AM
        • 1. Re: Listener Error - character string buffer too small (ORA-06502)
          Hi Brian,

          do you get the same error when you use a different web server than the APEX Listener, e.g. the EPG? Or did you use APEX Listener before for your APEX 3.x? It would be interesting to know if this error is related to the APEX Listener at all and if so, if it is related to a certain APEX version or a general issue.
          It would be helpful to know a bit more about your NLS-environment, e.g. do you use any special character sets in database or in your GlassFish? One reason for the buffer error could be some conversion problem.

          I'm sorry I can't be more helpful than answering with questions... But hopefully this will help to isolate the root cause for that problem.

          • 2. Re: Listener Error - character string buffer too small (ORA-06502)
            For my 3.x environment, I was using the embedded listener with OracleXE.

            In the process of upgrading, I did temporarily have a separate OracleXE instance set up using the embedded listener with APEX 4.0. I don't remember getting the error then, but I also didn't test that setup much because I decided to scrap it, move the database into our enterprise environment, and use the listener with Glassfish on my server. If I get some extra time, I may eventually try to set up another instance to test with the embedded listener, but I know I won't be using that configuration anymore.

            I have no special NLS vars set up for Glassfish. The only environment variable that I see set is NLS_LANG=AMERICAN_AMERICA.AL32UTF8. My server is running RHEL 5.

            On top of that, because I've been able to reproduce the problem using the Listener standalone (java -jar apex.war), I have eliminated Glassfish from the problem completely. Whatever is causing this is coming from the listener itself.
            • 3. Re: Listener Error - character string buffer too small (ORA-06502)

              just to make sure we talk about the same things, I'd be interested to know if the following ...
              For my 3.x environment, I was using the embedded listener with OracleXE.
              means you used the Embedded PL/SQL Gateway (EPG powered by XDBs HTTP Server) from the database, not the APEX Listener. Is that right?

              You can enable the EPG at any time and use it next to the APEX Listener. Just make sure both web servers don't use the same port, as they both default to 8080.
              To change the port used by your APEX Listener in standalone mode, you just need to add a parameter to the start, e.g.
              java -Dapex.port=8888 -jar apex.war
              will start it using port 8888.
              To enable the EPG again, follow the steps as documented in the [url http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/otn_install.htm#CHDDGBCA]APEX Installation Guide

              Unfortunately, I can't tell you what causes this error yet. Troubleshooting will take a little time, which you currently don't have, unfortunately. Since it seems the error only occurs when you run the application from the Application Builder, you could let your production run on the APEX Listener and use EPG for your development. In that case, it could make more sense to change the port used for the EPG and let the APEX Listener on the default port, so you don't have to announce a new URL.
              Anyway, it would be interesting to know if the same error occurs with the EPG.

              • 4. Re: Listener Error - character string buffer too small (ORA-06502)
                Yes, my 3.x environment was using the EPG that came with the OracleXE install.

                The tricky thing now is that I've moved APEX and my database into our production database cluster environment -- I'm not a DBA, so I don't have rights to run things as SYS like I did with my local XE instance, and I don't think they want the EPG enabled in their environment.

                I think what I'll try to do within the next few days is upgrade my old XE instance (it's still hanging around at the moment) to 4.0 and see if the error still happens with the EPG there alongside the standalone listener.
                • 5. Re: Listener Error - character string buffer too small (ORA-06502)
                  OK, I can now confirm that this bug does NOT happen with the embedded listener (EPG). It only happens when using one of the options involving the separate listener (Glassfish, Listener standalone, etc.).

                  I went back to my XE instance and upgraded my 3.0 APEX environment to 4.0. I connected through the EPG and hit the page in question - no error.

                  I then started up a standalone APEX listener on the same server (java -jar apex.war) and had it connect to the XE instance. I hit the same page in question: HTTP 500 error (ORA-06502 displaying on my listener console).

                  So the question now is this: What is Oracle doing differently within the EPG that the standalone APEX listener is doing?
                  • 6. Re: Listener Error - character string buffer too small (ORA-06502)
                    Hi Brian,

                    could you reproduce this page on apex.oracle.com, so I could try to import it in my APEX installation behind a Listener?
                    If that's not possible, could you post the APEX debug log for that page call?
                    In most cases, the error is related to NLS or characterset settings. These may be handled differently in EPG and Listener, as the Listener may run with separate settings, whereas your EPG obviously always uses the same as the database.

                    • 7. Re: Listener Error - character string buffer too small (ORA-06502)
                      I put the application here: http://apex.oracle.com/pls/apex/f?p=52654.

                      workspace: sandb
                      user: testadmin
                      password: password

                      The error happens on page 2, or when you click on the "view notifications" link on certain records. On my instance, the error doesn't happen on every record, just some, and I can't really tell what the pattern is for which ones do and don't.

                      Even when I have debug turned on, when the error happens, the page does not seem to actually load at all, so no debug log is ever actually created. The most meaningful logged error I can find is with the listener itself, which is in the original post in the thread.
                      • 8. Re: Listener Error - character string buffer too small (ORA-06502)
                        Could you either post the ddl for the data model of that application or give the testadmin access to SQL Workshop on the workspace at apex.oracle.com?
                        I can import and run the application, but of course I don't get the error you posted but an error for the missing tables. ;)
                        I probably don't need the actual data, but could you give me an idea of how many rows there are in these tables and if the columns contain any special characters?


                        • 9. Re: Listener Error - character string buffer too small (ORA-06502)
                          The testadmin user should have access to SQL Workshop now. There are three tables, and they're all pretty small - the biggest one has almost around 800 rows. The most "special" characters that are in the data is the occasional "@" symbol in the e-mail address fields.
                          • 10. Re: Listener Error - character string buffer too small (ORA-06502)
                            Hi Brian,

                            I just replicated your data model into my schema in my development instance and even imported the data I found on apex.oracle.com. Surprisingly, I get no error on my site, despite one on page 5 for the missing database link (in P5_CURRENT_USERS and P5_ADD_USERS) and one on pages 4 and 19 (similar reason, report Policies and      Associated Policies, respectively). After removing that link, all pages came up fine. I could enable "Eligible Delete Users" on page 1 without receiving any error afterwards.
                            So it seems, this is no error in your application, and it's not easily reproducible with an APEX Listener in a different environment.
                            I can also confirm that the data in those tables will not cause that error you get.

                            What could help to get a clue would be to compare your settings when running on EPG and Listener. To do so, you could just disable a region on page 1 and capture the debug output. Do the same with the EPG and put the output lists side by side. If you see any difference disregarding elapsed/execution time and the session id, this is what we should put our attention on next. You could just turn the time columns of in the report and use a text diff.

                            • 11. Re: Listener Error - character string buffer too small (ORA-06502)

                              I have modified the application to try and boil it down to just the error section. There are now just two tables: notify_user_policies (1 row) and ptl_backup_policy (269 rows). Don't worry about clicking tabs or buttons or anything - if the error happens as it's happening for me, you will see it immediately when the page tries to load. The data is exactly the same data that I'm working with in my environment where the error is being thrown. For whatever reason, the data DOES seem to be important in some way because if I change it or delete a bunch of random rows, the error goes away. I'm curious to see if you can reproduce the error now if you use my exact setup including the particular data I'm using.

                              To summarize, here are a few items I've discovered while troubleshooting:
                              -If I view the application outside of the apex builder (as a normal user), the error doesn't happen
                              -If I view the application within the apex builder but using the Embedded listener, the error doesn't happen
                              -If I set any of the 6 items on the page to "display never", the error doesn't happen
                              -If I set the two select lists to pull their source from the same table, the error doesn't happen
                              -If I set the user_email item to null, the error doesn't happen
                              -If I delete any single record out of ptl_backup_policy, the error doesn't happen

                              As you suggested, I did a diff of the debug output when viewing the page through the EPG vs. Listener (with one field set to display:never). The only differences were between lines 15 - 19 where it seems like EPG outputs additional details about checking to reuse the builder session. Everything else was identical, including the NLS settings output.

                              EPG Debug Output_
                              13 NLS: Language=en-us,4
                              14 "Application 103, Authentication: CUSTOM2, Page Template: 1804215068824361",4
                              15 "...Determine if user ""SANTEAM"" workspace ""941110468482121"" can develop application ""103"" in workspace ""941110468482121""",4
                              16 ...ok to reuse builder session for user:ANONYMOUS,4
                              17 "...Application session: 3728183060851423, user=ANONYMOUS",4
                              18 "...Determine if user ""SANTEAM"" workspace ""941110468482121"" c
                              19 ...Check for session expiration:,4

                              _*Standalone Listener Debug Output*_
                              13 NLS: Language=en-us,4
                              14 "Application 103, Authentication: CUSTOM2, Page Template: 1804215068824361",4
                              15 ...Session ID 1781440369740232 can be used,4
                              16 "...Application session: 1781440369740232, user=SANTEAM",4
                              17 "...Determine if user ""SANTEAM"" workspace ""941110468482121"" can develop application ""103"" in workspace ""941110468482121""",4
                              18 ...Check for session expiration:,4

                              Edited by: BrianB on May 23, 2011 1:15 PM
                              • 12. Re: Listener Error - character string buffer too small (ORA-06502)

                                this post is going to get a bit longer, so the summary comes first.
                                h6. Summary
                                1. I could reproduce the problem on my system using the APEX Listener in standalone mode.
                                2. I don't think the problem is content-related in the sense that you have any issue in your page or database contents.
                                3. I have a workaround for your problem.
                                h6. Error message
                                Having that error in my environment made me start to think. I not only disabled some item and got it work, but I could also add something to achieve this effect.

                                So I came to think, this really is somewhere deep down. The error message doesn't seem very helpful on first sight, but when starting to follow on what's happening there, things get clearer:
                                APEX generates pages dynamically, replacing substitution strings and other tokens to get the actual page definition. This has to be read by a requesting client. The use of VARCHAR2 as buffer introduces a limit of 32767 bytes, after which the contents is handled as CLOB instead.
                                h6. Analysis
                                Obviously, there are cases where the "estimation" fails. Of course, this is may only be relevant in rare cases, because:
                                1. If a page would exceed the maximum without some charset interpretation problem, the buffer would be switched to clob.
                                2. If a page stays small enough to stay below 32767 even with some characters that are acutally larger then expected, the buffer isn't busted.

                                To find out, if your could be one of these rare cases, I investigated the HTTP headers, focussing on X-DB-Content-length and made an odd observation.

                                Test case 1: "Go" button disabled, so the page runs fine with APEX Listener

                                1. It has *31968 bytes* when coming from APEX Listener with a Go button disabled.
                                2. It has *31938 bytes* according to the header set by EPG - for exactly the same page.

                                That makes a difference of 30 bytes for what is expected to be the same contents.

                                Test case 2: The button is enabled again
                                1. This causes the page load to crash in APEX Listener.
                                2. EPG transports *32341 bytes* according to that header.

                                So we are pretty close to the hard limit for the VARCHAR2 buffer.
                                For some reason, APEX Listener seems to cause a false calculation of the actual page size. Whether this is due to some charset problem or due to some other problem with response handling, I don't know. The 30 bytes difference may result from the odd header "X-ORACLE-IGNORE" with value "IGNORE, IGNORE, IGNORE, IGNORE" sent by the APEX Listener. This value has exactly 30 bytes in length, but this could be coincidence, as there are more differences in headers. If I add the size of all headers, we are even closer to the buffer limit and probably exceed it when some items need more bytes than expected.
                                This could even be as simple as a line break, as your page has about 424 lines when I disabled the button... Adding 1 byte per line to the 32341 bytes of the EPG, I get 32765 bytes. Now add that button (403 bytes difference on EPG) and you exceeed the limit. Reduce that value by the line count again and you are still below.
                                Could be coincidence as well, but makes me wonder.

                                h6. Workaround
                                To make sure that we were actually hitting that limit, I now introduce my suggestion for a workaround.

                                Test case 3: Add a hidden item
                                1. APEX Listener loads the page, stating the size to be *32876 bytes*
                                2. EPG sees 30 bytes less and transmits the header with *32846 bytes*
                                h6. Conclusion
                                I can't give you a real solution for that problem, nor do I have a definitive answer on what is the root cause for it. It seems, only one of the developers may find it. But I can offer you a workaround, which is to just add some hidden item to your page so it exceeds the limit for the VARCHAR2 buffer and gets handled as clob.
                                Note that this may occur outside the app builder as well. The app builder just renders some additional items when starting that page, so it has a different size from its productive representation. On the other hand, I may start counting the size of that additional page section - I wouldn't wonder if that results in a value around 400 bytes, and this is the forgotten part...
                                Unfortunately, if it actually is happening outside of the app builder, this workaround isn't very handy: Dynamic contents can't be calculated that easy all the time, so you may have cases where you just don't know in advance if you are close to the limit and have to add some item to exceed it or if you've already exceeded it or if you are far below, or close enough to actually hit it when adding just one byte...

                                • 13. Re: Listener Error - character string buffer too small (ORA-06502)

                                  Wow, thank you for that great analysis! I was very glad to see you were able to reproduce it yourself; seeing the issue as a problem with how APEX dances between which buffer to use explains the strangeness of it all. It's still a little odd how it never seems to happen with the EPG. Anyway, the workaround seems good enough for me for the moment. Hopefully Oracle handles this in the next version or so of the Listener.

                                  Thanks again!
                                  • 14. Re: Listener Error - character string buffer too small (ORA-06502)
                                    It's still a little odd how it never seems to happen with the EPG.
                                    Well, the EPG probably uses a different implementation. And as it is always inside the database, it is more likely not to have any charset compatiblily issues (if this actually the reason). But the most important advantage is: EPG is out for several years, so it's had a lot of time to get mature.
                                    Hopefully Oracle handles this in the next version or so of the Listener.
                                    I hope so too. There have been quite some posts on that error. Some seemed to be resolved, as the error disappeared after some reconfiguration in these cases (e.g. changing the charset used by the JEE-Server). But possibly what was regarded to be the solution was just just a workaround that changed the actual size for the error-causing page, so it hit didn't hit the limit in that state any longer.
                                    It might be hard to find out what's the root cause here. Even I as outsider have a few guesses, there may actually be more... On the other hand, we have at least one example that allows for constant reproduction, even with workaround, so it should be "debuggable".

                                    1 2 3 Previous Next