Exactly! Use DBMS_LOB package (specifically APPEND procedure for concatenation). 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 11:23 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: URGENT - HELP ORA-06502: This has the same result - 'PLS-00306: wrong number or types of arguments in call to 'concat' ----- Original Message ----- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Friday, February 27, 2004 1:20 PM Subject: Re: URGENT - HELP ORA-06502: > Did you tried concat > declare > > a clob := 'a'; > > b clob := 'sb'; > > begin > > dbms_output.put_line( concat(a,b) ); > > end; > > ----- Original Message ----- > From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx> > To: <oracle-l@xxxxxxxxxxxxx> > Sent: Friday, February 27, 2004 9:16 AM > 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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------