From oracle-l-bounce@freelists.org Thu Feb 26 11:41:31 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1QHfV213702 for ; Thu, 26 Feb 2004 11:41:31 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i1QHfVo13697 for ; Thu, 26 Feb 2004 11:41:31 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 21B46395A4F; Thu, 26 Feb 2004 12:42:50 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 26 Feb 2004 12:41:48 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from ahmler4.mail.eds.com (ahmler4.mail.eds.com [192.85.154.77]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 65FE63954E0 for ; Thu, 26 Feb 2004 12:41:39 -0500 (EST) Received: from ahmlir3.mail.eds.com (ahmlir3-2.mail.eds.com [192.85.154.133]) by ahmler4.mail.eds.com (8.12.10/8.12.9) with ESMTP id i1QHiZMi017018 for ; Thu, 26 Feb 2004 12:44:35 -0500 Received: from ahmlir3.mail.eds.com (localhost [127.0.0.1]) by ahmlir3.mail.eds.com (8.12.10/8.12.10) with ESMTP id i1QHiZ77024302 for ; Thu, 26 Feb 2004 12:44:35 -0500 Received: from USAHM102.amer.corp.eds.com ([207.37.138.190]) by ahmlir3.mail.eds.com (8.12.10/8.12.10) with ESMTP id i1QHiZP1024295 for ; Thu, 26 Feb 2004 12:44:35 -0500 Received: by USAHM102.amer.corp.eds.com with Internet Mail Service (5.5.2657.72) id <19SZL26N>; Thu, 26 Feb 2004 12:44:34 -0500 Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA1A@USAHM018.amer.corp.eds.com> From: "Powell, Mark D" To: "'oracle-l@freelists.org'" Subject: RE: URGENT - HELP ORA-06502: Date: Thu, 26 Feb 2004 12:44:34 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain X-archive-position: 2118 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mark.powell@eds.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Igor Neyman Sent: Thursday, February 26, 2004 12:39 PM To: oracle-l@freelists.org 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@perceptron.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Oracle Sent: Thursday, February 26, 2004 12:13 PM To: oracle-l@freelists.org 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@freelists.org 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@freelists.org 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@freelists.org 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 -----------------------------------------------------------------