| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: URGENT - HELP ORA-06502:
Take a look at the DBMS_LOB package for building a large CLOB data type - it
provides what you need to build a large CLOB variable that you can then
return via your function.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: Oracle [mailto:Oracle_list_at_hotmail.com]
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;
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
> -----------------------------------------------------------------
>
-- 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:41:45 CST
 |
 |