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.
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.
The SELECT statement I tried was with one of the aggregate functions that is in the procedure:
Select semic_concat(TXTDESC)
-- INTO v_a
From GEOT
WHERE ID IN (SELECT ID FROM DEMO);
and it raised the same error of string buffer too small; but when I added a group by to the statement it worked.
Select semic_concat(TXTDESC)
-- INTO v_a
From GEOT
WHERE ID IN (SELECT ID FROM DEMO)
GROUP BY ID;
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.