Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL function is broken

Re: PL/SQL function is broken

From: Alan Shein <alanshein_at_erols.com>
Date: Thu, 2 Dec 1999 09:52:03 -0500
Message-ID: <826124$ao8$1@autumn.news.rcn.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US