Need Help with string output from function or Proc

From: <eclipse98_at_hotmail.com>
Date: Thu, 30 Sep 1999 18:32:40 GMT
Message-ID: <7t0ac8$dpv$1@nnrp1.deja.com>

Hi All !!!

I would appreciate if anybody point me to the right direction with this problem.

I need to generate a string of concatenated values from a table and return it either as function or output parameter in Proc. In function below I loop through cursor and append field value to vTEMP variable (no problem here). However when I try to return it it gives me this error:

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

When I limit the string length to 1999 chars long it works just fine, but I need to return much longer string (50,000+). I tried to return long instead of varchar2, but get the same error.

Is there any other datatype I can use or is there some workaround? I know there is LOB in Oracle8, but I am using 7.

TIA, Davie (using Oracle7 Server Release 7.3.4.1.0).

---

CREATE OR REPLACE FUNCTION F_TEST
RETURN VARCHAR2
IS

       CURSOR MYCURSOR IS
	   SELECT G.FIRST_NAME, GP.PHONE_NO
	   FROM GUEST G, GUEST_PHONE GP
	   WHERE G.GUEST_ID = GP.GUEST_ID AND
	   G.FIRST_NAME is not null;
	   vTEMP varchar2(32000);
BEGIN
	 vTEMP := 'A';
	 FOR MYREC IN MYCURSOR LOOP
		IF LENGTH(vTEMP) < 1999 THEN
			vTEMP := vTEMP || MYREC.FIRST_NAME;
		 END IF;
	 END LOOP;
	 RETURN vTEMP;

END;
/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Sep 30 1999 - 13:32:40 CDT