Skip to main content

Hey all,

 

*** THIS ANSWERED THE ISSUE ***:

 

 

I must have missed that both a year ago and this morning before I posted. Apologies! I switched the NLS Length setting in SQL Developer preferences to CHAR, recompiled, and the API package works fine now!

 

 

 

ORIGINAL POST...

A strange error about a buffer being too small has happened again (happened a little over a year ago) in the INVENTORY_PART_IN_STOCK_API package.

I am needing to recompile that package for debug, and as soon as I do (no code changes whatsoever, just a recompile), any call to that package results in the error:

 

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

ORA-06512: at "BELL1APP.INVENTORY_PART_IN_STOCK_API", line 73

 

All parts of this API worked flawlessly up until now. Line 73 of the package body is this:

last_character_    CONSTANT VARCHAR2(1)  := Database_SYS.Get_Last_Character;

When I saw that line I recalled this happening before. I changed the size of the VARCHAR2 to 2 – still errors. Upped it to 3, all errors stop and the package functions correctly. So now the line looks like this, and everything works:

last_character_    CONSTANT VARCHAR2(2)  := Database_SYS.Get_Last_Character;

As I said, this happened a little over a year ago -- exact same thing in the same package. Never was able to figure out what happened. Our DEV environment (where this is happening) has been refreshed from PROD several times since then, so it must be the recompile that puts the package into a non-working state.

Can anyone shed some light on this? Appreciate any and all help!

 

Thanks,

Joe Kaufman

Hi @sutekh137,

If you are using tools such as Toad or Oracle SQL Developer or PLSQL Developer, some of those tools have the NLS_LENGTH_SEMANTICS parameter set to BYTE so that all the database sessions opened from that tool would have this parameter set to BYTE as well. When you compile database objects with such a session, the objects would compile as BYTE, instead of CHAR. We have seen it cause the “ORA-06502: PL/SQL: numeric or value error: character string buffer too small” error when the database objects are compiled as BYTE instead of CHAR. 

Please have a look at the response marked as answer in this post and see if the suggested steps resolve your issue:

Hope this helps!

Hi @sutekh137,

If you are using tools such as Toad or Oracle SQL Developer or PLSQL Developer, some of those tools have the NLS_LENGTH_SEMANTICS parameter set to BYTE so that all the database sessions opened from that tool would have this parameter set to BYTE as well. When you compile database objects with such a session, the objects would compile as BYTE, instead of CHAR. We have seen it cause the “ORA-06502: PL/SQL: numeric or value error: character string buffer too small” error when the database objects are compiled as BYTE instead of CHAR. 

Please have a look at the response marked as answer in this post and see if the suggested steps resolve your issue:

Hope this helps!

 

Charith, thanks, yes, I found that thread and another after I posted, so sorry to have wasted your time! Thanks for the response!

 

Joe Kaufman

Reply


Terms & ConditionsCookie settings