14 Replies Latest reply: Nov 21, 2012 9:46 AM by 974193 RSS

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

    974193
      CREATE OR REPLACE FUNCTION XPC.FN_END_DEV_SERIAL(V_DETAIL_ID IN END_DEV_DETAIL_SERIALS.DETAIL_ID%type)
      RETURN VARCHAR2
      IS
      V_SERIAL_NUMBER VARCHAR2(4000) := NULL;
      V_SERIAL_NUMBER_LIST VARCHAR2(4000) :=NULL;
      v_flag number:=0;
      CURSOR CUR_SERIALNO IS
      SELECT TRIM(SERIAL_NUMBER)
      FROM END_DEV_DETAIL_SERIALS ep
      WHERE DETAIL_ID = V_DETAIL_ID;
      BEGIN

      OPEN CUR_SERIALNO;
      LOOP
      FETCH CUR_SERIALNO INTO V_SERIAL_NUMBER;

      --The second exit condition here will limit the size of the list to
      --the same size as the Deviation Note field
      EXIT WHEN (CUR_SERIALNO%NOTFOUND) OR
      ((LENGTH(V_SERIAL_NUMBER_LIST) + LENGTH(V_SERIAL_NUMBER) + LENGTH(', ')) > 4000);

      IF V_SERIAL_NUMBER IS NOT NULL --Don't add null Serial Numbers to the list
      THEN
      If V_SERIAL_NUMBER_LIST IS NOT NULL then
      select instr(V_SERIAL_NUMBER_LIST, V_SERIAL_NUMBER) into v_flag from dual;
      if v_flag >0 then
      V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER_LIST;
      else
      V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER_LIST || ', '||V_SERIAL_NUMBER ;
      END IF;
      ELSE
      V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER;
      END IF;
      END IF;
      END LOOP;

      RETURN V_SERIAL_NUMBER_LIST;

      EXCEPTION
      WHEN OTHERS THEN
      RAISE;

      END;
      /

      While executing this procedure through informatica iam getting the below error
      Message: Database driver error...
      CMN_1022 [
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at line 2

      Database driver error...
      Function Name : ExecuteSP
        • 1. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
          971895
          Increze the size of V_SERIAL_NUMBER_LIST to 32767 and try
          • 2. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
            BluShadow
            For starters...

            Remove:
            EXCEPTION
            WHEN OTHERS THEN
            RAISE;
            So that you don't mask which line is really causing the issue. (and it's a pointless exception handler to have anyway)

            Also, what database character set is being used? Does it have multi byte characters?
            • 3. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
              BluShadow
              968892 wrote:
              Increze the size of V_SERIAL_NUMBER_LIST to 32767 and try
              I don't think that's going to help the OP.

              Comment in the code says:
              --The second exit condition here will limit the size of the list to
              --the same size as the Deviation Note field
              So, allowing the code to create a string bigger than the field it's destined for, will just move the error to another place.
              • 4. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                Veejays.User10302525-Oracle
                As already suggested you can remove the exception block and find which line is causing the problem or you can use dbms_utility.format_error_backtrace in exception block to get the line number causing the problem. You can either return that line number or use dbms_output to print the line number if you use sql*plus for your test execution.
                • 5. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                  kendenny
                  As others have said, removing the exception block will pinpoint the exact statement which is causing the error. But something else I noticed.
                  select instr(V_SERIAL_NUMBER_LIST, V_SERIAL_NUMBER) into v_flag from dual;
                  if v_flag >0 then
                    V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER_LIST; -- you could just have NULL; here
                  If you have a serial number that is a substring of a previous serial number, then it won't make the list. Instead of doing this check add a distinct to your select in your cursor.
                  • 6. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                    rp0428
                    Welcome to the forum!

                    Whenever you post provide your 4 digit Oracle version.
                    >
                    While executing this procedure through informatica iam getting the below error
                    Message: Database driver error...
                    CMN_1022 [
                    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                    ORA-06512: at line 2

                    Database driver error...
                    Function Name : ExecuteSP
                    >
                    The logical thing to do when you get a message saying that your buffer is too small for the character string you are trying to put into it is to examine the character string you are trying to put into the buffer.

                    This line tries to populate a buffer; is it line 2?
                    FETCH CUR_SERIALNO INTO V_SERIAL_NUMBER;
                    {quote}
                    So how big is the value being fetched when things go wrong? You don't know because, as others have said, there is nothing in your exception handler to give you any information. And you have nothing ANYWHERE to give you any information about what you are getting.
                    
                    This is the cursor definition
                    CURSOR CUR_SERIALNO IS
                    SELECT TRIM(SERIAL_NUMBER)
                    FROM END_DEV_DETAIL_SERIALS ep
                    WHERE DETAIL_ID = V_DETAIL_ID;
                    What is the defined datatype of SERIAL_NUMBER? If it is bigger than 4000 you have a problem. 
                    
                    1. modify V_SERIAL_NUMBER to VARCHAR2(32767)
                    2. modify your code to append only the first 4000 characters of the variable
                    3. add DBMS_OUTPUT.PUT_LINE statements to your exception handler to print out: V_DETAIL_ID, the length of V_SERIAL_NUMBER, the contents of v_serial_number and the contents of V_SERIAL_NUMBER_LIST so you can see what the values are when the exception occurs.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    • 7. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                      974193
                      Hi
                      Thanks for your reply
                      Varchar2 max limit is 4000 and you asked me to increase VARCHAR2 size to 32767.
                      Will it work fine?
                      • 8. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                        Veejays.User10302525-Oracle
                        why not just test....
                        declare
                        lvar varchar2(32767);
                        begin
                        lvar := rpad('x',32767,'x');
                        dbms_output.put_line(lvar);
                        end;
                        • 9. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                          BluShadow
                          971190 wrote:
                          Hi
                          Thanks for your reply
                          Varchar2 max limit is 4000 and you asked me to increase VARCHAR2 size to 32767.
                          Will it work fine?
                          Varchar2 limit in PL/SQL is 32767
                          Varchar2 limit in SQL is 4000

                          So if your result string is required to be used in SQL statements or stored in a VARCHAR2 on a database column, it must be limited to 4000 bytes (not necessarily 4000 characters as this depends on whether you are using a multi-byte character set or not).
                          • 10. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                            974193
                            I have modified the scripts as

                            CREATE OR REPLACE FUNCTION XPC.FN_END_DEV_SERIAL(V_DETAIL_ID IN END_DEV_DETAIL_SERIALS.DETAIL_ID%type)
                            RETURN VARCHAR2
                            IS
                            V_SERIAL_NUMBER VARCHAR2(32767) := NULL;
                            V_SERIAL_NUMBER_LIST VARCHAR2(4000) :=NULL;
                            v_flag number:=0;
                            CURSOR CUR_SERIALNO IS
                            SELECT distinct TRIM(SERIAL_NUMBER)
                            FROM END_DEV_DETAIL_SERIALS ep
                            WHERE DETAIL_ID = V_DETAIL_ID;
                            BEGIN

                            OPEN CUR_SERIALNO;
                            LOOP
                            FETCH CUR_SERIALNO INTO V_SERIAL_NUMBER;

                            --The second exit condition here will limit the size of the list to
                            --the same size as the Deviation Note field
                            EXIT WHEN (CUR_SERIALNO%NOTFOUND) OR
                            ((LENGTH(V_SERIAL_NUMBER_LIST) + LENGTH(V_SERIAL_NUMBER) + LENGTH(', ')) > 4000);

                            IF V_SERIAL_NUMBER IS NOT NULL --Don't add null Serial Numbers to the list
                            THEN
                            If V_SERIAL_NUMBER_LIST IS NOT NULL then
                            select instr(V_SERIAL_NUMBER_LIST, V_SERIAL_NUMBER) into v_flag from dual;
                            if v_flag >0 then
                            V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER_LIST;
                            else
                            V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER_LIST || ', '||V_SERIAL_NUMBER ;
                            END IF;
                            ELSE
                            V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER;
                            END IF;
                            END IF;
                            END LOOP;

                            RETURN V_SERIAL_NUMBER_LIST;

                            END;
                            /

                            But still iam getting the same error.Could you please help on this
                            Message: Database driver error...
                            CMN_1022 [
                            ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                            ORA-06512: at line 2

                            Database driver error...
                            Function Name : ExecuteSP

                            Oracle Fatal Error
                            Database driver error...
                            Function Name : ExecuteSP

                            Oracle Fatal Error]
                            • 11. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                              VI
                              try by increasing V_SERIAL_NUMBER_LIST variable size to 32767
                              • 12. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                                974193
                                Getting the same error even after increased the variable size V_SERIAL_NUMBER_LIST to 32767
                                • 13. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                                  ascheffer
                                  This code does the same as your code
                                  CREATE OR REPLACE FUNCTION XPC.FN_END_DEV_SERIAL(V_DETAIL_ID IN END_DEV_DETAIL_SERIALS.DETAIL_ID%type)
                                  RETURN VARCHAR2
                                  IS
                                    V_SERIAL_NUMBER VARCHAR2(32767);
                                    V_SERIAL_NUMBER_LIST VARCHAR2(32767);
                                    CURSOR CUR_SERIALNO IS
                                    SELECT distinct TRIM(SERIAL_NUMBER)
                                    FROM END_DEV_DETAIL_SERIALS ep
                                    WHERE DETAIL_ID = V_DETAIL_ID
                                    AND SERIAL_NUMBER IS NOT NULL;
                                  BEGIN
                                    OPEN CUR_SERIALNO;
                                    LOOP
                                      FETCH CUR_SERIALNO INTO V_SERIAL_NUMBER;
                                  --The second exit condition here will limit the size of the list to
                                  --the same size as the Deviation Note field
                                      EXIT WHEN CUR_SERIALNO%NOTFOUND) OR
                                                LENGTH(V_SERIAL_NUMBER_LIST) + LENGTH(V_SERIAL_NUMBER) + LENGTH(', ') > 4000;
                                      IF V_SERIAL_NUMBER_LIST IS NULL
                                      THEN
                                        V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER;
                                      ELSE
                                        V_SERIAL_NUMBER_LIST := V_SERIAL_NUMBER_LIST || ', '||V_SERIAL_NUMBER ;
                                      END IF;
                                    END LOOP;
                                    RETURN V_SERIAL_NUMBER_LIST;
                                  END;
                                  Your error message says the error is in ExecuteSP
                                  Doesn't that mean that the error isn't in the function FN_END_DEV_SERIAL ?
                                  Do you know that in a database with a multi byte character set a string with 4000 characters don't have to fit in a PL/SQL variable varchar2(4000)
                                  Try increasing the size of the variable which is receiving the result of this function FN_END_DEV_SERIAL
                                  • 14. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
                                    974193
                                    The issue is resolved. I have increased the variable size which receives the output of the function FN_END_DEV_SERIAL
                                    Thanks for all.