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 11:38:42 -0500
Message-ID: <002901c3fd50$295abf90$0704a8c0@development.perceptron.com> 





Exactly!


Use DBMS_LOB package (specifically APPEND procedure for 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: Friday, February 27, 2004 11:23 AM
To: oracle-l_at_freelists.org


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_at_dazasoftware.com>
To: <oracle-l_at_freelists.org>


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

> To: <oracle-l_at_freelists.org>

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

> -----------------------------------------------------------------

>





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 - 10:39:34 CST












Original text of this message













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