3 Replies Latest reply: Mar 13, 2013 11:34 AM by rp0428 RSS

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

    716065
      hi,

      I am building a dynamic Select Query, every value in the parm list with have a select query.
      I have assigned the local variable as
      vc_query VARCHAR2(32767);

      vc_query := 'SELECT T1.ORG_ID' || vc_col || ' FROM ORG_MAIN T1, ORG_ATTRIBUTE T2 ' || vc_subcomp || ' ' || 'WHERE' || ' ' || vc_build_where || '' || vc_fac_type || '' || vc_fac || '' || 'AND' || ' TO_DATE( ' || '''' || vd_date || '''' || ', ''DD-MON-YY'') BETWEEN T1.ORG_START_DATE AND T1.ORG_END_DATE ' || ' ' || 'GROUP BY T1.ORG_ID' || vc_groupby || ' ' || 'ORDER BY T1.ORG_ID';

      here the vc_subcomp variable should bring in the 76 select queries.

      Since the varchar is only 32767 bytes. the dynamic SQL is not completely building up.
      How can I solve this problem.

      When I run the function , the error it shows is as below:-
      PKG_EXTRACT_ALL.f_main_facility(10,'(2,7,3,24,25,29,30,31,32,33,34,35,36)','(25,23,35,1,2,3,4,18,19,20,21,22,24,26,27,28,29,30,31,32,33,34,36,15,16,17,5,6,7,8,9,10,11,12,13,14)','ALL',NULL) FROM DUAL
      *
      ERROR at line 2:
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at "RXG12.PKG_EXTRACT_ALL", line 310

      Please help.Thank You

      Edited by: user8703472 on Mar 13, 2013 7:15 AM
        • 1. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
          riedelme
          user8703472 wrote:
          hi,

          I am building a dynamic Select Query, every value in the parm list with have a select query.
          I have assigned the local variable as
          vc_query VARCHAR2(32767);

          vc_query := 'SELECT T1.ORG_ID' || vc_col || ' FROM ORG_MAIN T1, ORG_ATTRIBUTE T2 ' || vc_subcomp || ' ' || 'WHERE' || ' ' || vc_build_where || '' || vc_fac_type || '' || vc_fac || '' || 'AND' || ' TO_DATE( ' || '''' || vd_date || '''' || ', ''DD-MON-YY'') BETWEEN T1.ORG_START_DATE AND T1.ORG_END_DATE ' || ' ' || 'GROUP BY T1.ORG_ID' || vc_groupby || ' ' || 'ORDER BY T1.ORG_ID';

          here the vc_subcomp variable should bring in the 76 select queries.

          Since the varchar is only 32767 bytes. the dynamic SQL is not completely building up.
          How can I solve this problem.

          When I run the function , the error it shows is as below:-
          PKG_EXTRACT_ALL.f_main_facility(10,'(2,7,3,24,25,29,30,31,32,33,34,35,36)','(25,23,35,1,2,3,4,18,19,20,21,22,24,26,27,28,29,30,31,32,33,34,36,15,16,17,5,6,7,8,9,10,11,12,13,14)','ALL',NULL) FROM DUAL
          *
          ERROR at line 2:
          ORA-06502: PL/SQL: numeric or value error: character string buffer too small
          ORA-06512: at "RXG12.PKG_EXTRACT_ALL", line 310
          This isn't really a SQL*Developer issue but rather a PL/SQL issue that you will have problems with regardless of your development tool. This forum is for issues regarding SQL*Developer and will probably get a better and faster response in the SQL and PL/SQL forum.

          Be that as it may, is the line you posted the cited line 310? Is the resulting text to put into vc_query > 32767 bytes (this is possible)? Do you get the same error if you comment the line you copied that assigns vc_query? If so then that line is a problem; if not the problem is on a different line.

          Make certain you are looking at line 310 (you can find this in SQL*Developer by enableing line numbers if you have not done so already).
          • 2. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
            716065
            Thank U Sir,

            I was not sure about where i am suppose to post my problem.

            Yes the size of the local variable is the problem. I posted it in the right place. Looking forward for a solution or idea to solve it. Thanks Again for directing me to the right plc.
            • 3. Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too smal
              rp0428
              WRONG FORUM!

              Please mark this question ANSWERED now that you have reposted it in the SQL and PL/SQL forum where it belongs.
              Re: ORA-06502:PL/SQL: numeric or value error: character string buffer too small