• Register
0 votes
525 views

Problem :

I tried my Oracle code in different ways, like by taking out a while loop or a if loop, but when I put both loops together (if and while), I always face below error at the end.

ERROR:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
06502. 00000 -  "PL/SQL: numeric or value error%s"

 

7 5 2
3,870 points

Please log in or register to answer this question.

2 Answers

0 votes

Solution :

“PL/SQL: numeric or value error: character string buffer too small”

This error is due to a fact that you declare the string to be of the fixed length (say 30), and at some point in the code you assign it the value whose length exceeds what you have declared.

For example below code:

myOraString VARCHAR2(20);
myOraString :='abcdefghijklmnopqrstuvwxyz'; --length is 26

Will give the above error.

OR

CHAR is the always fixed-length data type that uses a as much space as possible. So a:= a||'one '; will require more space than it is available. So your problem can be fixed by the below example:

declare
  v_boo char(50);
begin
  v_boo := 'A';
  dbms_output.put_line('length of v_boo(A) = ' || length(v_boo));
  -- next line will raise your error:
  -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  v_boo := v_boo || 'B';
  dbms_output.put_line('length of v_boo(AB) = ' || length(v_boo));  
end;
/

So please never use the char.

9 7 4
38,600 points
0 votes

Solution:

PL/SQL: numeric or value error: character string buffer too small because of the fact that you declare a string to be of a fixed length (say 20), and at some point in your code you assign it a value whose

length overstep what you declared.

for example:

myString VARCHAR2(20);
myString :='abcdefghijklmnopqrstuvwxyz'; --length 26

will fire such an error

CHAR is a fixed-length data type that uses as much space as feasible. Hence a:= a||'one '; will need more space than is available. Your issue can be diminished to the following example:

declare
  v_foo char(50);
begin
  v_foo := 'A';
  dbms_output.put_line('length of v_foo(A) = ' || length(v_foo));
  -- next line will raise:
  -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  v_foo := v_foo || 'B';
  dbms_output.put_line('length of v_foo(AB) = ' || length(v_foo));  
end;
/

Never use char. For gradual check the following question (read also the links):

ariable v_field_A cannot hold value more than 100 characters

It is very much possible because you are concatenating the variable for each row in the CURSOR FOR LOOP.

For example:

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      v_name := v_name || i.ename;
  9    END LOOP;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

Employ DBMS_OUTPUT to view the present size of the variable and the new value being appended.

Let's debug

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      dbms_output.put_line('Length of new value = '||LENGTH(i.ename));
  9      v_name := v_name || i.ename;
 10      dbms_output.put_line('Length of variable = '||LENGTH(v_name));
 11    END LOOP;
 12  END;
 13  /
Length of new value = 5
Length of variable = 5
Length of new value = 5
Length of variable = 10
Length of new value = 4
Length of variable = 14
Length of new value = 5
Length of variable = 19
Length of new value = 6
Length of variable = 25
Length of new value = 5
Length of variable = 30
Length of new value = 5
Length of variable = 35
Length of new value = 5
Length of variable = 40
Length of new value = 4
Length of variable = 44
Length of new value = 6
Length of variable = 50
Length of new value = 5

Error

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9

It is pretty clear, we wanted to concatenate a string with length 5 to the variable declared as max size 50, at present occupying a value of size 50. Therefore, it throws the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

Increment the length of the variable v_variable1.

Example:

Declare
v_variable1 varchar(6);
BEGIN
select '123456' into v_variable1  from dual;
END;
/

PL/SQL procedure successfully completed.

Increment the limit of VAR variable to 2000, it will fixed the problem.
Maximum limit of VARCHAR2 is 32767.

set serveroutput on
DECLARE
var varchar2(2000); -- Size is 2000 bytes
var1 number;
CURSOR cn IS SELECT object_name FROM dba_objects where rownum NULL);
FOR v_objectname IN cn
LOOP
var1 := 1+var1;
var := var || v_objectname.object_name;
dbms_output.put_line(var1);
--Dbms_output.put_line(var);
END LOOP;
END;
/

 

10 6 4
31,120 points

Related questions

0 votes
1 answer 1 view
1 view
Problem: what is the best way to get RESULTROW populated, minding performance and the lesser queries as possible? Thanks in advance for any help/thoughts!
asked Apr 3 ummesalma 25.2k points
0 votes
1 answer 2 views
2 views
Problem: Get the above error when the execute immediate is called in a loop ora-01747: invalid user.table.column, table.column, or column specification
asked Apr 3 ummesalma 25.2k points
0 votes
1 answer 1 view
1 view
Problem: I am planning to install the UTL_MAIL Package and I’m currently testing the installation steps in my Development Environment. After sucessfully installing the UTL_MAIL Package Scripts and creating the sufficient PUBLIC Synonyms and Grants, I am getting the error ORA-29278 when running the test Anonymous Block. How do I solve this?
asked 3 days ago Atik03 10.1k points
0 votes
1 answer 4 views
4 views
Problem: I get to keep getting this error I can't figure out what is wrong. DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 11
asked Apr 3 ummesalma 25.2k points
0 votes
1 answer 4 views
4 views
Problem: When I try to execute the above code to save the function to the database, the environment (SQL*PLUS) hangs for a long time and at the end returns this error: ERROR at line 1: ORA-04021: timeout occurred while waiting to lock object What is the problem ??? Please!
asked Apr 3 ummesalma 25.2k points
0 votes
1 answer 8 views
8 views
Problem: Hope someone can spot what I'm doing wrong as I'm going bald from this. I have used utl_http & wallets to call https on 11gR1 without much trouble, but our new 12c installation is causing me a lot of grief.
asked Apr 2 ummesalma 25.2k points
0 votes
1 answer 3 views
3 views
Problem: I have a query and it returns the error ORA-00972 identifier is too long, is there any tip to make it work without making the alias shorter? Thanks
asked Apr 3 ummesalma 25.2k points
0 votes
1 answer 1 view
1 view
Problem I want DDLs of Tables so that I can put it to the database server using UTL Package in Oracle. Would be glad to get advise.
asked 17 hours ago anika11 5.1k points
0 votes
1 answer 1 view
1 view
Problem: This question raised a very interesting point; there seems to be a contradiction in the Oracle documentation on whether it's possible for %NOTFOUND to be null after a fetch. Is it?
asked 4 days ago ummesalma 25.2k points