From oracle-l-bounce@freelists.org Wed Mar 3 11:11:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i23HBYl25094 for ; Wed, 3 Mar 2004 11:11:34 -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 i23HBMo25073 for ; Wed, 3 Mar 2004 11:11:23 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 832093957A8; Wed, 3 Mar 2004 12:10:13 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2004 12:08:55 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from hotmail.com (bay15-dav13.bay15.hotmail.com [65.54.184.117]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 50FA53954FD for ; Wed, 3 Mar 2004 12:08:49 -0500 (EST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 3 Mar 2004 09:12:40 -0800 Received: from 20.138.1.245 by bay15-dav13.bay15.hotmail.com with DAV; Wed, 03 Mar 2004 17:12:40 +0000 X-Originating-IP: [20.138.1.245] X-Originating-Email: [oracle_list@hotmail.com] X-Sender: oracle_list@hotmail.com From: "Oracle" To: References: <001701c3fd33$ec60c470$0704a8c0@development.perceptron.com> Subject: Re: URGENT - HELP ORA-06502: Date: Wed, 3 Mar 2004 17:12:01 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 Message-ID: X-OriginalArrivalTime: 03 Mar 2004 17:12:40.0897 (UTC) FILETIME=[BC211310:01C40142] X-archive-position: 193 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Oracle_list@hotmail.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l I created this function: FUNCTION Fmemo_text (p_mtsn IN NUMBER) return CLOB is l_str CLOB; BEGIN Dbms_Lob.createtemporary(l_str,TRUE,Dbms_Lob.Call); 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 Dbms_Lob.WriteAppend(l_str,Length(x.memo_text),x.memo_text); END LOOP; RETURN l_str; END; However when i tried this insert into table concat_memo_text (mtsn NUMBER, memo_text CLOB) i get an error. INSERT INTO CONCAT_MEMO_TEXT (mtsn,memo_text) (SELECT mtsn,fmemo_text( mtsn ) MEMO_TEXT FROM nc_memo_text GROUP BY mtsn); ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYS.DBMS_LOB", line 722 Any ideas why?? ----- Original Message ----- From: "Igor Neyman" To: Sent: Friday, February 27, 2004 1:16 PM Subject: RE: URGENT - HELP ORA-06502: > "||" operator does not work with CLOB. > > 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: Friday, February 27, 2004 7:04 AM > To: oracle-l@freelists.org > Subject: Re: URGENT - HELP ORA-06502: > > The query returned a record which has length of 9612. This could vary > depending on when the query is run . > > So i changed the concat_memo_text.memo_text to CLOB. > And changed the function to use CLOB also. > > SQL> CREATE OR REPLACE FUNCTION Fmemo_text (p_mtsn IN NUMBER) > 2 return CLOB is > 3 l_str CLOB; > 4 BEGIN > 5 FOR x IN > 6 (SELECT REPLACE(REPLACE(MEMO_TEXT,chr(13),'Z'),CHR(10),'') AS > MEMO_TEXT > 7 FROM NC_MEMO_TEXT > 8 WHERE MTSN = p_mtsn > 9 ORDER BY TEXT_ORDER) > 10 LOOP > 11 l_str := l_str||''||x.memo_text; > 12 END LOOP; > 13 RETURN l_str; > 14 END; > 15 / > > However, i get error: > > 11/18 PLS-00306: wrong number or types of arguments in call to '||' > > Have i missed something?? > > > ----- Original Message ----- > From: "Igor Neyman" > To: > Sent: Thursday, February 26, 2004 6:19 PM > Subject: 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@perceptron.com > > > > > > > > -----Original Message----- > > From: oracle-l-bounce@freelists.org > > [mailto:oracle-l-bounce@freelists.org] On Behalf Of Powell, Mark D > > Sent: Thursday, February 26, 2004 12:45 PM > > To: 'oracle-l@freelists.org' > > 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@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 > > ----------------------------------------------------------------- > > > > > > ---------------------------------------------------------------- > > 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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------