aspose file tools*
The moose likes JDBC and the fly likes ORA-06502: PL/SQL: numeric or value error: character string buffer too small Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" Watch "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" New topic
Author

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

Vinod Vijay
Ranch Hand

Joined: Sep 13, 2011
Posts: 119

Hi, I have modified a working PL SQL code by adding some condition(Please see the code below, I have modified where it says //FROM HERE to //TO TILL HERE (both 1 and 2 parts). Now it is not working and I'm getting the following error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried a lot in figuring out and spent many hours in it but still didn't find any solution. And finally posting it here. Please help me in rectifying this issue.
Please note that I have removed some unwanted code in between which I didn't touched at all.

Warranty_Exp_Ren_Process.sql :-


Vinod Vijay Nair
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3450
    
  47

Oracle usually produces stack trace for PL/SQL errors. There will be a line number in the stack trace specifying the point where the error has occurred, this might help you to pinpoint the error. If you still cannot figure it out, post the stacktrace here.

As a rough guess, your usage of the lv_err_msg variable seems strange to me. You append some text to this variable in every iteration of the loop you've added; if the loop is executed lots of times, the resulting text might exceed the declared length of that variable. There might be other problems though.
Vinod Vijay
Ranch Hand

Joined: Sep 13, 2011
Posts: 119

Martin Vajsar wrote:Oracle usually produces stack trace for PL/SQL errors. There will be a line number in the stack trace specifying the point where the error has occurred, this might help you to pinpoint the error. If you still cannot figure it out, post the stacktrace here.

As a rough guess, your usage of the lv_err_msg variable seems strange to me. You append some text to this variable in every iteration of the loop you've added; if the loop is executed lots of times, the resulting text might exceed the declared length of that variable. There might be other problems though.


Thanks Martin, I think this could be the root cause of this issue. I will verify it and update you.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

Side issue: If you have PL/SQL variables that you plan to use for database values, use anchored datatypes (%ROWTYPE or %TYPE) instead of an explicit type/length e.g. lv_customer_name customer_table.customer_name%TYPE. This means (1) you can be sure any value you fetch into the variable will fit in it OK, and (2) if the column length changes you don't have to search through all your code to find the places where your code will break.

Also, for things like messages etc you might as well make your variables big enough for any reasonable value e.g. VARCHAR2(2000) or VARCHAR2(4000) - see the PL/SQL data types reference for your version of Oracle:

Oracle 10g:

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

Oracle 11g:

Memory Allocation
For a CHAR variable, PL/SQL allocates at compile time enough memory for the maximum size.

For a VARCHAR2 variable, memory allocation depends on maximum size:

If the maximum size is less than 4,000 bytes, PL/SQL allocates at compile time enough memory for the maximum size.

If the maximum size is 4,000 bytes or more, PL/SQL allocates at run time enough memory for the actual value.

For example, suppose that variables a and b are declared as follows:

a VARCHAR2(3999);
b VARCHAR2(4000);
If you assign the same 500-byte value to both variables, PL/SQL allocates 3,999 bytes for a at compile time and 500 bytes for b at run time.

Thus, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
 
Similar Threads
J2ee app gets stuck at a stored procedure call
java.sql.SQLException: ORA-06576: not a valid function or procedure name
Testing a simple Oracle PL SQL function using toad sql client?
Stored procedure which returns array
Calling stored procedure problems