0

I have an oracle procedure which appends varchar2:

var1 varchar2(32767);

if nvl(length(var1),0) + length(var2||var3||var2||chr(10)||var4||chr(10)||chr(10)) < 32767 
then  var1:= var1||var2||var3||var2||chr(10)||var4||chr(10)||chr(10);
end if;

While I'm doing debug I can confirm that:

lengh(var1||var2||var3||var2||chr(10)||var4||chr(10)||chr(10))

is lower than 32767. But, when I try to execute line 3, it gives me an error:

"numeric or value error: character string buffer too small"

Can anyone help me here?

ajmalmhd04
  • 2,582
  • 6
  • 23
  • 41
user1051434
  • 167
  • 1
  • 5
  • 17

1 Answers1

2

What is your character set?

LENGTH function returns count of characters, not bytes. If you want to check the length of a string in bytes, use the LENGTHB function.

The problem here may be the fact that you store characters that take up more than 1 byte. The 32767 is the bytes limit for a VARCHAR2 (NVARCHAR2 too) string, not characters. So, after concatenation, your string may have less characters than 32767, but it may use more bytes to represent all of them, and you get the buffer too small error.

Read more:

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41