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" <ineyman@xxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx > > > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Oracle > Sent: Friday, February 27, 2004 7:04 AM > To: oracle-l@xxxxxxxxxxxxx > 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" <ineyman@xxxxxxxxxxxxxx> > To: <oracle-l@xxxxxxxxxxxxx> > 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@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 //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 > > ----------------------------------------------------------------- > > > > > > ---------------------------------------------------------------- > > 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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------