How to clear a variable's contents

From: <mitch23_at_hotmail.com>
Date: Thu, 16 Sep 1999 18:17:18 GMT
Message-ID: <7rrc71$njq$1_at_nnrp1.deja.com>

Thanks to the help of one of the posters here, I have a PL/SQL function which concantenates several records into one output variable.

My problem is that the return variable in the function is running out of space before
reading all the records in the table I'm querying (It returns an ORA- 06512 error). I knnow that this is the case  because when I re-create the function and increase the size of the variable declaration
(from TEMP VARCHAR2(2000):=NULL; to TEMP VARCHAR2
(10000):=NULL;) my query then works.

Is there a way to clear our (reset) a variable after each loop in my fucntion so that it doesn't run of space? I could just declare a huge variable (VARCHAR2 1000000):=NULL;) but this seems ineffiecient.

The function and SQL query are below.
Thanks for any help.

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

   CURSOR C_STR(IN_NUMBERPRGN IN VARCHAR2) IS    SELECT ACTION ACT
   FROM HFS_PROBLEM_ACTION
   WHERE NUMBERPRGN = IN_NUMBERPRGN;

--
   STR_ACT C_STR%ROWTYPE;
      TEMP VARCHAR2(2000):=NULL;
BEGIN

   OPEN C_STR(P_NUMBERPRGN);
   LOOP

      FETCH C_STR INTO STR_ACT;
      EXIT WHEN C_STR%NOTFOUND;
	  IF (TEMP IS NULL) THEN
	     TEMP := STR_ACT.ACT;
	  ELSE
	     TEMP := TEMP||' '||STR_ACT.ACT;
	 END IF;
   END LOOP;
--
   RETURN TEMP;
END;



SELECT NUMBERPRGN, CONCAT_ACT(NUMBERPRGN) FROM HFS_PROBLEM_ACTION GROUP
BY NUMBERPRGN


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

Received on Thu Sep 16 1999 - 20:17:18 CEST