Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- From: David Barbour <david.barbour1@xxxxxxxxx>
- To: stbaldwin@xxxxxxxxxxxxxxxx
- Date: Thu, 30 Jul 2009 13:49:26 -0400
Does it fail again after you run it? At what point is it breaking? I
wonder if there is a session somewhere running the package with settings
that override the defaults and the package gets cached with those settings?
On Thu, Jul 30, 2009 at 1:06 PM, Steve Baldwin
<stbaldwin@xxxxxxxxxxxxxxxx>wrote:
> 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 :
>>>
>>
>>
>> --
>> //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.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
