CODEWITHSUNDEEP
oracle-databasevariable-lengthvarchar2
user1051434
user1051434

Reputation: 167

Append varchar2 length in Oracle

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?

Upvotes: 0

Views: 540

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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:

Upvotes: 2

Related Questions