PL/SQL function is broken

From: <mitch23_at_hotmail.com>
Date: Thu, 02 Dec 1999 14:31:13 GMT
Message-ID: <825vr5$9qk$1@nnrp1.deja.com>

I'm don't much about Oracle PL/SQL, so when I needed to build a function that concatenated several rows of VARCHAR data into one row, I posted to this newsgroup. Someone was kind enough to give me the attached code.

---

CREATE OR REPLACE FUNCTION CONCAT_RES(P_NUMBERPRGN IN VARCHAR2) RETURN VARCHAR2
AS

   CURSOR C_STR(IN_NUMBERPRGN IN VARCHAR2) IS    SELECT RESOLUTION RES
   FROM HFS_PROBLEM_RESOLUTION
   WHERE NUMBERPRGN = IN_NUMBERPRGN ORDER BY RECORD_NUMBER; --

   STR_REC C_STR%ROWTYPE;
   TEMP VARCHAR2(32000):=NULL;
BEGIN
   OPEN C_STR(P_NUMBERPRGN);
   LOOP

      FETCH C_STR INTO STR_REC;
      EXIT WHEN C_STR%NOTFOUND;
	  IF (TEMP IS NULL) THEN
	     TEMP := STR_REC.RES;
	  ELSE
	     TEMP := TEMP||STR_REC.RES;
	 END IF;

   END LOOP;

---

SELECT NUMBERPRGN, CONCAT_RES(NUMBERPRGN) RES FROM HFS_PROBLEM_RESOLUTION GROUP BY NUMBERPRGN

---

It worked great up until about a week ago, now I get the following error

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DHFSRPT.CONCAT_RES", line 11
ORA-06512: at line 1

There are about 45000 rows in the table...could the function variables be running out of space? Thanks for any insights.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 08:31:13 CST