Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: URGENT - HELP ORA-06502:

Re: URGENT - HELP ORA-06502:

From: Oracle <Oracle_list_at_hotmail.com>
Date: Wed, 3 Mar 2004 17:12:01 -0000
Message-ID: <BAY15-DAV13a8OSwkUB00010be2@hotmail.com> 





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??






> "||" operator does not work with CLOB.

>

> Igor Neyman, OCP DBA

> ineyman_at_perceptron.com

>

>

>

> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Oracle

> Sent: Friday, February 27, 2004 7:04 AM

> To: oracle-l_at_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" <ineyman_at_perceptron.com>

> To: <oracle-l_at_freelists.org>

> 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_at_perceptron.com

> >

> >

> >

> > -----Original Message-----

> > From: oracle-l-bounce_at_freelists.org

> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D

> > Sent: Thursday, February 26, 2004 12:45 PM

> > To: 'oracle-l_at_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_at_freelists.org

> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Igor Neyman

> > Sent: Thursday, February 26, 2004 12:39 PM

> > To: oracle-l_at_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_at_perceptron.com

> >

> >

> >

> > -----Original Message-----

> > From: oracle-l-bounce_at_freelists.org

> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Oracle

> > Sent: Thursday, February 26, 2004 12:13 PM

> > To: oracle-l_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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
-----------------------------------------------------------------


Received on Wed Mar 03 2004 - 11:11:34 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US