ORA-06502: PL/SQL: numeric or value error: character string buffer too small

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Thu, 30 Jul 2009 11:19:15 -0500
Message-ID: <DC55FA7F-6151-46E1-850F-BD089D91FC34_at_multiservice.com>

(On 11.1.0.7)

How's this for weird ...

SQL> select fw_phrase_common_p.normalise_phrase('�ber') from dual; select fw_phrase_common_p.normalise_phrase('�ber') from dual

        *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MSMARINE.FW_PHRASE_COMMON_P", line 1015

SQL> alter package fw_phrase_common_p compile body;

Package body altered.

SQL> select fw_phrase_common_p.normalise_phrase('�ber') from dual;

FW_PHRASE_COMMON_P.NORMALISE_PHRASE('�BER')

---

�BER We have NLS_LENGTH_SEMANTICS set to CHAR at the DB level.

It seems that it is somehow losing the nls_char_semantics setting of the package. The statement it was failing on is :

:
FUNCTION normalise_phrase(i_text IN VARCHAR2) RETURN VARCHAR2 AS
--

     l_ret           VARCHAR2(2000) := NULL;
     l_wds           PLS_INTEGER := 0;
     l_iswhite       BOOLEAN := TRUE;
     l_ch            CHAR(1);

--

BEGIN

     FOR i IN 1 .. LENGTH(i_text) LOOP
         l_ch := UPPER(SUBSTR(i_text, i, 1));    -- <-- Line 1015

Any ideas ???

Steve

---

This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 30 2009 - 11:19:15 CDT