Re: PL/SQL function is broken

From: <rtproffitt_at_my-deja.com>
Date: Thu, 02 Dec 1999 17:29:22 GMT
Message-ID: <826a9b$i5s$1@nnrp1.deja.com>

Sounds like you exceeded 32K in your temp variable... You could troubleshoot this by adding a counter to the loop, and if statement prior to the TEMP assignment.

> 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;

     rcnt number := 0;
> BEGIN
> OPEN C_STR(P_NUMBERPRGN);
> LOOP
> FETCH C_STR INTO STR_REC;
> EXIT WHEN C_STR%NOTFOUND;

       rcnt := rcnt + 1;


> 	  IF (TEMP IS NULL) THEN


> 	     TEMP := STR_REC.RES;


> 	  ELSE

If lengthb(temp) + lengthb(Stt_Rec.Res) > 32000 Then

   TEMP := '32000 exceeded at fetch row '||to_char(rcnt)      etc. ||' any other info you want to add....etc;     Exit;
End If;
> TEMP := TEMP||STR_REC.RES;
> END IF;
> END LOOP;
NOTE:
It was not shown, but I assume your function ends with RETURN TEMP; This way you can prove if you are exceeding the limit....

Good Luck
Robert Proffitt

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 11:29:22 CST