Good day gurus,

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.

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.

please; kindly find attached the part of the script I've developed into a procedure that I reckon gives the error.

CREATE OR REPLACE PROCEDURE V1 IS

--DECLARE

    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);

--
-- Procedure that populates DEMO table

        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 > 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;

/

The aggregate functions, commaencap_concat (encapsulates with a comma), or_concat (concats with an or) and semic_concat(concats with a semi-colon).

the remaining tables used are all linked to the main table DEMO.

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.

Any clues?

Many thanks for your anticipated support.