URGENT - HELP ORA-06502:
- From: "Oracle" <Oracle_list@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Feb 2004 17:12:44 -0000
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
-----------------------------------------------------------------
Other related posts:
