URGENT - HELP ORA-06502:

From: Oracle <Oracle_list_at_hotmail.com>
Date: Thu, 26 Feb 2004 17:12:44 -0000
Message-ID: <BAY15-DAV26lGnA6PSx00005f11@hotmail.com>

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

Received on Thu Feb 26 2004 - 11:15:41 CST