• Register
0 votes
400 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 60 views
60 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, 2020 jwilliam 3.9k points
1 vote
1 answer 178 views
178 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, 2020 Martin K 6.6k points
0 votes
1 answer 15 views
1 vote
1 answer 97 views
97 views
Problem: I am trying to learn Oracle. I have created two tables their names are Table Employee and Table USER, here is the structure. CREATE TABLE Employee ( ID NUMBER NOT NULL , USERID NUMBER, CONSTRAINT B_PK PRIMARY KEY ( ID ) ENABLE ); CREATE TABLE USER ( ... above error but because of my elementary knowledge on Oracle I am unable to fix it. Kindly guide me in fixing above Oracle error.
asked May 29, 2020 Martin K 6.6k points
1 vote
1 answer 29 views
29 views
Problem: Let me put it as sim as I can. I have an if statement in my code and it returns a value. Since IF is a boolean statement it should return value whether it’s true or false. So, in my program when a method tests an argument and returns a true or ... as a parameter in a different method inside an IF statement. How should I do that? Any kind of help is appreciated by my side. Thanks.
asked Mar 17, 2020 Gavin 15.3k points
0 votes
1 answer 21 views
21 views
Problem: I have good knowledge of Oracle. Currently I am trying my hand on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production and also I am trying to use the ftp package (UTL_TCP). Here is the procedure which I have written: declare     l_conn ... setup of a ACL as mentioned by them but still I am unable to figure it out why my network access is denied after the login.
asked Aug 15, 2020 Raphael Pacheco 4.9k points
1 vote
1 answer 47 views
47 views
Problem : I am very new to Oracle. I can very clearly see from the SQLPLUS startup output that my database is not starting up because in my init.ora file local_listener is setup as the LISTENER and now it says that it is unable to resolve that name. ... figure out my mistake. I need Oracle experts help to fix my bellow error: Ora 00119 invalid specification for system parameter local_listener .
asked Apr 22, 2020 stewart 4k points
0 votes
1 answer 12 views
12 views
String or binary data would be truncated SQL Server When I tried to add data to a newly created column using SQL Server Management Studio, I got the error above. I need help to solve the problem.
asked Dec 1, 2020 miki 1.9k points