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

--
http://www.freelists.org/webpage/oracle-l