title: ORA-06502: PL/SQL: numeric or value error: character string buffer too small with Oracle aggregate functions
body: <p>Good day gurus,</p>
  
  <p>I have a script that populates tables on a regular basis that crashed and gave the above error. The strange thing is that it has been running for close to 3 months on the production system with no problems and suddenly crashed last week. There has not been any changes on the tables as far as I know.</p>
  
  <p>Has anyone encountered something like this before? I believe it has something to do with the aggregate functions I'm implementing in it; but it worked initially.</p>
  
  <p>please; kindly find attached the part of the script I've developed into a procedure that I reckon gives the error.</p>
  
  <p>CREATE OR REPLACE PROCEDURE V1
    IS</p>
  
  <p>--DECLARE</p>
  
  <pre><code>    v_a       VARCHAR2(4000);
      v_b       VARCHAR2(4000);
      v_c       VARCHAR2(4000);
      v_d       VARCHAR2(4000);
      v_e       VARCHAR2(4000);
      v_f       VARCHAR2(4000);
      v_g       VARCHAR2(4000);
      v_h       VARCHAR2(4000);
      v_i       VARCHAR2(4000);
      v_j       VARCHAR2(4000);
      v_k       VARCHAR2(4000);
      v_l       VARCHAR2(4000);
      v_m       VARCHAR2(4000);
      v_n       NUMBER(10);
      v_o       VARCHAR2(4000);
  </code></pre>
  
  <p>--<br>
        -- Procedure that populates DEMO table</p>
  
  <pre><code>        BEGIN
  
                -- Delete all from the DEMO table
                DELETE FROM DEMO;
  
                -- Populate fields in DEMO from DEMOV1
                INSERT INTO DEMO(ID, D_ID, CTR_ID, C_ID, DT_NAM, TP, BYR, ENY,
                                 ONG, SUMM, DTW, REV, LD, MD, STAT, CRD)
                SELECT ID, D_ID, CTR_ID, C_ID, DT_NAM, TP, TO_NUMBER(TO_CHAR(BYR,'YYYY')), 
                     TO_NUMBER(TO_CHAR(NVL(ENY,SYSDATE),'YYYY')), CASE WHEN ENY IS NULL THEN 'Y' ELSE 'N' END, SUMMARY, DTW,
                     REV, LD, MD, '1', SYSDATE 
                FROM DEMOV1;
  
            -- LOOP THROUGH DEMO TABLE
            FOR j IN (SELECT ID, CTR_ID, C_ID FROM DEMO)
            LOOP
  
  
  
  
                  Select semic_concat(TXTDESC)
                  INTO v_a 
                  From GEOT
                  WHERE ID = j.ID;
  
  
  
                 SELECT COUNT(*)
                 INTO v_n
                 FROM MERP M, PROJ P
                 WHERE M.MID = P.COD
                 AND ID = j.ID
                 AND PROAC IS NULL;
  
                 IF (v_n &gt; 0)
                 THEN
  
  
                      Select semic_concat(PRO)
                      INTO v_b 
                      FROM MERP M, PROJ P
                      WHERE M.MID = P.COD
                      AND ID = j.ID;
  
                 ELSE
  
  
                      Select semic_concat(PRO || '(' || PROAC || ')' )
                      INTO v_b 
                      FROM MERP M, PROJ P
                      WHERE M.MID = P.COD
                      AND ID = j.ID;
  
                 END IF;
  
  
                  Select semic_concat(VOCNAME('P02',COD))
                  INTO v_c 
                  From PAR
                  WHERE ID = j.ID;
  
  
  
                  Select semic_concat(VOCNAME('L05',COD))
                  INTO v_d 
                  From INST
                  WHERE ID = j.ID;
  
  
                  Select semic_concat(NVL(AUTHOR,'Anon') ||' ('||to_char(PUB,'YYYY')||') '||TITLE||', '||EDT)
                  INTO v_e 
                  From REFE
                  WHERE ID = j.ID;
  
  
  
                  Select semic_concat(NAM)
                  INTO v_f 
                  FROM EDM E, EDO EO
                  WHERE E.EDMID = EO.EDOID
                  AND ID = j.ID;
  
  
  
                  Select semic_concat(VOCNAME('L08', COD))
                  INTO v_g 
                  FROM AVA 
                  WHERE ID = j.ID;
  
  
  
                 SELECT or_concat(NAM)
                 INTO v_o
                 FROM CON 
                 WHERE ID = j.ID
                 AND NAM = 'Unknown';
  
                      IF (v_o = 'Unknown')
                      THEN
  
                          Select or_concat(JOBTITLE || ' (' || EMAIL || ')')
                          INTO v_h 
                          FROM CON 
                          WHERE ID = j.ID;
  
                      ELSE
  
                          Select or_concat(NAM || ' (' || EMAIL || ')')
                          INTO v_h 
                          FROM CON 
                          WHERE ID = j.ID;
  
                      END IF;
  
  
  
                  Select commaencap_concat(COD)
                  INTO v_i 
                  FROM PAR 
                  WHERE ID = j.ID;
  
                  IF (v_i = ',')
                  THEN
  
                      v_i := null;
  
                  ELSE
  
                      Select commaencap_concat(COD)
                      INTO v_i 
                      FROM PAR 
                      WHERE ID = j.ID;
  
  
                  END IF;
  
  
  
                  Select commaencap_concat(COD)
                  INTO v_j 
                  FROM INST 
                  WHERE ID = j.ID;
  
                  IF (v_j = ',')
                  THEN
  
                      v_j := null;
  
                  ELSE
  
                      Select commaencap_concat(COD)
                      INTO v_j 
                      FROM INST
                      WHERE ID = j.ID;
  
                  END IF;
  
  
  
                  Select commaencap_concat(COD)
                      INTO v_k 
                      FROM SAR 
                      WHERE ID = j.ID;
  
                  IF (v_k = ',')
                  THEN
  
                      v_k := null;
  
                  ELSE
  
                      Select commaencap_concat(COD)
                      INTO v_k 
                      FROM SAR 
                      WHERE ID = j.ID;
  
                  END IF;
  
  
  
  
                  Select commaencap_concat(CONID)
                      INTO v_l 
                      FROM CON 
                      WHERE ID = j.ID;
  
                  IF (v_l = ',')
                  THEN
  
                      v_l := null;
  
                  ELSE
  
                      Select commaencap_concat(CONID)
                      INTO v_l 
                      FROM CON 
                      WHERE ID = j.ID;
  
                  END IF;
  
  
  
                  Select commaencap_concat(PROID)
                      INTO v_m 
                      FROM PRO 
                      WHERE ID = j.ID;
  
                  IF (v_m = ',')
                  THEN
  
                      v_m := null;
  
                  ELSE
  
                      Select commaencap_concat(PROID)
                      INTO v_m 
                      FROM PRO 
                      WHERE ID = j.ID;
  
                  END IF;
  
                   -- UPDATE DEMO TABLE
                   UPDATE DEMO
                   SET GEOC = v_a,
                       PRO = v_b,
                       PAR = v_c,
                       INS = v_d,
                       REFER = v_e,
                       ORGR = v_f,
                       AVAY = v_g,
                       CON = v_h,
                       DTH = v_i,
                       INST = v_j,
                       SA = v_k,
                       CC = v_l,
                       EDPR = v_m,
                       CTR = (SELECT NAM
                                    FROM EDM
                                    WHERE EDMID = j.CTR_ID),
                       COLL = (SELECT NAM
                                      FROM EDM
                                      WHERE EDMID = j.C_ID)
  
                  WHERE ID = j.ID;
  
            END LOOP;
  
  
          END V1;
  </code></pre>
  
  <p>/</p>
  
  <p>The aggregate functions, commaencap_concat (encapsulates with a comma), or_concat (concats with an or) and semic_concat(concats with a semi-colon).</p>
  
  <p>the remaining tables used are all linked to the main table DEMO.</p>
  
  <p>I have checked the column sizes and there seems to be no problem. I tried executing the SELECT statements alone and they give the same error without populating the tables.</p>
  
  <p>Any clues?</p>
  
  <p>Many thanks for your anticipated support.</p>
  
  <p>Thanks APC for all your help; particularly the rowtype and debug advice. I have checked all columns against each other and have made those that were less than 4000 bytes to 4000 bytes in size but I'm still getting the same error message.</p>
  
  <p>The SELECT statement I tried was with one of the aggregate functions  that is in the procedure:</p>
  
  <pre><code>            Select semic_concat(TXTDESC)
             -- INTO v_a 
              From GEOT
              WHERE ID IN (SELECT ID FROM DEMO);
  </code></pre>
  
  <p>and it raised the same error of string buffer too small; but when I added a group by to the statement it worked. </p>
  
  <pre><code>            Select semic_concat(TXTDESC)
              -- INTO v_a 
              From GEOT
              WHERE ID IN (SELECT ID FROM DEMO)
              GROUP BY ID;
  </code></pre>
  
  <p>I tried swapping all occurrences of such statements in the procedure but it still gives the same error. Thanks once again; still working on it.</p>