Re: URGENT - HELP ORA-06502:
- From: "Oracle" <Oracle_list@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Feb 2004 12:04:06 -0000
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
-----------------------------------------------------------------
Other related posts:
