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 //www.freelists.org/archives/oracle-l/ FAQ is at //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 //www.freelists.org/archives/oracle-l/ FAQ is at //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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------