Cursor variable is running out of space...

From: <mitch23_at_hotmail.com>
Date: Mon, 06 Dec 1999 16:04:17 GMT
Message-ID: <82gmpu$h6g$1@nnrp1.deja.com>

The following PL/SQL function appears to be crapping out because the TEMP variable is running out of space:

---

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

   CURSOR C_STR(IN_NUMBERPRGN IN VARCHAR2) IS    SELECT ACTION1 LOG FROM HFS_PROBLEM_ACTION    WHERE NUMBERPRGN = IN_NUMBERPRGN ORDER BY RECORD_NUMBER;
--

   STR_REC C_STR%ROWTYPE;

      TEMP VARCHAR2(32000):=NULL;
	RCNT NUMBER := 0;

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.LOG;
	  ELSE
	     TEMP := TEMP||STR_REC.LOG;
	 END IF;

   END LOOP;
--

   RETURN TEMP;
END;
/

---

Is there a way to clear out TEMP if it runs out of space or to initialize to a larger value? The table it references is pretty huge and is only likely to grow unfortunately.

Thanks

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 06 1999 - 10:04:17 CST