• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

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

 
Ranch Hand
Posts: 165
Tomcat Server Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 :-
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 165
Tomcat Server Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic