1 2 Назад Вперед 16 Ответы Последний ответ: 18.06.2019 15:36, автор: BluShadow

    CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    4008784

      Hi,

       

      I am using a CLOB datatype to store dynamic SQL inside a procedure. I am assigning the dynamic SQL by concatenating other variables and some static text.

      The total length of the dynamic SQL is greater than 32767 characters. This is working fine. However one of the variables used in concatenation will have value depending on a condition. So when this variable is having the value I am getting   6502 - character string buffer too small. The length of this variable text is just 630 characters even though we declared it with varchar2(32767).

      I am confused why i am getting this error for just extra 630 characters. Without this variable, the dynamic SQL length is around 60000 characters.

       

      Database: Oracle 11g

       

      I can't post the code as it is confidential.

       

      Psudo Code:

      V_CLOB := 'Static text..' || V_COND || 'static text....' || V2 || 'static text....' || V3;

        • 1. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
          mathguy

          Can you reproduce the issue by replacing your actual code (which I understand is confidential) with other, perhaps purely random, character strings of equal lengths? If you can, then you should post here the "made-up" example. If you can't, then perhaps the problem is not really caused by what you think.

          • 2. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
            cormaco

            Have you considered passing your values as bind variables instead of concatenating?

            https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/executeimmediate_statement.htm#LNPLS01317

            • 3. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
              Stew Ashton

              4008784 wrote:

               

              Hi,

               

              I am using a CLOB datatype to store dynamic SQL inside a procedure. I am assigning the dynamic SQL by concatenating other variables and some static text.

              The total length of the dynamic SQL is greater than 32767 characters. This is working fine. However one of the variables used in concatenation will have value depending on a condition. So when this variable is having the value I am getting 6502 - character string buffer too small. The length of this variable text is just 630 characters even though we declared it with varchar2(32767).

              I am confused why i am getting this error for just extra 630 characters. Without this variable, the dynamic SQL length is around 60000 characters.

               

              Database: Oracle 11g

               

              I can't post the code as it is confidential.

               

              Psudo Code:

              V_CLOB := 'Static text..' || V_COND || 'static text....' || V2 || 'static text....' || V3;

              Try

               

              V_CLOB := TO_CLOB('Static text..') || V_COND || 'static text....' || V2 || 'static text....' || V3;

               

              Bests regards,

              Stew Ashton

              • 4. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                4008784

                Hi,

                TO_CLOB works. However I want to understand when the specific error comes during concatenation. Since the string literal concatenation extends more than 60000 characters without error. It's only happening when that extra data of 630 characters is present.

                • 5. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                  Stew Ashton

                  4008784 wrote:

                   

                  Hi,

                  TO_CLOB works. However I want to understand when the specific error comes during concatenation. Since the string literal concatenation extends more than 60000 characters without error. It's only happening when that extra data of 630 characters is present.

                  I see, you don't care if it works, but you want to know why it doesn't?

                   

                  For deeper analysis, post a reproducible test case.

                   

                  Regards,

                  Stew

                  • 6. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                    Paulzip

                    I suspect your issue is you are concatenating two strings on the right hand side that exceed the limits for varchar2. Each individual concatenation operation follows the following rules....

                    The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to the CHAR maximum (e.g. In SQL it is 2000 characters). If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to the VARCHAR2 maximum (e.g. In SQL this is 4000 characters). If either argument is a CLOB, the result is a temporary CLOB.

                    The solution is to cast such strings in these concatenation operations to clob, or use DBMS_LOB.WRITEAPPEND which is faster anyway.

                    • 7. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                      _jum

                      Seems to works until concatenated string exceeds maxINT (65535) or is cast to CLOB (ORACLE 11.2.0.,12.1.0.):

                       

                      SET SERVEROUTPUT ON SIZE 900000;
                      
                      DECLARE
                        v_clob CLOB;
                        v_cond VARCHAR2(4000) := 'test';
                      BEGIN
                        v_clob := rpad('#',19)||rpad('#',32756)||V_COND||rpad('#',32756);
                        --65535
                        dbms_output.put_line( length(rpad('#',19)||rpad('#',32756)||V_COND||rpad('#',32756)));
                        --65536
                        --doesn't work ORA-06502
                        --V_CLOB := rpad('#',20)||rpad('#',32756)||V_COND||rpad('#',32756);
                        --works always
                        v_clob := to_clob(rpad('#',20))||rpad('#',32756)||V_COND||rpad('#',32756);
                        --65536
                        dbms_output.put_line( length(to_clob(rpad('#',20))||rpad('#',32756)||V_COND||rpad('#',32756)));
                      END;
                      
                      • 8. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                        4008784

                        It is throwing error as the string concatenation exceeds 65535 characters. Is this the max for varchar2 type?

                        • 9. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                          BluShadow

                          4008784 wrote:

                           

                          It is throwing error as the string concatenation exceeds 65535 characters. Is this the max for varchar2 type?

                           

                           

                          No, the max for varchar2 is 4000 bytes in SQL, on versions prior to 12c; or 32767 bytes from 12c onwards (providing extended varchar's are enabled).

                          In PL/SQL, then varchar2's have a maximum of 32767 bytes, regardless of version.

                           

                          Note, the key thing here is "bytes" not "characters".  Different charactersets may have different numbers of bytes per character. i.e you may have 2 or 4 bytes per character in some charactersets, or even variable bytes per character.

                          • 10. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                            4008784

                            The right hand side of the string concatenation is throwing error if it exceeds 65535 characters. In PL/SQL the max for Varchar2 is 32767 bytes. So the right hand side should throw error if the concatenated string exceeds 32767 characters rt? Why is it throwing error after it exceeds 65535 characters?

                            Also i verified how many characters can be stored in varchar2(1) and only one character is being stored.

                             

                            I am using Oracle 11g

                            • 11. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                              BluShadow

                              4008784 wrote:

                               

                              The right hand side of the string concatenation is throwing error if it exceeds 65535 characters. In PL/SQL the max for Varchar2 is 32767 bytes. So the right hand side should throw error if the concatenated string exceeds 32767 characters rt? Why is it throwing error after it exceeds 65535 characters?

                              Also i verified how many characters can be stored in varchar2(1) and only one character is being stored.

                               

                              I am using Oracle 11g

                               

                               

                              You say it, but you don't prove it.  Show your code that proves your statement.

                              When I concatenate strings in to a clob, ensuring the first string is converted to a clob explicitly, I have no problem with the number of characters/bytes...  proof...

                               

                              SQL> ed
                              Wrote file afiedt.buf

                                1  declare
                                2    big clob;
                                3    myvarchar varchar2(32767) := rpad('x',32767,'x');
                                4  begin
                                5    big := to_clob(myvarchar) || myvarchar || myvarchar;
                                6    dbms_output.put_line('My Clob is '||length(big)||' characters long');
                                7* end;
                              SQL> /
                              My Clob is 98301 characters long

                              PL/SQL procedure successfully completed.

                              • 12. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                4008784

                                I am not using To_clob on the right hand side. Just concatenation of strings. If u try removing the To_ clob on the right hand side it is throwing error after 65535 characters. I just want to understand if concatenation of strings will be treated as varchar type then why is it throwing error after 65535 instead of 32767.

                                • 13. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                  mathguy

                                  4008784 wrote:

                                   

                                  I am not using To_clob on the right hand side. Just concatenation of strings. If u try removing the To_ clob on the right hand side it is throwing error after 65535 characters. I just want to understand if concatenation of strings will be treated as varchar type then why is it throwing error after 65535 instead of 32767.

                                   

                                  Just speculation since I have no way of knowing how the Oracle programmers wrote the code behind all of this...

                                   

                                  The old limit of 4000 bytes, or 2000 for CHAR, etc., never make sense to me. Why 4000 and not 4073? 4073 has no special properties - AND NEITHER DOES 4000. 4095 would be more natural (it's 2^12 - 1), although I'm not sure why an exponent of 12 would be relevant.

                                   

                                  32767 makes more sense, but why not 65535?

                                   

                                  Apparently Oracle models VARCHAR2 as byte (not character!) arrays in C, using 16-bit integers for array indexes. Now, the range of 16-bit integers is from -32768 to +32767, which may shed some light on the 32767 limit.

                                   

                                  On the other hand, Oracle is written (mostly) in C, and C has the UNSIGNED INT data type - values range from 0 to 65535, It makes a lot of sense to index byte arrays (or ANY C arrays, really) using UNSIGNED integers of any size, not signed ones.

                                   

                                  So, to me the 32767 limit makes no sense, but that's how Oracle's coders wrote PL/SQL and then SQL (for the extended VARCHAR2 data type). Then, for some reason, in some operations they must be using unsigned integers, and they don't check for "overflow" (which doesn't really exist, it's only an "overflow" with reference to the Oracle specification) when the indexes grow above 32767. An error - a real one, due to physical limitations - is only reached when the indexes grow above 65535.

                                   

                                  Best advice is to NOT rely on such nonsense. Oracle says 32767, so you are best not to stretch the limits just because "it works anyway." It may work today, then Oracle tomorrow may change that.

                                  1 пользователь считает эту информацию полезной
                                  • 14. Re: CLOB: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                                    BluShadow

                                    mathguy, I think it related more to the block sizes, especially on earlier versions of Oracle, where the recommendation was 2K or 4K, hence 4000 bytes, plus some for the additional bits of data needed.

                                    Of course, with technology moving on, now with 64 bit processors and larger memory models, the possibilities for expansion and dealing with larger amounts of data in a single hit is much easier.  Hence Oracle now allowing 32K.

                                     

                                    However, the 65535 limit issue being observed in PL/SQL is (and I admit it's an educated guess) likely due to an allowance of double the size of a varchar2 to allow for internal manipulation of strings without causing loss i.e. for the purposes of concatenating and then trimming etc. without losing data half way through the process, only to be finally truncated at the end to fit in with the 32767 byte limit.  If the varchar2 was 65535 bytes in limit then I can imagine they'd need double that to deal with string manipulation internally.

                                    1 пользователь считает эту информацию полезной
                                    1 2 Назад Вперед