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: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 27 Feb 2004 08:16:34 -0500
Message-ID: <001701c3fd33$ec60c470$0704a8c0@development.perceptron.com> 





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







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


Received on Fri Feb 27 2004 - 07:13:33 CST












Original text of this message













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