RE: URGENT - HELP ORA-06502:

You could be right, but: 

Select MTSN, sum(length(MEMO_TEXT))
        From NC_MEMO_TEXT  
        Group by MTSN;

Should tell, if "l_str" is big enough to hold the result of
concatenation.  

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Powell, Mark D
Sent: Thursday, February 26, 2004 12:45 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: URGENT - HELP ORA-06502:

Actually could it be that while pl/sql supports a varchar2 being up to
32K
in size that the calling environment only supports a varchar2 being as
large
as the database supports: 4,000 bytes?

How large is the string returned by Fmemo_text?  Can you test with a
value
that will return under 2K and see if the error still occurs or if the
function works?


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Igor Neyman
Sent: Thursday, February 26, 2004 12:39 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: URGENT - HELP ORA-06502:


What error says, is that l_str variable in FUNCTION Fmemo_text is too
small to hold the result of concatenation.

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Oracle
Sent: Thursday, February 26, 2004 12:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: URGENT - HELP ORA-06502:

Hi,

I have the following 2 Tables:

CONCAT_MEMO_TEXT -
MTSN NUMBER(10)
MEMO_TEXT VARCHAR2(4000)

NC_MEMO_TEXT  -
MTSN                                    NUMBER(10)
TEXT_ORDER                      NUMBER(10)
MEMO_TEXT                       VARCHAR2(255)
R_STATUS                            VARCHAR2(1)

This function:

CREATE OR REPLACE FUNCTION Fmemo_text (p_mtsn IN NUMBER)
 return VARCHAR2 is
 l_str  VARCHAR2(10000);
BEGIN
  FOR x IN
  (SELECT REPLACE(REPLACE(MEMO_TEXT,chr(13),'Z'),CHR(10),'') AS
MEMO_TEXT
   FROM  NC_MEMO_TEXT
   WHERE  MTSN = p_mtsn
   ORDER  BY TEXT_ORDER)
    LOOP
        l_str := l_str||''||x.memo_text;
    END LOOP;
   RETURN l_str;
END;
/

I am trying this insert statement:

INSERT INTO CONCAT_MEMO_TEXT (mtsn,memo_text)
       (SELECT  mtsn,fmemo_text( mtsn ) MEMO_TEXT
         FROM nc_memo_text
        GROUP BY mtsn);

However i get this error:

ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small
ORA-06512: at line 1

I have tried various settings for l_str in the finction however i keep
getting this error.

I have also tried changing the datatype of CONCAT_MEMO_TEXT .MEMO_TEXT
to
Long, clob etc but no luck.

Anyone got any ideas.

Thanks


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: