Re: 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 12:06:12 -0500
Message-ID: <A2594728-AD00-4D5F-81C8-F5A84832EE5A_at_multiservice.com>

Thanks for the reply Rich. I should have mentioned that the DB char set is AMERICAN_AMERICA.AL32UTF8.

By setting NLS_LENGTH_SEMANTICS, we're telling Oracle that a declaration such as CHAR(1) is to be interpreted as CHAR(1 CHAR) nor CHAR(1 BYTE). However, for some reason, the package seems to be confused as it fails assigning a wide character to a CHAR(1). The package is not marked as invalid (or I would have got a different error the first time I ran it) so recompiling should do nothing, but lo and behold after recompiling the body, it works as expected.

That's what I find weird.

Steve

On 30/07/2009, at 11:38 AM, Rich Jesse wrote:

> Hey Steve,
>
> If you're worried about CHAR vs BYTE, is there a reason you're not
> using
> NCHAR/NVARCHAR2? Also, I'd think passing your static parameter as
> (N'Über')
> would also be ideal to help prevent casting.
>
> Just a thought...
>
> Rich
>
>> 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 :
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

---

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 - 12:06:12 CDT