• Register
0 votes
310 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

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 43 views
43 views
Problem : I am newly installing the Oracle XE database on my Ubuntu by following below steps: Downloaded the latest rpm file for the Oracle XE and ran below commands to install my database: sudo apt-get install alien alien oracle-xe-11.2.0-1.0.x86_64.rpm ... to start my database then I am facing below error “Ora-00205: error in identifying control file, check alert log for more info”
asked Jan 29 jwilliam 3.9k points
1 vote
1 answer 99 views
99 views
Problem : I am learning Oracle and trying to create and drop tables. If I try to drop my table it is always showing me below error: DROP TABLE SUBURB; DROP TABLE CITY; DROP TABLE STATE; Error at line 1: ORA-02449: unique/primary keys in table referenced ... already used by an existing object. I am unable to understand the above errors. Can someone guide me in fixing above Oracle related errors?
asked May 12 Martin K 6.6k points