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

So far (after trying it several hundred times), after the recompile, all is good. As far as I'm aware, no code we have does any explicit mucking with NLS params. I guess it is possible someone has a funky GUI tool or some such thing. That's a good suggestion. I'll look into it. 

Cheers,

Steve

On 30/07/2009, at 12:49 PM, David Barbour wrote:
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 :


--
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






------------------------
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.

Other related posts: