Sounds like bad or unexpected data in the table.
<mitch23_at_hotmail.com> wrote in message news:825vr5$9qk$1_at_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:52:03 CST
